With adavnced filter, how do I exclude a value?

nmss18

Active Member
Joined
Jun 28, 2011
Messages
312
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

<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>SalespersonSales
<>Beverages
<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>SalespersonSales
BeveragesSuyama$5,122
MeatDavolio$450
produceBuchanan$6,328
ProduceDavolio$6,544

<tbody>
</tbody><colgroup><col span="3"></colgroup>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Here is what I did...

Advanced Filter
  • Action: Copy to another location
  • List range: $A$1:$C$5
  • Criteria range: $F$1:$H$2
  • Copy to: $F$4:$H$4
  • OK

If you want to filter in place, under Action, check Filter the list, in-place...

F2, even thought it looks like <>Beverages, in the formula bar it is actually ="<>Beverages"

Data Range
A​
B​
C​
1​
Type​
Salesperson​
Sales​
2​
Beverages​
Suyama​
$5,122​
3​
Meat​
Davolio​
$450​
4​
produce​
Buchanan​
$6,328​
5​
Produce​
Davolio​
$6,544​

Data Range
F​
G​
H​
1​
Type​
Salesperson​
Sales​
2​
<>Beverages​
3​
4​
Type​
Salesperson​
Sales​
5​
Meat​
Davolio​
$450​
6​
produce​
Buchanan​
$6,328​
7​
Produce​
Davolio​
$6,544​
 
Upvote 0
Hmm. that didnt work.
I'll have to take a closer look at the link you sent me.

Beverages
<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>SalespersonSales
<>Beverages
<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>SalespersonSales
BeveragesSuyama$5,122
MeatDavolio$450
produceBuchanan$6,328
ProduceDavolio$6,544

<COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2360" width=66><TBODY>
<FORM id=aspnetForm method=post name=aspnetForm action=http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx> </FORM>
</TBODY>

A1: Beverages

Criteria range is A4:C5, where A5 houses the following formula:

="<>"&$A$1

Run Advanced Filter the foregoing criteria range.
 
Upvote 0
I realized what I did wrong. It works now. Using this formula ="<>"&$A$1 would not work for me since I have a huge spreadsheet and I dont want to exclude a cell, I need to exclude a value.

However, now I came across a similar issue. If I have a huge list in a column of email address values (numbering in the thousands), and I want to exclude 10-20 (maybe even more) of these email addresses.
I tried using the ="<>john@abc.com" formula but it didnt work. I suspect that the @ symbol is blocking this.

Any suggestions?
 
Upvote 0
I realized what I did wrong. It works now. Using this formula ="<>"&$A$1 would not work for me since I have a huge spreadsheet and I dont want to exclude a cell, I need to exclude a value.

However, now I came across a similar issue. If I have a huge list in a column of email address values (numbering in the thousands), and I want to exclude 10-20 (maybe even more) of these email addresses.
I tried using the ="<>john@abc.com" formula but it didnt work. I suspect that the @ symbol is blocking this.

Any suggestions?

Maybe this:

Type
Salesperson
Sales
Email
CalcCriteria
Type
Salesperson
Sales
Email
Beverages
Suyama
$5,122
suyama@email.com
FALSO
produce
Buchanan
$6,328
buchanan@email.com
Meat
Davolio
$450
davolio@email.com
produce
Buchanan
$6,328
buchanan@email.com
Formula
Produce
Davolio
$6,544
davolio@email.com
=AND(D2<>{"davolio@email.com";"suyama@email.com"})
*********
***********
******
********************
**
******************************************************
**
*********
***********
******
********************

<tbody>
</tbody>

Or this:
Type
Salesperson
Sales
Email
CalcCriteria
Type
Salesperson
Sales
Email
Beverages
Suyama
$5,122
suyama@email.com
FALSO
produce
Buchanan
$6,328
buchanan@email.com
Meat
Davolio
$450
davolio@email.com
produce
Buchanan
$6,328
buchanan@email.com
davolio@email.com
Produce
Davolio
$6,544
davolio@email.com
suyama@email.com
Formula
=COUNTIF($F$4:$F$5,D2)=0
*********
***********
******
********************
**
******************************************************
**
*********
***********
******
********************

<tbody>
</tbody>



Markmzz
 
Last edited:
Upvote 0
I realized what I did wrong. It works now.

Fine, but you are wrong on...

Using this formula ="<>"&$A$1 would not work for me since I have a huge spreadsheet and I dont want to exclude a cell, I need to exclude a value.

for it works for me and it excludes the values specified by A1, not a cell.

However, now I came across a similar issue. If I have a huge list in a column of email address values (numbering in the thousands), and I want to exclude 10-20 (maybe even more) of these email addresses.
I tried using the ="<>john@abc.com" formula but it didnt work. I suspect that the @ symbol is blocking this.

Any suggestions?

Try to follow the following set up...


Beverages TypeSalespersonSales
produce MeatDavolio$450
SalespersonSales
FALSE
TypeSalespersonSales
BeveragesSuyama$5,122
MeatDavolio$450
produceBuchanan$6,328
ProduceDavolio$6,544

<COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2360" width=66><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><TBODY>
</TBODY>

A4:C5 constitutes the criteria range.

A4 must be empty.

A5 houses a formula that uses an exclude list in A1:A2.

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

B5 and C5 are empty.

Advanced Filter using A4:C5 as criteria range produces the result in F:H.
 
Upvote 0
Aladin,
Your solution worked. Thanks much. 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.

Markmzz,
I am trying to figure out what you did here. Could you please clarify?
Thanks,
nmss18
 
Upvote 0
Markmzz,
I am trying to figure out what you did here. Could you please clarify?
Thanks,
nmss18

No problem.

In the first example, the formula:

=AND(D2<>{"davolio@email.com";"suyama@email.com"})

you have to put in F2.

PS: FALSO = FALSE

In the second example, the formula:

=COUNTIF($F$4:$F$5,D2)=0

you have to put in F2.

PS: FALSO = FALSE

And in both cases F1:F2 is the criteria range.

I hope that this helps.

Markmzz
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top