questions on advanced filter

[John Keith]

I'm trying to use an advanced filter to eliminate don't care rows from
a large data set and the filter is not behaving as I expect.

My dataset has 22203 rows plus a header row, I', reduced the data to
just the one column that I need to filter on.

My criteria set set is 155 rows plus an identical header row, again
only one column.

In all of my tests I have selected all 22204 row for the source data,
and I'm filtering in place (ultimately I will want to filter to a
different destination.) Also all of my filter criteria are of the
form:

<>*term1*
<>*term2* and so on

For the criteria I first selected just the header row and the first
criteria item and the correct number of rows were filter out, all is
good so far.

Then I undo this filter and select for the criteria range the header
and the first two rows. This time the filter result contains more rows
than the first test, and the result should be a smaller set.

Then I undo the previous result and I select for the filter range the
header row and the first three rows and this time there are no rows
filtered from the source, I am left with the original set.

I must be doing something wrong but I can't see it. Any suggestions?

Thanks


John Keith
(e-mail address removed)

 

[John Keith]

I should have noted I'm using Excel 2007 and WinXP.


John Keith
(e-mail address removed)

 

[John Keith]

After some further thinking I've solved the problem.

I orginally had the criteria in a column (as below)

<>*term1*
<>*term2* and so on

but since each criteria was a "NOT" function the criteria needed to be
configured as a "OR", thus in a row (with each column containing the
header) like this:

column 1 column 2 column 3
col 1 header col 2 header column 3 header
<>*term1* <>*term2* <>*term2* and so on



John Keith
(e-mail address removed)

 

[ozgrid.com]

You shouldn't need wildcard Characters as one has to force an exact match
with criteria like ="<>term1"

 

[John Keith]

You shouldn't need wildcard Characters as one has to force an exact match
with criteria like ="<>term1"

Dave,

I removed the wildcards and the results were clearly not correct.

I would need to try this on a much smaller dataset to get a handle on
what the filter is doing.

Thanks for the suggestion.



John Keith
(e-mail address removed)