Excel 97 - Advanced Filter - Text and Blanks 2

[hotboxsal]

Hi Guys,

How do I filter the column 'Course' (as shown below) for values : 'AD1', 'AD2', 'NFI' and include blanks in same query ?
Here are the columns :

ID Course
---- ------
12 NXP
222 AD2
133 NR
177 AD1
244
130 NFI
120
589 AD1

REQUIRED RESULTS
ID Course
---- ------
222 AD2
177 AD1
244
130 NFI
120
589 AD1

[ul][li]I've tried Advanced Filter using a criteria of the column header 'Course' and AD1, AD2, NFI and a blank cell in various positions. None of these work - I can get AD1, AD2, NFI to work on their own using the Criteria.[/li]
[li]I don't want to do this filter in 2 'runs' and combine the results due to how I'll be using this feature later.[/li]
[li]I can't sort the original columns as they must be in this order.[/li][/ul]

Any ideas ?

Many Thanks

Sal

 

[SkipVought]

Hi,
Use Advanced Filter and for the EMPTY cell criteria use...

="="

Workd for me
Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Sal,

I've put together a "working model" that does exactly what you describe, and MORE.

Contrary to a bug in Excel that generates an ERROR message when one attempts to manually (via the menu) use Data - Filter - Advanced Filter - to copy data to a separate sheet, it IS possible.

This model demonstrates via code attached to a "macro button" that it is indeed possible, and is VERY useful for generating various reports - based on "specific criteria" that extracts and "isolates" that specific data for which you wish to generate reports.

The model also demonstrates the "database formula" - that can be used with separate criteria to produce a "matrix" or a summary of totals. These SAME criteria can be used in the extraction process - to extact and isolate the "data behind any particular total".

If you would like this example file, just email me - at my HOME address below - and I'll send the file via return email.

If ANYONE ELSE would like a copy of the file, please don't hesitate to ask.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[hotboxsal]

Hi Guys,

Thanks to you both

Skip -
That solution worked a treat - thanks very much - I can't believe how straight forward it seems but you've saved me a lot of time


Dale -
Thanks very much for your reply - I've come across the ERROR msg in copying data to a separate sheet.
I'll be in touch about your example file, as I'm currently working on a lengthy macro and could do with this facility working properly


Cheers

Sal

 

[hotboxsal]

Hi Dale,

Excellent !!!

Thanks very much for the file and solution - I passed it to a colleague who'd also been struggling with the same problem.
I appreciate the comments and notes you included on how you achieved each
step.

This should prove useful to anyone else defeated by this Excel Bug (Advanced Filter - Copy Data to another worksheet) - just email Dale as shown above

Cheers

Sal