lookup one column and merge similar cell into one (concat and line break)

[mq1973]

Hi guys,

I have this this table where i am trying to cell in column b into 1 cell if there are similar cells in column A.

I tried this formula but something went wrong in C2.
CONCATENATEIF($A$2:$A$16,A2,$B$2:$B$16," ")

Edited: Also for clarity, basically I am trying to combine multiple items in one report so I can get red of duplicate reports in column A.

merge pic.png

[Ace_XL]

Do you mean

=CONCAT(IF($A$2:$A$324=A2,$C$2:$C$324&" ",""))

Array-entered

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

However, I'm not sure if the CONCAT function is available in Excel 2016

[mq1973]

Thanks Ace. I am getting 0's in column C! and Yes, I am using Excel 2016.

Edit: I changed it to CONCAT(IF($A$2:$A$16=A2,$B$2:$B$16&" ","")) and it is working perfect. Is there away to make each items starts from a new line (it's like using alt+Enter function)? So instead of having 3 items appear on one line, I would like each item to start with a new line within the same cell.


merge cells 2.png

[mq1973]

I guess the proper question is how to use CONCAT with line break. Please help!

[Bo_Ry]

Please try at C2

=TEXTJOIN(CHAR(10),,REPT($B$2:$B$16,$A$2:$A$16=A2))

[mq1973]

Please try at C2

=TEXTJOIN(CHAR(10),,REPT($B$2:$B$16,$A$2:$A$16=A2))

Thanks so much Bo_Ry. This is exactly what I was looking for