Advanced Filter Criteria for Blanks and Nonblanks

[pompita]

Hi,

I apologize if this is somewhat of a repost. I've searched thoroughly for responses to my advanced filter issue and I seem to only come across responses for users of Excel 2003 and prior versions. I am using Excel 2007 and I wonder if the approach is different than older versions.

I am relatively new to Advanced Filters, but I understand the basics. My scenario is the following:

I have a list of data in cells A5:X7000 of Sheet1. My list headers are on row 5 of Sheet1. I have my criteria on Sheet2, cells A1:A2. On Sheet3, I would like to have a new, filtered list that includes all of the data on my Sheet1 list where the data in column A is a blank. Any nonblank cells in column A contain only text values. I have tried each of the following criteria to accomplish this with no luck.

=<>
<>
<>=
<>"="


So the question is three parts:

What is the necessary criteria for an advanced filter of the list on Sheet1 so that only data with NOTHING in column A is listed on Sheet3?

What is the necessary criteria for an advanced filter of the list on Sheet1 so that only data with A VALUE in column A is listed on Sheet3?

Does the response to the above two questions differ if the data in Column A is instead NUMERIC DATA (not text)?

I really am at a stopping point and I am having zero success with this last issue. Please help or let me know if I need to clarify anything.

Thanks for your help. I use this forum a ton, despite my infrequent postings.

Regards,
Alex

 

[Aladin Akyurek]

Sheet3

Leave A1 empty.

A2:

=Sheet1!$A6<>""

Select A1:A2, fire up Advaced Filter with List range, etc. fiiled in appropriately.

 

[mikerickson]

In Sheet2! A1, enter the formula =Sheet1!A5
Sheet2! A2, the text '=?*

Select Sheet3 and press AdvancedFilter

Copy to another location
List Range: Sheet1!A5:X700
Criteria Range: Sheet2!A1:A2
Copy To: Sheet3!A1

If sheet 3 isn't active when AdvancedFilter is invoked, a misleading error message appears.

 

[pompita]

I think both of these work. Mike, I like yours because it doesn't involve a formula. That's perfect.

I will now try and create a macro that performs an advanced filter on the defined criteria. Wish me luck!

Thanks for your help. I'll repost if I run into any issues.

Alex

 

[pompita]

Hey Mike,

The format you provided works great with text, but it does not filter properly when the data is numbers, not text.

Any thoughts?

 

[mikerickson]

With everything else the same try in sheet2 A2 try the text '<>

 

[pompita]

No dice.

Is there anything else I can provide you to help in this exercise? Quite honestly, I have a few different criteria, all of which function perfectly thanks to your previous advice. This last one is the only hangup I have left.

I don't understand why it doesn't work. I even tried the following two criteria >0 and <=0 and it still shows me blank cells. Very frustrating.

 

[mikerickson]

Similar to Alan's approach, try leaving Sheet2! A1 blank and put the formula
=NOT(ISBLANK(Sheet1!A2))
in Sheet2!A2 (no $ in the formula is important.)