Forum Discussion
My Formula Isn't Working!
My Excel spreadsheet is currently set up like this (pretend these are Columns A and B, in order):
Person Possible Cupcake Flavors
Michael Red Velvet
Michael Vanilla
Michael Funfetti
but I want it to be like this:
Person Possible Cupcake Flavors
Michael Red Velvet, Vanilla, Funfetti
Michael
Michael
(I'll just delete the rows that say "Michael" but are left blank by the formula I hopefully get from a whiz on this forum.)
I've been trying to construct a formula to take care of this for me, but each of my attempts (which I'll provide below) are giving me this result:
Person Possible Cupcake Flavors
Michael Red Velvet, Vanilla
Michael Vanilla, Funfetti
Michael Funfetti
FYI: there are names and possible cupcake flavors other than Michael and Red Velvet, Vanilla, Funfetti.
Here are my attempts:
=IF(A2=A3,IF(A2=A3=A4,TEXTJOIN(", ",TRUE,B2,B3,B4),TEXTJOIN(", ",TRUE,B2,B3)),B2)
=IFS(A2=A3=A4,TEXTJOIN(", ",TRUE,B2,B3,B4),A2=A3,TEXTJOIN(", ",TRUE,B2,B3),A2<>B2,)
=IF(A2=A3=A4,TEXTJOIN(", ",TRUE,B2,B3,B4),(IF(A2=A3,TEXTJOIN(", ",TRUE,B2,B3),B2)))
The thing that's frustrating me most is that the Funfetti is being left off the first row with the formula in it; if the first row had all 3 cupcake flavors in it, I could just delete the other rows...
In C2:
=IF(A2=A1, "", TEXTJOIN(", ", TRUE, FILTER($B$2:$B$1000, $A$2:$A$1000=$A2)))
Fill down.
- OliverScheurichGold Contributor
=BYROW(A2:A9,LAMBDA(row,IF(COUNTIF(A2:row,row)=1,TEXTJOIN(",",,FILTER(B2:B9,A2:A9=row)),"")))
An alternative could be this formula which spills the results.