Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - not =
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - not =
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "LiAD" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - not =
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter - not =
Thanks both of you.
Suspected I'd need another column - thats what I was hoping to avoid. Thanks for your help "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
filter: how to print filter list options in dropdown box | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |