Filtering text data based on unique names and then pasting all the data into one single cell for each name

[p49]

Hi all, excel noob here trying to learn but I have spent an insane amount of time on this problem without going anywhere. I made this mockup with the 2nd picture being the desired result, you can ignore the contents in the Adress field. The goal is to assign companies and the stores under them to each unique name and then remove the duplicate name rows. Basically copying and consolidating info from the duplicate rows into a cell of the first instance of a name. I have tried using pivot tables but that doesn't work because of these all being text fields. Filtering using UNIQUE also doesn't seem to work. At this point I'm considering trying to process the data in sql, maybe I'll have better luck there. I just want to know how to achieve this desired result . Thank you in advance.

 

[DanteAmor]

Hi and welcome to MrExcel

For example in cel H2 put the following formula:

=LET(a,UNIQUE(D2:E8),d,D2:D8,e,E2:E8,VSTACK(
BYROW(a,LAMBDA(br,TEXTJOIN(CHAR(10),1,FILTER(A2:B8,(d=CHOOSECOLS(br,1))*(e=CHOOSECOLS(br,2)))))),
BYROW(a,LAMBDA(br,INDEX(C2:C8,MATCH(1,(d=CHOOSECOLS(br,1))*(e=CHOOSECOLS(br,2)),0)))),a))

 

[hagia_sofia]

@DanteAmor's solution is no doubt perfect as always, I just wanted to test the newly released GROUPBY function:

=CHOOSECOLS(SUBSTITUTE(GROUPBY(C2:E8,A2:A8&"; "&B2:B8,ARRAYTOTEXT,,0),"; ",CHAR(10)),4,1,2,3)

 

[p49]

Hi and welcome to MrExcel

For example in cel H2 put the following formula:

=LET(a,UNIQUE(D2:E8),d,D2:D8,e,E2:E8,VSTACK(
BYROW(a,LAMBDA(br,TEXTJOIN(CHAR(10),1,FILTER(A2:B8,(d=CHOOSECOLS(br,1))*(e=CHOOSECOLS(br,2)))))),
BYROW(a,LAMBDA(br,INDEX(C2:C8,MATCH(1,(d=CHOOSECOLS(br,1))*(e=CHOOSECOLS(br,2)),0)))),a))

You’re amazing! Thank you so much oh great excel wizard!

 

[p49]

@DanteAmor's solution is no doubt perfect as always, I just wanted to test the newly released GROUPBY function:

=CHOOSECOLS(SUBSTITUTE(GROUPBY(C2:E8,A2:A8&"; "&B2:B8,ARRAYTOTEXT,,0),"; ",CHAR(10)),4,1,2,3)

Thank you! I’ll have a look at this one too.