Filter rows on multiple criteria within a single column

[samunder]

Hi,

I am working on an excel solution where I need to open an excel file with 20 columns and 1000 rows, remove unwanted rows based on multiple criteria. One of the columns (the first one) is "CodeNo" which can have values between 1 and 100. I need to retain the rows with values 10, 20, 40 and 70 and remove the rest of them. I tried to apply the advanced filter (with <>10, <>20, <>40, <>70) but it seems the filter is not being applied cumulatively and I end up with all the rows as they are.

Please help me solve this. Is there a way to filter cumulatively?

[Bob Phillips]

Add a formula in a helper column of

=ISNUMBER(MATCH(E2,{10,20,40,70},0))

assuming E is the code columns, and then filter by that colume with a TRUE value

[mikerickson]

Assuming that your data is layed out such that A1 holds the header "CodeNo":

If you use a 2 row, 4 column Criteria Range (dots "..." for this display spacing only)

CodeNo...CodeNo...CodeNo...CodeNo
<>10......<>20......<>40.....<>70

AdvancedFilter will show the rows you want to delete.

Adding rows to a Criteria Range performs a logical OR, adding columns a logical AND.
(every number is <>10 OR <>20 which is why your 5 row CR acted the way it did.)


Alernately, a 2 row 1 column CriteriaRange with the top cell empty and the second cell holding the formula =NOT(OR(A2=10,A2=20,A2=40,A2=70))

will get the same result.

[samunder]

xld and mikerickson, Thank you so much for the prompt replies. I will check both the methods.