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.