With adavnced filter, how do I exclude a value?

[nmss18]

Hello,
I am trying to use the advanced criter filter to filter OUT a value. For example. In the table below, how do I display everything but beverages. I am using the <> to filter this out but it is not working.
Any assistance would be grateful.
Thanks,
nmss18

[TABLE="width: 336"]
<tbody>[TR]
[TD]<form id="aspnetForm" method="post" name="aspnetForm" action="http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx">Type</form>[/TD]
[TD]Salesperson[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]<>Beverages[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]<form id="aspnetForm" method="post" name="aspnetForm" action="http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx">Type</form>[/TD]
[TD]Salesperson[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Beverages[/TD]
[TD]Suyama[/TD]
[TD="align: right"]$5,122 [/TD]
[/TR]
[TR]
[TD]Meat[/TD]
[TD]Davolio[/TD]
[TD="align: right"]$450 [/TD]
[/TR]
[TR]
[TD]produce[/TD]
[TD]Buchanan[/TD]
[TD="align: right"]$6,328 [/TD]
[/TR]
[TR]
[TD]Produce[/TD]
[TD]Davolio[/TD]
[TD="align: right"]$6,544 [/TD]
[/TR]
</tbody><colgroup><col span="3"></colgroup>[/TABLE]

 

[Aladin Akyurek]

Aladin,
Your solution worked. Thanks much.

You are welcome. Thanks for providing feedback.

Do you have a way to reference the exclusion list from another worksheet? because, my list of email addresses to exclude could be significantly large and I would rather place the exclusion list on another worksheet to avoid the clutter.
...

You can set up the exclude list in a different area on a different sheet... For example:

=ISNA(MATCH($A9,Exclude!$A$1:$A$2,0))

where the items to exclude are in A1:A2 on the Exclude sheet.

 

[pmich]

Aladin, I tried your exact example and it works fine. Thanks for the code.
How to exclude if BEVERAGES or BeverageS?

 

[pmich]

It is excluding Beverages and Produce. Fine. It is not filtering if data is Good Beverages and Nice Produce. It should. Further it should not filter BEVERAGES and ProDuce. Please help.

 

[pmich]

Cross posted here

 

[Aladin Akyurek]

Aladin, I tried your exact example and it works fine. Thanks for the code.
How to exclude if BEVERAGES or BeverageS?

It is excluding Beverages and Produce. Fine. It is not filtering if data is Good Beverages and Nice Produce. It should. Further it should not filter BEVERAGES and ProDuce. Please help.

Change the MATCH formula in A5 to:

=SUMPRODUCT(ISNUMBER(FIND($A$1:$A$2,$A9))+0)=0

Note that an item either partially or as whole must equal the items in A1:A2 for exclusion to work.

 

[pmich]

THANKS Aladin Akyurek,
Works perfectly.
Appreciate your help. Learning new things.
Hope others also will be befitted by your suggestions.
Thanks once again.

 

[Aladin Akyurek]

THANKS Aladin Akyurek,
Works perfectly.
Appreciate your help. Learning new things.
Hope others also will be befitted by your suggestions.
Thanks once again.

You are welcome. Thanks for providing feedback.

 

[ktrasler]

Hi

This is just what I've been after but I need one extra thing.

Can this work with not including certain criteria and not including blank cells

Thanks

 

[Aladin Akyurek]

Hi

This is just what I've been after but I need one extra thing.

Can this work with not including certain criteria and not including blank cells

Thanks

Which set up or formula are you addressing?

 

[ktrasler]

Hi
Can this work with not including certain criteria and not including blank cells
Thanks

I think I have done this, but please say if there is a better solution

=IF(AND(COUNTBLANK($BG$6:$BG$12)>0,ISBLANK(BG21)),FALSE,ISNA(MATCH(BG21,$BG$6:$BG$12,0)))

Thanks