Any way to filter with more than two comparison criteria?
Posted by Merle Mason on January 23, 2001 12:24 PM
Is there a way to do more than two comparison criteria for the same column? Say I want to see all entries in 415, 510 and 310 area codes (I've only been able to look at two comparison critera at a time). Thanks!
Posted by Mark W. on January 23, 2001 1:39 PM
Use an Advanced AutoFilter specifying a criteria.
Posted by Mark W. on January 23, 2001 1:50 PM
Suppose A1:A10 contains {"Sally";"Mary";"Larry";"Jerry";"Linda";"Mike";"Jim";"Jake";"Moe";"John"}
and B1:B10 contains {"Mark";"Sally";"Barry";"Larry";"Peter";"Tom";"Paul";"Mary";"Moe";"Jim"}.
Then {=COUNTA(A1:A10,B1:B10)-SUM((A1:A10=TRANSPOSE(B1:B10))+0)} does what you
want. This is an array formula which must be entered using Shift+Ctrl+Enter.
Posted by Mark W. on January 23, 2001 1:51 PM
Oops! This is a reply to another posting!
Posted by Merle Mason on January 23, 2001 5:37 PM
Mark,
I pasted the column heading to an empty area (A1:F1). C was the Area Code column, so in C2 I entered 415, in C3 I entered 510, and in C4 I entered 310. Next I defined the range A1:F4 as Criteria. Then I clicked a cell in my list, then chose Data>Filter>Advanced Filter. The List range ($A$7:$F$17) and Criteria Range ($A$1:$F$4) appeared correct. But when I clicked OK, only the 310 and 510 entries showed. So it ignored C2 (415). I'm not sure what I did wrong. Thanks again!
Posted by Mark W. on January 23, 2001 6:15 PM
Merle, make sure that the 415 in your criteria and database are the same data type -- 415 is not the same as "415". An easy way to check the data type of a value in a cell is to click on an empty cell, type an equal sign (=), click on the cell containing the value that you want to examine, and press Ctrl+=. What's displayed in the formula bar? ="415" or =415.
Posted by Merle Mason on January 24, 2001 1:11 PM
Mark,
Correct you are! I made sure the cells in list and the cells in the criteria were Text and lo and behold it filtered all three. Thank you so much again!
Merle
