Filtering a filter list

  • Thread starter Thread starter rwrussell
  • Start date Start date
R

rwrussell

I'm using Excel 2000. I have used advanced filtering to extract a se
of data from one worksheet to another worksheet. I now want t
eliminate some of this filtered data to further refine my search. If
use the auto filter feature ("does not contain") on this new workshee
it does what I need but only allows for 2 criteria and I have 5 or mor
that I need to filter out.

Is there a way to expand the auto filter to allow more than 2 filters?
If not, I assume I can create a criteria table, but what is the forma
for "does not contain"?

Thanks
 
One way if you do not want to use VBA, try adding 5 helper columns on the
side of your data that return true/false for each of the 5 criteria. Then
use auto filters on the helper columns to hide either the true or the false
values depending on what you are looking for.

HTH,

ryanb.
 
You can list the five criteria on the worksheet, and add a column to the
table, to check the row against the criteria list.

For example, list the five criteria in cells K1:K5

Insert a column in the table, with a heading in the first row, e.g. "Found"
In the first data row, enter a formula that refers to the criteria
cells, and the cell being checked, e.g.:


=OR(ISNUMBER(SEARCH($K$1,C2)),ISNUMBER(SEARCH($K$2,C2)),ISNUMBER(SEARCH($K$3,C2)),ISNUMBER(SEARCH($K$4,C2)),ISNUMBER(SEARCH($K$5,C2)))

Copy the formula down to the last row of data
Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in the "Found" heading cell, choose FALSE
 
Thank you for the suggestions, I'll give them a try.

Just to be sure I'm not missing the obvious here (which I often am!) --
I want to be sure there is not a similar and equally easy way to do
this as there is in Quattro Pro. With QP you can run a query and for
all matching records in the criteria field you have the option to
delete them. It's all built in to their query function.

Is there a similar "filter" option in Excel?

Thanks again for your help.
 
Back
Top