Extract Unique Values From A Single Cell Formula

[legalhustler]

I have a single cell formula that uses the TEXTJOIN function and returns multiple values. Although some answers are returning duplicate values (which is working as designed b/c multiple instances do appear when I do the look up), but I want to only show the unique values in my single cell solution. For example my formula is returning the following in a single cell, separated by commas:

5,15,20,15,25

I want to further amend my formula below so that it only shows 5,15,20, 25

={TEXTJOIN(",",1,IF(Customers!$B$1:$B$1000=$A3,IDs!$A$1:$A$1000,""))}

Any help is appreciated. TIA!

 

[legalhustler]

Try:

=TEXTJOIN(",",1,IFERROR(IF(MATCH($A4&"|"&INDIRECT(E$1&"!A1:A1000"),INDIRECT(E$1&"!B1:B1000")&"|"&INDIRECT(E$1&"!A1:A1000"),0)=ROW(INDIRECT(E$1&"!A1:A1000"))-ROW(INDIRECT(E$1&"!A1"))+1,INDIRECT(E$1&"!A1:A1000"),""),""))

with CSE.

Awesome! Worked beautifully.

 

[legalhustler]

Hi,
Can you please post your excel sheet.. I am a bit confused with the E1 Cell

E1 is header name which is the same as my lookup sheet name. I'm using the header name of the column to look up the sheet via INDIRECT.

 

[Eric W]

ALso you can use Unique function if you are using office 365
=TEXTJOIN(",",1,UNIQUE(IF(Customers!$B$1:$B$1000=$A3,IDs!$A$1:$A$1000,"")))

I don't have the new functions yet, or I'd try it, but I suspect the new FILTER function would work here too.

 

[Eric W]

Awesome! Worked beautifully.

Glad we could help! ?