CODEWITHSUNDEEP
excel
drv82
drv82

Reputation: 13

How to return a unique list that contains a string?

I'm trying to return a unique list that contains a string:

Example: Criteria - contains "Banana"

Original List        Desired List
1Banana              1Banana
1Banana              2Banana
1Orange              4Banana
1Apple
2Banana
2Banana
2Apple
2Apple
3Orange
3Apple
4Orange
4Banana
4Banana
5Apple

Upvotes: 0

Views: 46

Answers (1)

barry houdini
barry houdini

Reputation: 46341

Assuming original list in A2:A15 then with header in B1 use this array formula in B2

=IFERROR(INDEX(A$2:A$15,MATCH(1,ISNUMBER(SEARCH("banana",A$2:A$15))*(COUNTIF(B$1:B1,A$2:A$15)=0),0)),"")

Confirm with CTRL+SHIFT+ENTER and copy down column. When valid entries are exhausted you get blanks. This is not case-sensitive

Upvotes: 2

Related Questions