My Formula Isn't Working!

Copper Contributor

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




(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...


2 Replies


In C2:

=IF(A2=A1, "", TEXTJOIN(", ", TRUE, FILTER($B$2:$B$1000, $A$2:$A$1000=$A2)))

Fill down.



An alternative could be this formula which spills the results.