1

I ran into something I can't really explain trying to filter a huge pile of data using the advanced filter...

I had a document with hundreds of thousands of lines (.xlsb) containing a whole bunch of products with their various attributes. One item, one line, attributes in columns. What I wanted to do was filter out (remove from the list) the items which had a Y in one of the columns (the other option being N), and also the items which were any one of a couple brands (another column) and also in a given category (yet another column). To put it another way, I wanted to remove anything with a Y in column V, and also anything that had AAA in column C and 1A2B in column D.

First I created the Y filter: I put ="=N" in that column up top, and tested it, and it worked great. Everything with Y in that column was removed, so far so good.

I then added one brand (AAA) into its own column, put <>1A2B into another, and ran the filter again. Instead of it filtering out the first line (the Ys) first and then, of the remaining items, filtering out the ones that were of the given brand (AAA) and the given category (1A2B), it completely ignored the second line. Nothing changed.

I even tried adding ="=N" into column V just to make sure, but that didn't change anything.

The category filter, in and of itself, works as expected. The Y/N filter, in and of itself, also works fine. But for some reason when put together, only the broader filter is executed. Any ideas?

The way I finally got it to work was by listing every possible brand (AAA, AAB, AAC, etc) individually, with ="=N" in column V. That way it was individually "allowing" every brand with an N in it, but this is far from an ideal solution. I was hoping having only the ="=N" in the line would achieve the same, but apparently not so.

Improve this question
6
  • Can you provide the simplest example you can test in Excel that produces this behavior?
    – Dane
    May 15, 2015 at 15:17
  • When I set up an Excel spreadsheet with the example criteria, it behaves as expected. You might be doing something that only becomes clear once we can see what you're doing instead of imagining it.
    – Dane
    May 15, 2015 at 15:24
  • How could I upload an example or example file?
    – RedAero
    May 15, 2015 at 15:38
  • I was just thinking that you might mock up your table in your question. If you use the code formatting option, the font will be monospaced, so you can get your columns to line up. Otherwise, your options are a publicly shared file on something like Dropbox or a screenshot.
    – Dane
    May 15, 2015 at 15:57
  • For example, here's the simple mockup I did based on reading your question: dropbox.com/s/6mb3nsbbxm7oa5d/AdvancedFilter.xlsx?dl=0
    – Dane
    May 15, 2015 at 17:25

2 Answers 2

Reset to default
0

Rather than trying to do this with horribly complex filters, it is much better, assuming you are free to add another column, to create a column with a formula that equates to true or false (or Y/N or whatever preference you have). Then you can apply the filter to just that column really easily.

Improve this answer
0

I think I figured out what you're trying to do with the Advanced Filter.

You need to use a single row for your criteria, because each row is an OR with the others. Meaning when you do the advanced filter, you get a row if it matches the criteria in any of the criteria rows. If you want a dependent criteria, such as "NOT AND", then you need to put that criteria in a new column and make your formula reference your first row of data.

In the following mockup, we want those with "N" in column B and if they have "AAA" in column C, then we only want it if it does not have "1A2B" in column D. So, in column E, we put a new formula that reads: =NOT(AND(C5="AAA",D5="1A2B"))

If you do that and run the advanced filter, you'll filter out rows 5-7 for having Beta = Y and you filter out 9 and 13 as well.

  |A     |B     |C     |D     |E
--+------+------+------+------+-------
 1|Alpha |Beta  |Gamma |Delta |Epsilon
 2|      |N     |      |      |FALSE
 3|      |      |      |      |
 4|Alpha |Beta  |Gamma |Delta |
 5|Andy  |Y     |AAA   |1A2B  |
 6|Bob   |Y     |BBB   |1A2B  |
 7|Chad  |Y     |AAA   |ZZ00  |
 8|Duke  |N     |BBB   |ZZ00  |
 9|Ed    |N     |AAA   |1A2B  |
10|Frank |N     |BBB   |1A2B  |
11|Gus   |N     |AAA   |ZZ00  |
12|Herb  |N     |BBB   |ZZ00  |
13|Indy  |N     |AAA   |1A2B  |
14|John  |N     |BBB   |1A2B  |
15|Kirk  |N     |AAA   |ZZ00  |
16|Luke  |N     |BBB   |ZZ00  |

Your other option is to have three rows of criteria (<>AAA & <>1A2B, AAA & <>1A2B, <>AAA & 1A2B), but that scales really poorly and gets unwieldy.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .