I'm trying to do the following:
I'm using the formula to solve this problem. To do that, I need to write the formula in each cell. But I want to solve this using the ARRAYFORMULA so I can write the formula in the 1st cell.
This is what I am using right now:
=TEXTJOIN(", ",TRUE,ARRAYFORMULA(IF(B2=$B$1:F1,$C$1:C1,"")))
You can see the file here: Check this file
Thank you in advance for your effort.
try:
=INDEX(SUBSTITUTE(SUBSTITUTE(IF(COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))>1,
REGEXEXTRACT(IFNA(VLOOKUP(A2:A&"",
TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:A&"×", SUBSTITUTE(B2:B, " ", "_"), ROW(B2:B)},
"select max(Col2) where Col2 is not null group by Col3 pivot Col1"),,9^9)), "×")), 2, 0)),
TRIM(REPT("[\w]+ ", COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A))))), ), " ", ", "), "_", " "))