Aggregate text data in Excel

I have some data like:

DATE INFO
-------- ----
2000/1/1 A
2000/1/2 B
2000/1/2 C
2000/1/2 D
2000/1/3 E

I want to retrieve all the INFO associated with each date. The result would be:

DATE RESULT
-------- ------
2000/1/1 A
2000/1/2 BCD
2000/1/3 E

(I do not mind if the result is in columns)

1 Answer

If you have Office 365 Excel then you can use this array formula:

=CONCAT(IF($A$2:$A$6=D2,$B$2:$B$6,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If you do not have Office 365 Excel and you are okay with the results in separate columns then use this:

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$6)/($A$2:$A$6=$D2),COLUMN(A1))),"")

Put that in the first cell, copy over and down.