I have a list of data in each cell. I would like to be able to filter the
spreadsheet based on certain text within the cells. Therefore, in the
example below if I were looking for only Dog or Cat data in column A, I would
use an AutoFilter and use the costum auto filter option and type: contains
Dog or Cat, and only rows that have Dog or Cat in them would appear.
However, what if I would like it to contain 3 different items from the list,
Dog, Cat, and Fish?

Example:
ColumnA
Dog,Cat
Dog,
Mouse,Cat
Mouse,Dog
Fish
Fish,Cat
Fish,Mouse

Here's one non-array formulas play ..

Assuming source data is in A2 down

Let's say E2:E10 will be where the list of text such as: dog, cat, fish, etc
will be input (the "input range")

Put in B2:
=IF(COUNT(C:C)<ROW(A1),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

Put in C2:
=IF(SUMPRODUCT(ISNUMBER(SEARCH($E$2:$E$10,A2))*($E $2:$E$10<""))0,ROW(),"")

(Leave C1 empty)

Select B2:C2, fill down to the last row of data in col A

Col B will extract the required results

We could replace SEARCH with FIND in the criteria formulas in col C if case
sensitivity searching is needed for the items listed in the input range
(SEARCH is not case sensitive)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Visual Calendar Dilemma" wrote:
I have a list of data in each cell. I would like to be able to filter the
spreadsheet based on certain text within the cells. Therefore, in the
example below if I were looking for only Dog or Cat data in column A, I would
use an AutoFilter and use the costum auto filter option and type: contains
Dog or Cat, and only rows that have Dog or Cat in them would appear.
However, what if I would like it to contain 3 different items from the list,
Dog, Cat, and Fish?

Example:
ColumnA
Dog,Cat
Dog,
Mouse,Cat
Mouse,Dog
Fish
Fish,Cat
Fish,Mouse