I need to put together multiple entries into one where the results is text and has to be in one cell.

From this

table 1

to this

table 2

  • In your real-world application, how many total rows will you be trying to do this for? It matters to the approach.
    – Erik Tyler
    Aug 28 '20 at 17:47
  • The people can be infinite but the items (apple,water) will be limited
    – Abul
    Aug 28 '20 at 18:43
  • "Infinite" is neither helpful nor possible in Sheets. Do you have an estimate of what you expect to see in your own real-world situation? And what is the maximum TOTAL number of items, as well as the max items per person?
    – Erik Tyler
    Aug 28 '20 at 21:31
  • Please also set up a spreadsheet with the raw data you listed in your original post, then share the link here, being sure to set the share permission for the link to "Anyone with the link can edit." This will be the best way for solutions to be shared with you.
    – Erik Tyler
    Aug 28 '20 at 23:36

There is an excellent precedent on StackOverflow for this question - though I can't locate it just as the moment. This solution is based on How to do a "group concat" in an ARRAYFORMULA? in the Google support form.

Let's assume that your raw data is in Columns A and B, and the output will be in Columns C and D.

Insert this formula in Cell C1.
=Arrayformula(unique({A1:A, substitute(Trim(transpose(query(if(transpose(A1:A)=A1:A,B1:B&Char(10),),,9^99))),char(10),",")}))

BEFORE - raw data


AFTER - additional data


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.