Archive of Mr Excel Message Board
Back to Data in Excel archive index
Back to archive home
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!
Re: Any way to filter with more than two comparison criteria?
Posted by Mark W. on January 23, 2001 1:39 PM
Use an Advanced AutoFilter specifying a criteria.
Re: Any way to filter with more than two comparison 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.
Oops! This is a reply to another posting!
Posted by Mark W. on January 23, 2001 1:51 PM
Re: Any way to filter with more than two comparison criteria?
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!
Re: Any way to filter with more than two comparison criteria?
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.
Re: Any way to filter with more than two comparison criteria?
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
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.