How to extract only unique values from TEXTJOIN string?

[Jasuan]

Problem: Used textjoin to create tables based on an identifier. Textjoin has now created multiple stings of numbers (i.e. "7, 26" and "7, 37") and I can not figure out which function or combination of functions to use retain only the first unique instance of a number.

I have tried the following formula: =TEXTJOIN(", ",TRUE,IF(I2:I22<>"",IF(MATCH(I2:I22,I2:I22,0)=(ROW(I2:I22)-ROW(I1)),I2:I22,""),""))
However, this returns "7, 26, 7, 37, 7" When I only want "7, 26, 37"

 

[Peter_SSs]

Are you using TEXTJOIN in Microsoft 365 or in Excel 2019?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jasuan]

Are you using TEXTJOIN in Microsoft 365 or in Excel 2019?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using Microsoft 365

 

[Peter_SSs]

Thanks for updating your profile.

With 365 then you could try something like this

21 03 28.xlsm
	I	J
1
2	7, 26	7, 26, 37
3
4	7, 37
5	7
6
Unique

Cell Formulas
Range	Formula
J2	J2	=TEXTJOIN(", ",1,UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,I2:I22),",","</c><c>")&"</c></p>","//c")))

 

[Jasuan]

Thanks for updating your profile.

With 365 then you could try something like this

21 03 28.xlsm
	I	J
1
2	7, 26	7, 26, 37
3
4	7, 37
5	7
6
Unique

Cell Formulas
Range	Formula
J2	J2	=TEXTJOIN(", ",1,UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,I2:I22),",","</c><c>")&"</c></p>","//c")))

Hey Peter! This formula works great with one small exception: If a range has no values, like K2:K22, then it will return #VALUE! instead of just blank. I tried adding

=IF(I2:I22="","",TEXTJOIN(", ",1,UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,I2:I22),",","</c><c>")&"</c></p>","//c"))))

however, if the first cell in the range was blank, like J2, then it would produce no values.

 

[Peter_SSs]

If a range has no values, like K2:K22, then it will return #VALUE! instead of just blank.

=TEXTJOIN(", ",1,UNIQUE(IFERROR(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,K2:K22),",","</c><c>")&"</c></p>","//c"),"")))

 

[Jasuan]

=TEXTJOIN(", ",1,UNIQUE(IFERROR(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,K2:K22),",","</c><c>")&"</c></p>","//c"),"")))

Excellent formula sir!

 

[Peter_SSs]

Glad it worked for you. Thanks for the confirmation.

 

[mali_cap]

Hey, the formula does work.

What about if i want the extracted values to be alphabetically ordered?

 

[Fluff]

You can use the sort function, like

=TEXTJOIN(", ",1,SORT(UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(", ",1,I2:I22),",","</c><c>")&"</c></p>","//c"))))