Advanced Filter in a database containing blank fields

[shirleyj]

Hi Mr Excel,

I am struggling to get Excel’s Advanced Filter to work on a database, where not every field is populated. The problem I am experiencing is that Excel is treating a blank field as a criteria. When I run the Advanced Filter, any fields in the database that are not populated are filtered out of the results.

To try and illustrate the problem with a simple example: I have a database with two filter criteria: TYPE and CATEGORY. If I filter on type, it returns 100 results [correct]. If I filter on category, it returns 50 results, which is incorrect, as it should be 70. The reason it is returning an incorrect result is because it is treating the unpopulated/blank fields under TYPE as a criteria.

I have set up my criteria table with the below Boolean formulas: if the result of the formula is true (ie if it is blank), it returns “” and if it is false, it returns the selection.

=IF(ISBLANK(type),"",type)

Frustratingly, Excel Advanced Filter still treats the result of this formula as a criteria. So when I run the procedure, with only CATEGORY selected, all blank fields under the TYPE are filtered out of the results. The only way to get the Advanced Filter to produce the correct result is to delete the formula under TYPE from the criteria table – which of course defeats the object!

Does anyone know how to work around this problem? My intention is to incorporate the Advanced Filter into a VBA procedure, where the user will be able to select criteria under TYPE and CATEGORY from drop down menus.

I can think of only three possible solutions to the problem:

1. Ensure there are no blanks in the database (not really practical as it is not always possible to apply type or category)
2. Tweak the formula so that the Advanced Filter will not treat “” as a criteria
3. Write a VBA procedure, where the selections are written each time to the criteria table when the code is run (hoping to avoid this!)

Any ideas for solving this problem would be really appreciated!

Best regards,

Shirley

 

[Aladin Akyurek]

Care to post a sample of 10 records with TYPE and CATEGORY along with the desired results?

 

[shirleyj]

Hi Aladin,

No prob! How do you attach a file?

 

[shirleyj]

Just looked at the bottom, left of the screen and it says "you may not post attachments"

 

[Aladin Akyurek]

Hi Aladin,

No prob! How do you attach a file?

To post a sample on the board itself, try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste.

 

[shirleyj]

I cannot get the above add-ins to work. Is there anyway I can attach the Excel file directly?

 

[Aladin Akyurek]

I cannot get the above add-ins to work.

Did you try the last option which Internet Explorer?

You can also use the following method:

A1:A3

Item
JAD
LAD
GAD

Category
cat-1
cat-2
cat-1

Any would be helper can copy such sample directly into Excel and attempt to create a solution.

Is there anyway I can attach the Excel file directly?

No, there isn't. It is a thought-thru board policy.

 

[shirleyj]

No problem. I understand. I will persevere with the add-ins - and hopefully get them to work.

 

[Aladin Akyurek]

No problem. I understand. I will persevere with the add-ins - and hopefully get them to work.

As far as I know, those add-ins won't work on Mac, alas.

 

[shirleyj]

Yes, I suspect that they do not work on a Mac. Thank you for your help anyway .... appreciated.