Filter - not =

  • Thread starter Thread starter LiAD
  • Start date Start date
L

LiAD

Hi,

I have a database of products, times usual type data, text etc. I would
like to apply a filter to see certain items in a particular list. For
reference I have a possible 15 different items in col A and I would like to
find some way of applying a filter so that I can see all but a specified 8 of
them. For example I have the following list

A 2 4T 8
B 3 1U 3
C 5 5F 23
D 7 3T 8
E 5 1P 4
B 2 6Y 2

If I would like to see anything that does not equal A or B or C or D in
column 1 how can I apply a filter to do this without just telling excel to
filter for E? I can only see two conditions in the excel filter but I need a
does not equal for 8.

Is this possible?

Thanks
LiAD
 
Hi,

One solution is to upgrade to 2007.

In 2003 unless you can find a common characteristic you will probably need a
dummy column:

Suppose you enter all the items you don't want to display in I1:I4 then in
cell E1 enter

=SUMPRODUCT(--(A2<>$I$1:$I$4))<COUNTA($I$1:$I$4)

and copy it down. Filter this column on FALSE
 
LiAD,

Make a list of the 8 values that you do not want to see in a column somewhere, then add a column of
formulas to your table like

=ISERROR(MATCH(A2,YourList,False))

with fixed reference on the list, like so:

=ISERROR(MATCH(A2,$T$2:$T$9,False))

and copy yoru formula down to match your table. Then filter on that column to show TRUE.

HTH,
Bernie
MS Excel MVP
 
Thanks both of you.

Suspected I'd need another column - thats what I was hoping to avoid.

Thanks for your help
 
Back
Top