Concatenate a row or part of

[StuartWhi]

Hi all,

I have been reading quite a few posts on this and haven’t managed to find what will work for me,
- Several post talking about using VBA, but unfortunately as I’m sharing the document so this wouldn't be ideal (many won’t accept macros),
- Several talking about a non-standard functions which runs a plugin (such as “concatIf”) or VBA also not ideal due to the above.

What I would like to do is concatenate a group of cells if they have text in them.
Quick background, arranging an extended family gathering and people are arriving / departing on different dates,
- Have found each instance of a date and using that to place the who arrive on the same date in different columns,

Arival Date / Time	People 1.0	People 1.1	People 1.2	People 1.3	People 1.4	People 1.5	People 1.6	People 1.7	People 1.8	People 1.9	People 1.10
Tue, 24-Mar-15, 0:00	Shirl, Steve, Anthony, Nicole, Chloe
Wed, 25-Mar-15, 16:30	Lea, Brett	Neil, Val	Dennis, Marie, Tobi	Yol, Kris, Kyan, Addy	Jill, Barry
Thu, 26-Mar-15, 18:30	Pat, Suzanne	Stuart, El

<tbody>
</tbody>

I would like to use a formula to then concatenate these into a common cell with a “, “ {comma then space} between them.
- I can achieve this by using this formula but it’s very long and ugly (embedded If’s) is there are better way to do this in a formula.

=IF(J5<>"",IF(AK5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5&", "&AK5,IF(AJ5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5&", "&AJ5,IF(AI5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5&", "&AI5,IF(AH5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5&", "&AH5,IF(AG5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5&", "&AG5,IF(AF5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5&", "&AF5,IF(AE5<>"",AA5&", "&AB5&", "&AC5&", "&AD5&", "&AE5,IF(AD5<>"",AA5&", "&AB5&", "&AC5&", "&AD5,IF(AC5<>"",AA5&", "&AB5&", "&AC5,IF(AB5<>"",AA5&", "&AB5,IF(AA5<>"",AA5,""))))))))))),"")

All I’m doing in the above is repeating the same thing, checking if the cell has anything in it, if it does add it to the cells before it. There are 10 cells it checks for data thus the reason for being so long.

Any ideas would be appreciated.

Thanks,

Stuart.

 

[István Hirsch]

This formula tries to handle the case when several names belong to the same person:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(AA5&"#"&AB5&"#"&AC5&"#"&AD5&"#"&AE5&"#"&AF5&"#"&AG5&"#"&AH5&"#"&AI5&"#"&AJ5&"#"&AK5," ","|"),"#"," "))," ",", "),"|"," ")