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,
[TABLE="width: 849"]
<tbody>[TR]
[TD]Arival Date / Time[/TD]
[TD]People 1.0[/TD]
[TD]People 1.1[/TD]
[TD]People 1.2[/TD]
[TD]People 1.3[/TD]
[TD]People 1.4[/TD]
[TD]People 1.5[/TD]
[TD]People 1.6[/TD]
[TD]People 1.7[/TD]
[TD]People 1.8[/TD]
[TD]People 1.9[/TD]
[TD]People 1.10[/TD]
[/TR]
[TR]
[TD]Tue, 24-Mar-15, 0:00[/TD]
[TD]Shirl, Steve, Anthony, Nicole, Chloe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed, 25-Mar-15, 16:30[/TD]
[TD]Lea, Brett[/TD]
[TD]Neil, Val[/TD]
[TD]Dennis, Marie, Tobi[/TD]
[TD]Yol, Kris, Kyan, Addy[/TD]
[TD]Jill, Barry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu, 26-Mar-15, 18:30[/TD]
[TD]Pat, Suzanne[/TD]
[TD]Stuart, El[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[gaz_chops]

How many columns are there?

 

[StuartWhi]

Hi Gaz,

11 colums aa to ak.
- Then Copy down.

Thanks,

Stuart

 

[Rick Rothstein]

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,
[TABLE="width: 849"]
<tbody>[TR]
[TD]Arival Date / Time[/TD]
[TD]People 1.0[/TD]
[TD]People 1.1[/TD]
[TD]People 1.2[/TD]
[TD]People 1.3[/TD]
[TD]People 1.4[/TD]
[TD]People 1.5[/TD]
[TD]People 1.6[/TD]
[TD]People 1.7[/TD]
[TD]People 1.8[/TD]
[TD]People 1.9[/TD]
[TD]People 1.10[/TD]
[/TR]
[TR]
[TD]Tue, 24-Mar-15, 0:00[/TD]
[TD]Shirl, Steve, Anthony, Nicole, Chloe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed, 25-Mar-15, 16:30[/TD]
[TD]Lea, Brett[/TD]
[TD]Neil, Val[/TD]
[TD]Dennis, Marie, Tobi[/TD]
[TD]Yol, Kris, Kyan, Addy[/TD]
[TD]Jill, Barry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thu, 26-Mar-15, 18:30[/TD]
[TD]Pat, Suzanne[/TD]
[TD]Stuart, El[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.

The range AA5:AK5 has 11 cells in it, not 10. This formula is a little simpler than the one you posted...

=MID(IF(AA5="","",", "&AA5)&IF(AB5="","",", "&AB5)&IF(AC5="","",", "&AC5)&IF(AD5="","",", "&AD5)&IF(AE5="","",", "&AE5)&IF(AF5="","",", "&AF5)&IF(AG5="","",", "&AG5)&IF(AH5="","",", "&AH5)&IF(AI5="","",", "&AI5)&IF(AJ5="","",", "&AJ5)&IF(AK5="","",", "&AK5),3,999)

Note that I did not test J5 to see if it contained a value or not because I assumed if J5 had nothing in it, then none of AA5:AK5 would either. If that assumption is not correct, then use this formula instead...

=IF(J5="","",MID(IF(AA5="","",", "&AA5)&IF(AB5="","",", "&AB5)&IF(AC5="","",", "&AC5)&IF(AD5="","",", "&AD5)&IF(AE5="","",", "&AE5)&IF(AF5="","",", "&AF5)&IF(AG5="","",", "&AG5)&IF(AH5="","",", "&AH5)&IF(AI5="","",", "&AI5)&IF(AJ5="","",", "&AJ5)&IF(AK5="","",", "&AK5),3,999))

 

[gaz_chops]

Try,
=SUBSTITUTE(SUBSTITUTE(TRIM(AA1 & " " & AB1 & " " & AC1 & " " & AD1 & " " & AE1 & " " & AF1 & " " & AG1 & " " & AH1 & " " & AI1 & " " & AJ1 & " " & AK1), ",", "")," ",", ")

 

[Rick Rothstein]

Try, adjust the cell references to suit
=SUBSTITUTE(SUBSTITUTE(TRIM(A1 & " " & B1 & " " & C1 & " " & D1 & " " & E1 & " " & F1 & " " & G1 & " " & H1 & " " & I1 & " " & J1), ",", "")," ",", ")

That will only work if the text being concatenated are single words... if a single cell contains two or more words, your formula will put a comma/space between each of its words.

 

[István Hirsch]

That will only work if the text being concatenated are single words... if a single cell contains two or more words, your formula will put a comma/space between each of its words.

For me the formula in post #5 does what (I think) is expected.

 

[Rick Rothstein]

For me the formula in post #5 does what (I think) is expected.

Maybe, as long as all names being concatenated are single names; however, I have a friend whose name first name is "Mary Ann" (with the space in the middle)... her name would come out "Mary, Ann" in the list and look like two people within the overall concatenated list.

 

[StuartWhi]

Hi Gaz and Rick,

Thanks for all your help, I have checked both formulas in my situation and end up with Rick's. The format of the names is "Shirl, Steve, x" as these people are traveling under the same booking. Unfortunately it's not a single person per booking.
In any case both address the task slightly differently and work great in their own way.

Always amazes me what you guys are capable of, take it easy and enjoy the rest of your weekend wherever you are.

Regards,

Stuart.

 

[gaz_chops]

You're welcome,
Both should produce the same result, except if there are 1st 2nd names (or surnames), as Rick points out (e.g. Rick Rothstein with my formula would result in Rick, Rothstein, )