Create distinct list with criteria

[brentbush]

Hello,

I have successfully created a distinct list of items from another list (no duplicates) and now I want to filter that list so that it only lists items that include a certain text string in it. I need to use wild cards because the test string is imbedded with other text.

In my search I see multiple ways to do this. I do not have the Unique or Filter formulas available, so I found others use the match, countif + if or search, but I am unable to get these options to work. I have attached a generic example.

Any insights on what to add to this formula to only include a certain text string?
IFERROR(INDEX($B$5:$B$11, MATCH(0,COUNTIF($D$4:D4, $B$5:$B$11), 0)),"")

Thank you for your help.

Brent

Unique list with criteria.xlsx
	B	C	D
4	Long List		Unique List
5	a Item 1		a Item 1
6	aItem2		aItem2
7	part1		part1
8	aItem2		part2
9	part2		product 1
10	a Item 1
11	part1
12	product 1
Sheet1

Cell Formulas
Range	Formula
D5:D9	D5	=IFERROR(INDEX($B$5:$B$12, MATCH(0,COUNTIF($D$4:D4, $B$5:$B$12), 0)),"")
D10:D11	D10	=IFERROR(INDEX($B$5:$B$11, MATCH(0,COUNTIF($D$4:D9, $B$5:$B$11), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D
1
2
3
4		Long List		Unique List
5		a Item 1		a Item 1
6		aItem2		aItem2
7		part1
8		aItem2
9		part2
10		a Item 1
11		part1
12		product 1
13
Data

Cell Formulas
Range	Formula
D5:D9	D5	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$5:$B$100)/(ISNUMBER(SEARCH("item",$B$5:$B$100)))/(ISNA(MATCH($B$5:$B$100,D$4:D4,0))),1)),"")

 

[brentbush]

Works like a champ! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.