Excel 2007: Filtering out rows in a table based on a list

microsoft excelmicrosoft-excel-2007

I have a large table that looks like this:

ID      String
1       abcde
2       defgh
3       defgh
4       defgh
5       ijkl
6       ijkl
7       mnop
8       qrst

I want to selectivley hide rows by populating a list of filterd values. For example, I'd like to filter out (hide) all rows that contain 'ef', 'kl', and 'qr'. Is there an easy way to do this?

I know how to use Advanced filters to include only the rows that contain those substrings, but not the inverse. Has anyone does this before?

Best Answer

You can still use the Advanced Filters. Here's how you can use it for your problem:

Single Criterion

Here's a way to exclude or hide cells that contain the substring "ef."

enter image description here

Result:

enter image description here

This is the formula in A13:

=ISERROR(SEARCH($B$13,B2))

which works the same as: =ISERROR(SEARCH("ef",B2))

Note the usage of absolute and relative references. The 2nd reference (B2) points to the first item in your data range; in order for the filter to work, it needs to be relative. The reference that points to the text substring that you don't want to include needs to be absolute.

Also, the Criteria range needs to have a blank header; although you have include it when setting up your advanced filter (see the Criteria range field in the screenie).

Multiple Criteria

This example hides cells that contain the substrings "ef", "j" and "rs."

enter image description here

Result:

enter image description here

Here's the formula in A13 this time:

=AND(ISERROR(SEARCH($B$13,B2)),
     ISERROR(SEARCH($B$14,B2)),
     ISERROR(SEARCH($B$15,B2)))

or you could use:

=AND(ISERROR(SEARCH("ef",B2)),
     ISERROR(SEARCH("j",B2)),
     ISERROR(SEARCH("rs",B2)))
Related Question