CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulastextjoin
Randy Adikara
Randy Adikara

Reputation: 387

Array Formula to Combine Text with Unique Condition

I have a list of customers and the products they bought.

I want to combine every product that each customer bought into respectively a single cell next to customers' name.

Is there any way with array formula that can do this? The formula is expected to be a single formula that can automatically return the value when new data is input, so I think it should be an array formula.

Here is the sample case

https://docs.google.com/spreadsheets/d/1GpCbpFz7wrZjamhtm9rHk38RgyAUCBx54BeQCIeG1H4/edit#gid=0

The expected return can be seen in E:E. But the expected formula shouldnt be like that since it is kinda painful to drag formulas all over when I input new data, it is expected to be like an array formula.

Upvotes: 0

Views: 191

Answers (1)

JPV
JPV

Reputation: 27282

Erase contents in D:E and then enter in D1

=arrayformula(regexreplace({unique(A1:A), trim(transpose(query(if((transpose(unique(A1:A))=A1:A)*len(A1:A),B1:B&" - ",),,50000)))},"-$", ))

See if that works?

enter image description here

Upvotes: 2

Related Questions