I have a spreadsheet with user details and all their clients in another column. I'm trying to form a new table with users in one column and all their clients in one row.
Actual table
User | Client |
---|---|
UserA | ClientA |
UserA | ClientA |
UserB | ClientA |
UserC | ClientA |
UserA | ClientB |
UserB | ClientC |
UserC | ClientB |
UserA | ClientC |
Expected table
User | Client |
---|---|
UserA | ClientA, ClientB, ClientC |
UserB | ClientA, ClientC |
UserC | ClientA, ClientB |
Try this Google sheets formula:
I used unique to eliminate duplicate, then filter to get the clients of every user then join them together to achieve the comma separated values, then using byrow to apply this per user.
=LET(a, UNIQUE(A2:A9),b, BYROW(a, LAMBDA(r, JOIN(",",FILTER(B2:B9,A2:A9 =r)))), HSTACK(a,b))
Sample Output
UserA | ClientA,ClientB,ClientC |
---|---|
UserB | ClientA,ClientC |
UserC | ClientA,ClientB |
REFERENCES: