I have some personal data with first/last names, e-mails, institutions people work at, etc. There are many, many duplicates because this was collected from a few sources over 2-3 years. Sometimes the same person provided different versions of their name, a different e-mail address, etc. I'd like to have a compact version of this data, where a single person (identified by a PersonID) is listed on a single row, with unique variants of their name, e-mail, etc. listed in each cell. Bonus points if the values in every cell are sorted, but far from required. Example above also available at

CodePudding user response：

Here's one way to do that using MAP:

=MAP(UNIQUE(D4:D),LAMBDA(id,BYCOL(FILTER(A4:D,D4:D=id),LAMBDA(col,JOIN(CHAR(10),UNIQUE(col))))))