Thread: Autofilter "dead zones"

Ok, I have a question regarding autofilters and their logic.

In some large spreadsheets, we have autofiltered data where applying filters in different orders yields different final lists. That is, applying filter A, then B, then C, won't show the same rows as applying C, then B, then A.

I'm certain that this has to do with the placement of the data in the sheets, and probably with the order in which the columns are presented.

Does anyone know of a good reference that explains Autofilter data set best pracitices, to avoid getting inconsistent results? Do columns need to be sorted left to right by number of entries, or is the algorithm for ensuring consistent sorting more complicated than that?

Thanks in advance to anyone who can provide insight or additional info!

-Adam

if you filter say a on =1 then any values from b or c will be hidden if a<>1
you need advanced filter using OR criteria
see http://www.contextures.com/xladvfilter01.html
if you get stuck come back

martindwilson,

Thanks for the link, but that's not the problem that I have. I get the correct values when I filter A->B->C, so the logical AND in the default filter function is working as desired. If I filter C->B->A, I also get some results, but not the entire set. That is, if I do ABC, I may get 10 results, and that is the complete, accurate set of data matching my filters. If I do CBA, I may get 7 results, all of which were on the original ABC list, but there are also 3 additional results that aren't shown.

I assume that the logical AND is commutative for autofilters, and so the order of operations shouldn't matter, but I'm finding that it does.

-Adam

cant be. as each filter is applied it only filters on what's visible
so filtering by col c first may remove some of column a that you want
thats why advanced filter exists