Using Jext Join and Match to find and join all cells excluding blanks

[Sparkee]

I am using TextJoin and Match to find all text in a cell that matches a reference criteria to create a single list for each reference. The formula is working great, however, it is including zero values and I would like to remove the zeros. Each time I try I break the formula, can someone please give me a hand?

Here is the formula: =TEXTJOIN(",",TRUE,IF(FREQUENCY(IF($D$2:$D1000=$D$2,MATCH(IF($D$2:$D$1000=$D$2,$E$2:$E$1000),IF($D$2:$D$1000=$D$2,$E$2:$E$1000),0)),ROW($D$2:$D$1000)-ROW($D$2)+1)>0,$E$2:$E$1000,""))

Thank you, Michelle

Last Name	First Name	Suburb	Ref Number	Type	Specialty	Text Join Field for Type	Text Joint for Specialty
Jones	Barry		111111​	General		General	0
Jackson	Michael		222222​	General		General,Specialist	0,Clowning
Jackson	Michael		222222​	General		General,Specialist	0,Clowning
Jackson	Michael		222222​	General		General,Specialist	0,Clowning
Jackson	Michael		222222​	Specialist	Clowning	General,Specialist	0,Clowning
John	Elton		333333​	General		General,Specialist	0,Clowning,Juggling
John	Elton		333333​	General		General,Specialist	0,Clowning,Juggling
John	Elton		333333​	General		General,Specialist	0,Clowning,Juggling
John	Elton		333333​	Specialist	Clowning	General,Specialist	0,Clowning,Juggling
John	Elton		333333​	Specialist	Juggling	General,Specialist	0,Clowning,Juggling

 

[Alex Blakenburg]

Do you have Microsoft 365 and as such access to the new dynamic array functions including the filter function ?
If so you could use this:-

=TEXTJOIN(",",TRUE,TRANSPOSE(UNIQUE(FILTER($F$2:$F$1000,($D$2:$D$1000=D7)*($F$2:$F$1000<>""),""))))

Note: Your code uses column E but based on you inserted image I have assumed it should be the Specialty column which seems to be column F.

My result based on your sample data is below:

20210225 TextJoin Filter.xlsx
	A	B	C	D	E	F	G	H	I
1	Last Name	First Name	Suburb	Ref Number	Type	Specialty	Text Join Field for Type	Text Joint for Specialty	MY TextJoin Filter version
2	Jones	Barry		111111	General		General	0
3	Jackson	Michael		222222	General		General,Specialist	0,Clowning	Clowning
4	Jackson	Michael		222222	General		General,Specialist	0,Clowning	Clowning
5	Jackson	Michael		222222	General		General,Specialist	0,Clowning	Clowning
6	Jackson	Michael		222222	Specialist	Clowning	General,Specialist	0,Clowning	Clowning
7	John	Elton		333333	General		General,Specialist	0,Clowning,Juggling	Clowning,Juggling
8	John	Elton		333333	General		General,Specialist	0,Clowning,Juggling	Clowning,Juggling
9	John	Elton		333333	General		General,Specialist	0,Clowning,Juggling	Clowning,Juggling
10	John	Elton		333333	Specialist	Clowning	General,Specialist	0,Clowning,Juggling	Clowning,Juggling
11	John	Elton		333333	Specialist	Juggling	General,Specialist	0,Clowning,Juggling	Clowning,Juggling
12	John	Elton		333333	Specialist	Juggling	General,Specialist	0,Clowning,Juggling	Clowning,Juggling
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=TEXTJOIN(",",TRUE,TRANSPOSE(UNIQUE(FILTER($F$2:$F$1000,($D$2:$D$1000=D2)*($F$2:$F$1000<>""),""))))

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Last Name	First Name	Suburb	Ref Number	Type	Specialty	Text Join Field for Type	Text Joint for Specialty
2	Jones	Barry		111111	General		General
3	Jackson	Michael		222222	General		General,Specialist	Clowning
4	Jackson	Michael		222222	General		General,Specialist	Clowning
5	Jackson	Michael		222222	General		General,Specialist	Clowning
6	Jackson	Michael		222222	Specialist	Clowning	General,Specialist	Clowning
7	John	Elton		333333	General		General,Specialist	Clowning,Juggling
8	John	Elton		333333	General		General,Specialist	Clowning,Juggling
9	John	Elton		333333	General		General,Specialist	Clowning,Juggling
10	John	Elton		333333	Specialist	Clowning	General,Specialist	Clowning,Juggling
11	John	Elton		333333	Specialist	Juggling	General,Specialist	Clowning,Juggling
Detailed_view

Cell Formulas
Range	Formula
G2:H11	G2	=TEXTJOIN(",",,UNIQUE(FILTER(E$2:E$11,$D$2:$D$11=$D2,"")))