Need help with a CountA formula that concatenates

**shannonandreas113** · 02-24-2017, 03:10 PM

Here is the formula. It refers to a list of names:

Name List

John Smith
Mary Smith
Lou Philips
Jenny Mao
Philip Long

=IF(COUNTA(B1:B6)=1,B1,IF(COUNTA(B1:B6)=2,CONCATENATE(B1," and ",B2),IF(COUNTA(B1:B6)=3,CONCATENATE(B1,", ",B2,","," and ",B3),IF(COUNTA(B1:B6)=4,CONCATENATE(B1,", ",B2,", ",B3,","," and ",B4),IF(COUNTA(B1:B6)=5,CONCATENATE(B1,", ",B2,", ",B3,", ",B4,","," and ",B5),IF(COUNTA(B1:B6)=6,CONCATENATE(B1,", ",B2,", ",B3,", ",B4,", ",B5,","," and ",B6)," "))))))

Basically, I have allotted for up to 6 names to be concatenated with commas and "and" on output. I need to find a way to allow for an unlimited amount of concatenated names. Output example would be as follows:

John Smith, Mary Smith, Lou Philips, Jenny Mao, and Philip Long

Please let me know if you need more data.

Thank you in advance for your help!

Shannon

**mehmetcik** · 02-24-2017, 06:43 PM

Formula:

Please Login or Register  to view this content.

**shannonandreas113** · 02-24-2017, 07:33 PM

I will give this a try! Thank you so much for your response!

How would I end the concatenation with "and"?

Shannon

**mehmetcik** · 02-25-2017, 01:01 PM

Ahh good Question.

Formula:

Please Login or Register  to view this content.

**shannonandreas113** · 02-26-2017, 08:42 PM

Thanks! However, "and"-1 will only work if the row data stops at B5 or B7, etc. I need to allow for unlimited rows or at least 20 (so, B1:B2), but not all outputs are going to have 20. Does that make sense? Some might have 5 or 6 or 1.

**JeteMc** · 02-26-2017, 09:33 PM

The proposed solution is a 'low tech' (uses a helper column in XFD) method. The formula for the helper column is:

Formula:

Please Login or Register  to view this content.

the formula has been copied down to XFD52 so that accommodates up to 50 names however if you have more names you could copy it as far down the column as needed. The result is then displayed in B3 which references XFD52, so if you copy the formula further down column XFD just change the cell reference in B3.
Let us know if you have any questions.