Advanced Filter Query--Not

  • Thread starter Thread starter Orgelizer
  • Start date Start date
O

Orgelizer

I have a bunch of data that I'd like to filter by specifically excluding one
piece of data. Specifically, if one column in my data contains the value, I
would like to use <>1803 as the criteria. However, it seems that Excel
treats that cell as if it were empty. Is it possible to have a "not equal
to" in an advanced filter?

Also, and I don't think it makes a difference, the column containing the
1803 also has stuff like 0507, 0110, 1409, 1815--all defined as text because
leading zeros must be included. I've tried using a leading ' before the
number, but that doesn't work, either.

Any assistance would be appreciated.
Thanks
 
One trick I have used in the past was to multiply the "text" data *1 so that
it converted the text numbers to a number. Then in the autofilter select all,
then uncheck the 1803
 
In the criteria area, leave the heading cell blank
Then, in the cell below, enter a formula that refers to the first data
row in the column with the numbers that you want to filter.

For example, if the numbers are in column E:
=$E2<>"1803"

Then run the advanced filter, and you should get everything except that
number.

Or, you could type the number in the criteria heading cell, and refer to
that cell in the formula. For example, if the criteria range is H1:H2:
=$E2<>$H$1
 
Back
Top