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!

 

[AhoyNC]

Try one of these formulas.
If you have Excel 365 use formula in C4.
If not try the array formula in C2. Enter with CTRL-SHIFT-ENTER.

CUSTOMER sheet

Book1
	A	B	C
1		Cust1
2		Cust2	5,15,20,25
3	Cust1	Cust1
4		Cust2	5,15,20,25
5		Cust1
6		Cust6
7		Cust2
8		Cust1
9		Cust9
10		Cust10
11		Cust1
Customers

Cell Formulas
Range	Formula
C2	C2	{=TEXTJOIN(",",1,IF(IF($B$1:$B$1000=$A$3,MATCH(IDs!$A$1:$A$1000,IDs!$A$1:$A$1000,0)=MATCH(ROW(IDs!$A$1:$A$1000),ROW(IDs!$A$1:$A$1000)))=TRUE,IDs!$A$1:$A$1000,""))}
C4	C4	=TEXTJOIN(",",1,UNIQUE(IF(Customers!$B$1:$B$1000=$A3,IDs!$A$1:$A$1000,"")))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

ID sheet

Book1
	A	B	C
1	5		Cust1
2	2		Cust2
3	15		Cust1
4	4		Cust2
5	20		Cust1
6	6		Cust6
7	7		Cust2
8	15		Cust1
9	9		Cust9
10	10		Cust10
11	25		Cust1
IDs

 

[legalhustler]

I don't have Dynamic Arrays. I tried the first formula with CSE but it returned a blank when I was expecting it to show some values. I modified my original formula in post #1 to make it easier to read, but that may have been a mistake. So here is my real formula:

=TEXTJOIN(",",1,IF(INDIRECT(E$1&"!$B$1:$B$1000")=$A4&"",INDIRECT(E$1&"!$A$1:$A$1000"),"")

Note: $A4&"" is referencing the current sheet and the ranges inside of both INDIRECT is reference another sheet.

Can you please amend that to your option 1 format? Also, any way to enhance your option 1 formula so it doesn't use CSE?

Thanks.

 

[AhoyNC]

Sorry, I'm not sure how you would do it using a formula. It maybe something that would have to be done using VBA. It would probably be easier for someone to help if you would post a small sample.

 

[Eric W]

Try:

=TEXTJOIN(",",1,IFERROR(IF(MATCH(A3&"|"&IDs!$A$1:$A$1000,Customers!$B$1:$B$1000&"|"&IDs!$A$1:$A$1000,0)=ROW(Customers!$A$1:$A1000),IDs!$A$1:$A$1000,""),""))

with CSE.

 

[legalhustler]

Try:

=TEXTJOIN(",",1,IFERROR(IF(MATCH(A3&"|"&IDs!$A$1:$A$1000,Customers!$B$1:$B$1000&"|"&IDs!$A$1:$A$1000,0)=ROW(Customers!$A$1:$A1000),IDs!$A$1:$A$1000,""),""))

with CSE.

Can you help extract unique values from the results of my current formula below?

=TEXTJOIN(",",1,IF(INDIRECT(E$1&"!$B$1:$B$1000")=$A4&"",INDIRECT(E$1&"!$A$1:$A$1000"),"")

 

[Eric W]

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.

 

[CA_Punit]

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,"")))

 

[CA_Punit]

Book3
	A	B	C	D
1	Cust1
2	Cust2
3	Cust1		Cust1	5,15,20,25
4	Cust2
5	Cust1
6	Cust6
7	Cust2
8	Cust1
9	Cust9
10	Cust10
11	Cust1
Sheet3

Cell Formulas
Range	Formula
D3	D3	=TEXTJOIN(",",TRUE,IFERROR(IF(FREQUENCY(INDEX(IDs!$A$1:$A$11,IF(IDs!$C$1:$C$11=Sheet3!C3,ROW(1:11))),INDEX(IDs!$A$1:$A$11,IF(IDs!$C$1:$C$11=Sheet3!C3,ROW(1:11))))>0,1,"")*INDEX(IDs!$A$1:$A$11,IF(IDs!$C$1:$C$11=Sheet3!C3,ROW(1:11))),""))
D6	D6	=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"),""),""))

 

[CA_Punit]

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.

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