Avatar of Tina K
Tina K
Flag for United States of America asked on

Excel: Concatenate Issue-textjoin would be perfect ....

rankings.pngI have a workbook where I am trying to concatenate cells in column A based on the values in column E  Simply put... if the value in E:E = "A1" then concatenate all the corresponding names in A:A separated by commas.  Textjoin would perfect, but we have not yet graduated to Office 365/Excel 2019.  I would really love to avoid vba if at all possible, but I'm not ruling it out.    

In my example, I would like the grid in cells P5:R8 to fill automatically with the names from A:A based on the values found in column E.  All people with a ranking of A1 should appear in cell P5 separated by commas.  I've tried all sort of combinations of if and concatenate (unfortunately, concatenateif does not work on my system either)

(Note that D contains a formula assigning a number value to the word in column C, and then concatenating B and D in column E.  These columns will be hidden)
Microsoft Office* concatenateMicrosoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In P5
=TEXTJOIN(", ",TRUE,IF($E$3:$E$14=P$4&$O5,$A$3:$A$14,""))

Open in new window

Confirm with Ctrl+Shift+Enter and then copy across and down.

TextJoin.jpgPS: If your Excel version supports Dynamic Arrays, you may simply confirm the above formula with Enter alone.
Ejgil Hedegaard

Without Textjoin it is more complicated, because each item must be added separately to the list.
Attached can assemble up to 3 names in the list.

The calculation can be done using columns B and C instead of D and E.
See O9:R12
Tina K

Subodh - Textjoin does not work in our version of Excel - even using ctrl-shift-enter results in the #NAME error.  
Ejgil - There will be several hundred names in the list, so a formula for capturing three will not be robust enough

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Subodh Tiwari (Neeraj)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ejgil Hedegaard

The formula can easily be expanded for more names, but if the result could have hundreds, I think VBA is the way.
Tina K

Many thanks Subodh Tiwari - your solution worked perfectly!
Subodh Tiwari (Neeraj)

You're welcome Tina! Glad it worked as desired.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.