Fetch names through duplicate User ID and show all names in Unique IDs-CodePudding

I have 2 spreadsheets in Google Sheets. In spreadsheet 1 - Column A has the User ID (including duplicate ID) and Column B has Names. In Sheet 2 - Column A has a Unique ID.

I want names to be shown in Column B in spreadsheet 2 (if in 1 User ID there are multiple names and it should show all the names in the unique ID.

I tried the below formula but it is showing the wrong names (names are not matching with the corresponding User ID).

In another sheet I got Unique IDs through: =UNIQUE(Sheet2!A:A) and names through =JOIN(",",FILTER(Sheet2!B:B,Sheet2!A:A = Sheet2!A1))

Attaching screenshots for reference. Spreadsheet 1

Spreadsheet 2

Thank you

CodePudding user response：

Try below lambda formula-

=LAMBDA(lm,{lm,MAP(lm,LAMBDA(x,JOIN(", ",FILTER(B2:B,A2:A=x))))})(UNIQUE(A2:INDEX(A2:A,COUNTA(A2:A))))