Make Column that Finds Unique Values Based on Keywords without Duplicates

Hello,

I am trying to create a unique values column (V) that looks to data source column (S), that will pull out unique values that only contain "key text" without repeats. So far I have the formula that will analyse the entire data source column and will create a list of unique values. This formula is as follows:

{=IFERROR(INDEX($S$3:$S$500,MATCH(0,INDEX(COUNTIF($V$2:V2,$S$3:$S$500),0,0),0)), "")}

Any idea how to make it so it will only add values that contain "MET/" and/or "XDCR/" to the unique values column?

Post some sample data and show us what results you expect.

I could only do this by using a helper column.

Data Range

	S	T	U	V
2	------	------	------	------
3	MET/LN1	X		MET/LN1
4	MET/LN2	X		MET/LN2
5	21B/LN1			XDCR/R1
6	87B/LN2			XDCR/R2
7	XDCR/R1	X		MET/LN3
8	XDCR/R2	X
9	51A/R3
10	MET/LN1	X
11	MET/LN3	X
12	XDCR/R1	X

This formula entered in T3 and copied down:

=IF(SUM(COUNTIF(S3,{"MET/*","XDCR/*"})),"X","")

This array formula** entered in V3:

=IFERROR(INDEX(S$3:S$12,MATCH(0,IF(T$3:T$12="X",COUNTIF(V$2:V2,S$3:S$12)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Originally Posted by Tony Valko

I could only do this by using a helper column.

I must have been suffering from a brain cramp!

Data Range

	S	T	U	V
2	------	------	------
3	MET/LN1			MET/LN1
4	MET/LN2			MET/LN2
5	21B/LN1			XDCR/R1
6	87B/LN2			XDCR/R2
7	XDCR/R1			MET/LN3
8	XDCR/R2
9	51A/R3
10	MET/LN1
11	MET/LN3
12	XDCR/R1

This array formula** entered in V3:

=IFERROR(INDEX(S$3:S$12,MATCH(0,IF((LEFT(S$3:S$12,4)="MET/")+(LEFT(S$3:S$12,5)="XDCR/"),COUNTIF(V$2:V2,S$3:S$12)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

You're welcome. Thanks for the feedback!