google-sheetsgoogle-sheets-formula

vlookup to find employees of all clients


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

Solution

  • 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:

    BYROW , FILTER, JOIN