Filter - not =

[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

 

[Shane Devenshire]

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

 

[Bernie Deitrick]

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

 

[LiAD]

Thanks both of you.

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

Thanks for your help