Textjoin formula needed

[annep833]

Hi, I want to return all the different values in column e associated with all of the matching values in column b with a comma in between the values. I want this formula in column f. For example, cell b2=hammer, b3=hammer, b4= hammer and cell e2=a, e3=b and e=c. If I entered the formula in f2, I want it to return "a,b,c" without the quotes. F3 and f4 would be blank because I just want them combined into one cell. If that's not possible to have the blanks, I could remove duplicates from column b. Thank you

 

[Cubist]

Which version of XL are you using? You've mentioned "TEXTJOIN" which doesn't exist in 2010 as indicated in your profile.

 

[annep833]

Which version of XL are you using? You've mentioned "TEXTJOIN" which doesn't exist in 2010 as indicated in your profile.

Sorry about that. It's 365

 

[Cubist]

See if you have GROUPBY:

Book2
	B			E	F	G	H
1
2	hammer			a		bucket	d, e
3	hammer			b		hammer	a, b, c
4	hammer			c
5	bucket			d
6	bucket			d
7	bucket			e
Sheet10

Cell Formulas
Range	Formula
G2:H3	G2	=GROUPBY(B2:B7,E2:E7,LAMBDA(x,ARRAYTOTEXT(UNIQUE(x))),,0)
Dynamic array formulas.

 

[annep833]

Groupby is not working. I get a #NAME? error

 

[Cubist]

It means you don't have GROUPBY yet. Here's an alternative:

Book4
	B			E	F	G	H
1
2
3	hammer			a		hammer	a, b, c
4	hammer			b		bucket	d, e
5	hammer			c
6	bucket			d
7	bucket			d
8	bucket			e
Sheet4

Cell Formulas
Range	Formula
G3:H4	G3	=LET(u,UNIQUE(B3:B8),HSTACK(u,MAP(u,LAMBDA(m,TEXTJOIN(", ",,UNIQUE(FILTER(E3:E8,B3:B8=m)))))))
Dynamic array formulas.

 

[annep833]

That one worked! Thank you so much!