Merge data under same criteria in a column of array

August 8, 2023

I need to merge data of a column of a table that under the same key. I named the first table tb.

|   Name1  |  Name2  |
| -------- | ------- |
|     A    |    X    |
|     B    |    Y    |
|     A    |    Y    |
|     B    |    Z    |
          ↓↓↓
|   Name1  |  Name2  |
| -------- | ------- |
|     A    |   X,Y   |
|     B    |   Y,Z   |

I’ve tried to use the combination of FILTER, UNIQUE and TEXTJOIN but I can’t get the wanted result. Is it even achievable?

>Solution :

you can use this formula:

=LET(n,Table1[Name1],v,Table1[Name2],h,Table1[#Headers],
m,BYROW(UNIQUE(n),LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER(v,n=x)))),
VSTACK(h,HSTACK(UNIQUE(n),m)))