MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Quick Advanced Filter Question


Posted by Robert on October 25, 2001 6:19 AM

I just have a quick (hopefully) question about advanced filter criteria. I have a pretty complicated advanced filter going and in one column I am filtering things that do not contain a certain word by using <>*xxxxx*. I also need to filter out things in the same column that don't contain a different word. So what I would like to know is if there is a way to have the criteria say "does not contain xxxxx or yyyyy". Help would be greatly appreciated. Thanks.


Posted by Aladin Akyurek on October 25, 2001 6:24 AM

For OR'ing use additional row(s) in the criteria range.

Aladin

Posted by Melanie Swarner on October 25, 2001 6:33 AM

Are you using titles?

In an advanced filter, you apply AND logic by putting the conditions on the same line. If you don't use titles, you can get a little more complex with your criteria. See "Conditions created as the result of a formula" in Excel help for "Examples of advanced filter criteria".

You would set up two cells on the same line that say =FirstCellInColumn<>*xxxxx* and =FirstCellInColumn<>*yyyyy* respectively. These will evaluate to true or false. When you apply the filter, it will iterate these conditons down from the "FirstCellInColumn" to the bottom. Don't forget to leave a blank row in place of the titles when you set up your Criteria range.

You will need to set up boolean criteria for any existing formulas as well. It's a little tricky to understand at first, but it's worth it for what you gain. Just don't forget that each row is an AND of all of it's cells. So if you have 3 rows, it means "all of row 1 is true" OR "all of row 2 is true" OR "all of row3 is true"

Hope this helps!

Posted by Melanie Swarner on October 25, 2001 6:36 AM

One more thing....

Also see the thread titled "Advanced Advanced Filter Criteria" further down in this sheet. Juan Pablo and Mark W. really explained it well when I posed a similar question. Recreate their examples in a new sheet just as they list them and you'll understand a little better.

Posted by Aladin Akyurek on October 25, 2001 6:44 AM

Re: Are you using titles?

Melanie,

All that sounds pretty complicated. I had the impression that Robert wanted to join criteria with OR. If I'm not mistaken, he has

Items (or a similar label that is also heading one of the columns of his data, say, in A1.
<>"*xxxxx*" in A2. He seems to need
<>"*yyyyy*" in A3.

The first crit is in row 2, the second in row 3.

AND'ing would require 2 columns looking like:

Items in A1 and Items in B1.
<>"*xxxxx*" in A2 and <>"*yyyyy" in B2

producing a compund criteria which would be hard to satisfy.

Regards,

Aladin

Posted by Robert on October 25, 2001 6:46 AM

I tried that before and it didn't work. Maybe I should explain my filter a little more. Columns A,B,C contain numbers. I have it filtered so that if any numbers less than 2000 are shown by OR'ing using 3 different rows. In addition to that I need to make sure that another column does not contain "xxxx" or "yyyy" no matter what the numbers in A,B,C are. I can get it to filter out one of the "does not contains" by simply copying the formula down 3 rows so that it matches up with the < 2000 statements and forms an AND function. There are 2 other columns that i have words filtered out of too by copy "does not equal" for the 3 rows. Simply added another row with "does not contain" messes the whole filter up. Again, help would be greatly appreciated.

Posted by Robert on October 25, 2001 9:01 AM

Thanks

Thanks for the help Melanie. This is a really good way to filter through stuff. Very useful if you have a complex spreadsheet. I still wasn't able to write my formulas as "does not contain" but I got around it by simply writing and statements with all the possibilities that I needed eliminated. Why won't Logic statements let you use ** for contains? Still very helpful.

Posted by Mark W. on October 25, 2001 9:12 AM

Robert, there lots of approaches to this kind of
problem, but it sounds like it would be useful to
simplify your Criteria. It's also helpful to
provide a consise example that everyone can work
on. Here's my attempt at both...

The Data...

={"Field1","Field2","Field3","Field4"
;2001,2000,2000,"xxxx"
;1999,1998,1999,"zzzz"
;2002,2000,2001,"bbbb"
;1999,1999,1999,"yyyy"
;2001,1998,1999,"aaaa"}

My recommmendation...

Enter the formula...

=OR(Field1<2000,Field2<2000,Field3<2000)

...into the 1st column and 2nd row of your criteria
range. Next, enter the formula...

=AND(ISNA(MATCH({"*xxxx*","*yyyy*"},Field4,0)))

...into the 2st column and 2nd row of your criteria
range. Since this is a computed criteria the 1st
row should remain blank.

The Results...

{"Field1","Field2","Field3","Field4"
;1999,1998,1999,"zzzz"
;2001,1998,1999,"aaaa"}

The 4th data row, {1999,1999,1999,"yyyy"}, isn't
displayed because Field4="yyyy".


Posted by Aladin Akyurek on October 25, 2001 10:06 AM

I also got this far by using ordinary criteria instead of computed criteria (succeded to crash Excel in the process). It seems Robert wouldn't want the second row of the result that you've got. If I'm mistaken, please disregard what follows.

I just applided the AND-formula as criterion in the first pass. This gives the result above. Applying the ordinary criteria (which Robert seems to use)

{"Field1";"<2000";"Field2";"<2000";"Field3";"<2000"}

on this result range eliminates the last row where Field4="aaaa".

Aladin

=========


Posted by Mark W. on October 25, 2001 11:25 AM

> It seems Robert wouldn't want the second row of
> the result that you've got"

This is one of those cases where a sample data
set and desired result may be more instructive
than interpeting the semantics of an english
discription. Wouldn't it be lovely if everyone
provided them! : ) I based my interpetation on
Robert's statement, "I have it filtered so that
if any numbers less than 2000 are shown". It was
my understanding that he wanted to "show [rows
where] any number [in columns A:C] that were "less
than 2000".

> Applying the ordinary criteria...

I used a computed criteria to avoid the use of
3 OR-ed rows so than additional constraints like
my use of =AND(ISNA(MATCH({"*xxxx*","*yyyy*"},Field4,0)))
wouldn't need to be duplicated on each row of the
OR-ed criteria range.


Posted by Mark W. on October 25, 2001 11:40 AM

Oops! Your criteria array had too many semicolons...

...and I misread it. I'm feel certain that you
meant...

{"Field1","Field2","Field3";"<2000","<2000","<2000"}

instead of...

{"Field1";"<2000";"Field2";"<2000";"Field3";"<2000"}


Posted by Robert on October 25, 2001 12:14 PM

Sample Data Set Per Mark's Requests

Sample Data For My Problem (Made Up Names):

{"Stock","Sold","Order","Random","Name","Company"
;100,3000,4000,"plum","apple","berry" XXX
;100,3000,4000,700,"apple","berry"
;3000,150,3000,3000,"orange","grape"
;3000,3000,1999,"kiwi","pear","banana"
;1500,3000,1500," ","pear","banana"
;3000,2,2000,400,"green apple","grape" XXX
;1,1,1,1,"green olive","berry" XXX
;30,30,4000,"grape","red cherry","grape" XXX
;3000,3000,2001,250,"plum","apple","berry" XXX
;2,2,2,4,"cherry","banana"
;1,1,1,1,"red plum","apple" XXX
;1,1,1,1,"peach","lemon" XXX
;1,1,1,1,"kiwi","lemon lime" XXX

The data with "XXX" are the rows that should not be shown. The criteria is that "Stock","Sold",OR "Order" <2000; "Random"<>"plum"; "Name" cannot contain "red" or "green"; "Company" cannot contain "lemon".

I hope this helps and that I said everything the way that I wanted to. :)

Posted by Mark W. on October 25, 2001 12:22 PM

Robert, this is a valid array... I know that it's missing quotes around XXX...

If you've typed this into a worksheet, just click
on a empty cell, type an equal sign, select
your data range, and press Control+=. Now, paste
what's in your formula bar into a followup posing.

Posted by Robert on October 25, 2001 12:27 PM

I actually did want the "aaaa" row to show up. I didn't explain myself very well. I wanted all rows that had either "Field 1", "Field 2", OR "Field 3" <2000 to show up as long as the rest of the criteria matched.


Posted by Mark W. on October 25, 2001 12:28 PM

Okay, now I understand what XXX, but there's still some data misalignments (nt)

Posted by Robert on October 25, 2001 12:37 PM

Re: Robert, this is a valid array... I know that it's missing quotes around XXX...

I didn't put this into a worksheet, I just typed it into the message. The XXX aren't part of the array. They are to represent which rows should not be shown once everything is filtered. There is a mistake I see now however. Each row should have 6 entries to go with the 6 columns. The 10th row down has 7. Please delete 250 from this row so it now looks like the following. Thanks for the tip on pasting too.

={"Stock","Sold","Order","Random","Name","Company";100,3000,4000,"plum","apple","berry";100,3000,4000,700,"apple","berry";3000,150,3000,3000,"orange","grape";3000,3000,1999,"kiwi","pear","banana";1500,3000,1500,0,"pear","banana";3000,2,2000,400,"green apple","grape";1,1,1,1,"green olive","berry";30,30,4000,"grape","red cherry","grape";3000,3000,2001,"plum","apple","berry";2,2,2,4,"cherry","banana";1,1,1,1,"red plum","apple";1,1,1,1,"peach","lemon";1,1,1,1,"kiwi","lemon lime"} If you've typed this into a worksheet, just click

Posted by Mark W. on October 25, 2001 12:38 PM

REPOST: Robert, this ISN'T a valid array... I know that it's missing quotes around XXX...

If you've typed this into a worksheet, just click
on a empty cell, type an equal sign, select
your data range, and press Control+=. Now, paste
what's in your formula bar into a followup posting.


Posted by Mark W. on October 25, 2001 12:56 PM

Okay, here's one approach...

I create a criteria range in I1:L2...

1. Enter {"Random";"<>plum"} into I1:I2
2. Enter {"Company";"<>*lemon*"} into J1:J2
3. Enter =OR(Stock<2000,Sold<2000,Order<2000) into K2
4. Enter =AND(ISNA(MATCH({"*red*","*green*"},Name,0))) into L2