Advanced Filter "Not Equal"

[Guest]

When applying multiple <> criteria in advanced filter it does not work! Why?
A1
<>*bus*
<>*Inde*
<>*schoo*
Why don't the "Or" statements work?
Thanks

 

[Debra Dalgleish]

You probably need an AND, not an OR, if you're trying to exclude all
three of these strings.

For an AND, add the heading cell to the criteria range three times, and
put all three criteria on the same row.

Run the advanced filter again, and adjust the criteria range.

 

[Guest]

Thanks Deb>that makes sense>but why do the or statements work e.g.
A1
*bus*
*scho*
*indep*
Thanks again for the help

 

[Debra Dalgleish]

With the sample data:
bus
school
house

Testing the data against your criteria:
Is "bus" equal to *bus* OR *scho* OR *indep* -- TRUE
Is "scho" equal to *bus* OR *scho* OR *indep* -- TRUE
Is "house" equal to *bus* OR *scho* OR *indep* -- FALSE
Since you used the OR operator, and "bus" meets at least one of the
criteria, it passes through the filter.

When you add the <> (does not equal) operator:
Is "bus" not equal to *bus* -- FALSE
OR not equal to *scho* -- TRUE
OR not equal to *indep* -- TRUE

Since you used the OR operator, and "bus" meets at least one of the
criteria, it passes through the filter.
If you use the AND operator, "bus" must meet all the criteria, and not
equal any of the items in the list. It doesn't meet all the criteria, so
would not pass through the filter.

 

[Guest]

gracias por sus explinacion a mi amiga!

With the sample data:
bus
school
house

Testing the data against your criteria:
Is "bus" equal to *bus* OR *scho* OR *indep* -- TRUE
Is "scho" equal to *bus* OR *scho* OR *indep* -- TRUE
Is "house" equal to *bus* OR *scho* OR *indep* -- FALSE
Since you used the OR operator, and "bus" meets at least one of the
criteria, it passes through the filter.

When you add the <> (does not equal) operator:
Is "bus" not equal to *bus* -- FALSE
OR not equal to *scho* -- TRUE
OR not equal to *indep* -- TRUE

Since you used the OR operator, and "bus" meets at least one of the
criteria, it passes through the filter.
If you use the AND operator, "bus" must meet all the criteria, and not
equal any of the items in the list. It doesn't meet all the criteria, so
would not pass through the filter.