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)
Open in new windowConfirm with Ctrl+Shift+Enter and then copy across and down.
PS: If your Excel version supports Dynamic Arrays, you may simply confirm the above formula with Enter alone.