# Aggregate data to show text in one cell

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

``````From this
`````` ``````to this
`````` • In your real-world application, how many total rows will you be trying to do this for? It matters to the approach. 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? 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. 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  