Quick Advanced Filter Question

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

Aladin

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!

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.

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

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 &LT; 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.

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.

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...

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

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

=========

> 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.

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"}

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" &LT;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. :)

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.

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" &LT;2000 to show up as long as the rest of the criteria matched.

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

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

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.

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