Using XLOOKUP or FILTER to aggragate data into a single cell

Question

I have a data set that is compiled by various people filling out a form to populate a spreadsheet on Google Sheet. Here's what the data set might look like: A data set listing out name, collected reviews, date, and caller.

There a group of callers, calling individuals to collect information, let's say movie reviews, and are making note in the form the name of who they called, what their review was, the date, and the name of the caller. I want to aggregate the data so that it is organized into a separate table by name listing all the reviews given by that person in a single cell, noting the name of the caller who collected the review and the date it was collected. This is what I want the resulting table to look like.

I've tried two different options. First XLOOKUP, and that function looks like:

=CONCATENATE(XLOOKUP(I2, $A$2:$A, $B$2:$B), " (",XLOOKUP(I2, $A$2:$A, $D$2:$D), ", ", TEXT(XLOOKUP(I2, $A$2:$A, $C$2:C),"mm/dd/yyyy"), ")")

This gave me the right formatting, but with XLOOKUP only gives me the first result if there are multiple reviews given by the same person. I want to collect all their reviews. Resulting table using XLOOKUP with the cells that don't include the second set of reviews highlighted in red.

Then I tried using FILTER:

=TEXTJOIN(CHAR(10),TRUE,FILTER($B$2:B, $A$2:A=L2),FILTER($D$2:D,A$2:$A=L2), FILTER($C$2:C,A$2:$A=L2))

This gave me all the data but formatted incorrectly. Now, if there are multiple reviews, they are listed in order, and then the names of the callers are listed, and then the dates

How can I return all the matches, but customize the order/formatting of the returned data into 1 cell?

Answer 1

Welcome!

You're right - TEXTJOIN(CHAR(10)... will do the trick.

You're also right - FILTER() can select the data you want.

You missed only one point - as the first parameter (an array for filtering), you need to pass a set of already formatted strings:

=IFERROR(TEXTJOIN(CHAR(10),1,
    ARRAYFORMULA(FILTER(
       {B1:B&" (" & D1:D & ", " & TEXT(C1:C,"YYYY-MM-DD") & ")"},
       A1:A=L1))),"")`

Answer 2

For those using Excel 365:

On the first column, type the formula:

=SORT(UNIQUE($A$2:$A$9))

On the second column, enter the formula below and enable Wrap Text for the cell.

=TEXTJOIN(CHAR(10),TRUE,TOROW(FILTER(HSTACK($B$2:$B$9,$D$2:$D$9,TEXT($C$2:$C$9,"dd/MM/yyyy")),A13=$A$2:$A$9),0,TRUE))

Drag the formula down to fill the cells below.