Conditional Formating - Filter NON Formated rows

[doug firr]

Hi

I have a sheet with over 10,000 rows and numerous columns. One of the columns are descriptive and contain text.

I am seeking to filter the sheet based on this descriptive column where it contains one of three words: Decking, Flooring and Paneling. I did this - any row with decking in descriptive column is red, Flooring in descriptive column is blue and paneling in descriptive column is yellow.

Because I want to keep those rows that contain one of these three words only in the descriptive column, I want to filter by color: no fill. I can filter one of the individual colors and copy paste to another sheet 3 times, but for the future there surely must be a better way of filtering non filled cells?

... any ideas?

 

[Jonmo1]

So you want to filter the rows that DO NOT contain any of the 3 words?

Use a helper column with the formula

=OR(A1={"Decking","Flooring","Paneling"})
And fill it down.

Then filter on that column for FALSE.

 

[doug firr]

Thanks for response Jon. Tried that and it doesn't work - it returns False for everything (Yes I've changed the cell reference from A1 to match appropriate column!)

Never used "OR" before - the column in question has much text int he cells and I filtered on whether contains any of these key words amongst that text.. not sure if thats important here or not.

 

[Weaver]

try

=OR(NOT(ISERROR(FIND({"decking","panelling","flooring"},B2))))

then filter for false

 

[Jonmo1]

OK, that's the key...

the column in question has much text int he cells and I filtered on whether contains

Try
=ISNUMBER(LOOKUP(2,1/SEARCH({"Decking","Flooring","Paneling"},A1)))

 

[doug firr]

Brilliant thanks that has worked. Never used "search" before - hows that different from Find? Oh well, thanks again

 

[Jonmo1]

Glad to help, thanks for the feedback...


The difference between Search and Find is Case Sensitiveness...

FIND is case sensitive, Search is not.

 

[doug firr]

made a mental note of that, thanks again

 

[Weaver]

Glad to help, thanks for the feedback...


The difference between Search and Find is Case Sensitiveness...

FIND is case sensitive, Search is not.

Ha, I hadn't appreciated the distinction myself!

=OR(NOT(ISERROR(SEARCH({"decking","panelling","flooring"},B2))))