Aggregate Text Data in Excel

Question

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)

Accepted 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.

1 Answer