Excel: Concatenate Issue-textjoin would be perfect ....
I 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)
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
Ranking-concatenate.xlsx
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
Ranking-concatenate.xlsx
ASKER
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
Thanks!
Ejgil - There will be several hundred names in the list, so a formula for capturing three will not be robust enough
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The formula can easily be expanded for more names, but if the result could have hundreds, I think VBA is the way.
ASKER
Many thanks Subodh Tiwari - your solution worked perfectly!
You're welcome Tina! Glad it worked as desired.
In P5
Open in new window
Confirm with Ctrl+Shift+Enter and then copy across and down.