Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter Query--Not
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter Query--Not
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 "Orgelizer" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Advanced Filter Query--Not
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 Orgelizer wrote: 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 -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |