Forum Discussion

Racheyisabean's avatar
Racheyisabean
Copper Contributor
May 24, 2023

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

 

Resources