Can I use Autofilter WITH Advanced Filter? Also, Advanced Filter won't filter "<>0"

[RockandGrohl]

Hi guys, got a double whammy here.


I'm using AutoFilter to chop down a 300,000 line database to the relevant fields - bought within a timeframe, are eligible to be mailed, etc.

I also want to use Advanced Filter to filter through six different product columns so that one filter doesn't exclude the other - so if a customer has bought a "N" product, or a "P" product, they aren't filtered out, whereas if I used regular autofilter they would have had to have bought "N" AND "P".

The problem here is that if I use one type of filter, it removes the other, whereas just using Autofilter results in additive filtering which is useful for chopping down a list of clients.


Also I'm having an issue with my advanced filter. I figured if there's no way to make advanced & autofilters play nice, then I can write out the criteria and apply the big filter that way, using it for both "OR" & "AND" operations.

No matter what, I can't get the following critieria to apply:

Landmail_Optin

<>0

The column header is definitely correct and <>0 should be a legal operation. If I use a regular filter and select "Does not contain" and then write "0" - it produces the same result. I need to find 1's and blanks.

Cheers!

 

[Alex Blakenburg]

1) You are correct you can't use Advanced Filter and AutoFilter at the same time.
2) <>0 definitely works in Microsoft 365, it filters out the 0 and leaves the blanks and other values.
Your profile says you are on 2010, is that really the case ? I can't test if it works in 2010.
I am assuming that if you filter for 1 it works (just as proof that your filter ranges are set up correctly)

Row 12 in the below has the zero in it.

20210330 AutoFilter Not Zero.xlsx
	A
1	Col1
2	<>0
3
4
5	Col1
6	1
7	3
8	4
9
10	5
11	6
13	8
14
Sheet1

Cell Formulas
Range	Formula
A9	A9	=""

 

[RockandGrohl]

Unfortunately that really, really is the case.

I've tested it on another sheet with dummy data and <>0 appears to work. However, when I use on my database sheet (which comes from a CSV), <>0 does not appear to work. However, filtering this manually works every time.

I'll try filtering for 1 and see what I get.

 

[RockandGrohl]

1) You are correct you can't use Advanced Filter and AutoFilter at the same time.
2) <>0 definitely works in Microsoft 365, it filters out the 0 and leaves the blanks and other values.
Your profile says you are on 2010, is that really the case ? I can't test if it works in 2010.
I am assuming that if you filter for 1 it works (just as proof that your filter ranges are set up correctly)

Row 12 in the below has the zero in it.

20210330 AutoFilter Not Zero.xlsx
	A
1	Col1
2	<>0
3
4
5	Col1
6	1
7	3
8	4
9
10	5
11	6
13	8
14
Sheet1

Cell Formulas
Range	Formula
A9	A9	=""

Hi Alex,

I've just had another go and it appears that <>0 is now just returning 1's and not blanks.

I need to return 0's or blanks. The blanks are natural blanks, and not the result of a formula. Thanks.

 

[Alex Blakenburg]

Hi Alex,

I've just had another go and it appears that <>0 is now just returning 1's and not blanks.

I need to return 0's or blanks. The blanks are natural blanks, and not the result of a formula. Thanks.

The last statement contradicts the previous ones. Can you clarify the requirement.
The original requirement said you wanted to return 1 and blank.
The last statement says you want to return 0 and blank.

I have tested on MS 365 and <>0 does return the following:-
• 1
• blank whether that is "" or empty (what you called natural blank)

If you want 0 and blank (both "" and empty), then <>1 works in MS 365.

You didn't clarify whether you are in fact using 2010.
If nothing else works for you, you may need to consider using a helper column and using that in your selection criteria.

 

[RockandGrohl]

Hi Alex,

Apologies

I am on Excel 2010, believe it or not.

I have a column with 0's, 1's and blanks. The blanks are true blanks and not derived from formula in any way.

I am trying to filter out the 0's to provide me with just 1's and blanks.


Thanks.

 

[Alex Blakenburg]

<>0 works for me and I have tried it with loading in a csv file.
I have no way of testing it on 2010.
If you can't get it to work, you might need to go the Helper column route. eg =If(Col_CurrentRow=0,"EXCLUDE","INCLUDE") and filter on that.

 

[RockandGrohl]

I just tested it on Excel 365 on my home PC, not working. Still only returns 1's.

This is my filter criteria exactly:

Brochure_Region	OptIn_NH_LandMail	Last_Booked	Last_Booked	FamilyFun	FamilyFun	Sports	Sports
<>0	>0	>=43466	<=43830	>=1	<=10000
<>0	>0	>=43101	<=43465			>=1	<=10000

 

[RoryA]

>0 and <>0 are not the same thing.

 

[RockandGrohl]

Not sure what rules are on this, here's a chopped down, redacted version.

https://filebin.net/he4toq4286qp6dxz/NH_MLB_Problem_Extract.xlsm?t=0u93zkkh

First tab are the list of clients, second is the criteria.