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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
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
1Col1
2<>0
3
4
5Col1
61
73
84
9 
105
116
138
14
Sheet1
Cell Formulas
RangeFormula
A9A9=""
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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
1Col1
2<>0
3
4
5Col1
61
73
84
9 
105
116
138
14
Sheet1
Cell Formulas
RangeFormula
A9A9=""


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

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

<>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.
 
Solution

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
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_RegionOptIn_NH_LandMailLast_BookedLast_BookedFamilyFunFamilyFunSportsSports
<>0>0>=43466<=43830>=1<=10000
<>0>0>=43101<=43465>=1<=10000
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
>0 and <>0 are not the same thing.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,104
Messages
5,640,128
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top