Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
There is a "does not contain" option under autofilter, but this only permits two custom settings. –  variant Aug 12 '11 at 18:00
    
This is exactly what I need, but for a variable length of strings. Two is insufficient for this application. –  Sam Johnson Aug 12 '11 at 18:02
add comment

3 Answers

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)))
share|improve this answer
    
Bah! Imgur ruined the screenie :( –  Ellesa Aug 12 '11 at 19:25
    
Thanks for the awesome screens, however I need to do the exact opposite of this. I want to hide rows in the table based on a criteria range, not include them. Any idea on how to invert this? –  Sam Johnson Aug 12 '11 at 19:28
    
Updated my answer. Sorry if the screenies are ugly. –  Ellesa Aug 12 '11 at 20:30
add comment

I know your question dates more than one year, but you can use the conditional formatting if you havent figured it out yet.

share|improve this answer
add comment

One easy and wholly inelegant way I can think of is setting up another column to check if the value in your target column contains your filter strings.

Your formula would look something like

=IF(ISNUMBER(FIND("ef",B2)),1,0)+IF(ISNUMBER(FIND("kl",B2)),1,0)+IF(ISNUMBER(FIND("qr",B2)),1,0)

You could then autofilter on 0.

share|improve this answer
    
This is close - if there was a way to say FIND(C:C,B2) and have it return true if any of the string in column C were present in B2 I would be all set. –  Sam Johnson Aug 12 '11 at 18:08
    
@Sam Johnson, You can do that with an array formula. =IF(SUM(IFERROR(FIND($C:$C,B2),0))=0,0,1) entered by pressing Ctrl+Shift+Enter. Use that formula in Row 2, then fill down. –  Excellll Aug 15 '11 at 13:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.