With adavnced filter, how do I exclude a value?

N

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

[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]
 
Sort by date Sort by votes
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
[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
1​
[/td][td]
Type​
[/td][td]
Salesperson​
[/td][td]
Sales​
[/td][/tr]

[tr][td]
2​
[/td][td]
Beverages​
[/td][td]
Suyama​
[/td][td]
$5,122​
[/td][/tr]

[tr][td]
3​
[/td][td]
Meat​
[/td][td]
Davolio​
[/td][td]
$450​
[/td][/tr]

[tr][td]
4​
[/td][td]
produce​
[/td][td]
Buchanan​
[/td][td]
$6,328​
[/td][/tr]

[tr][td]
5​
[/td][td]
Produce​
[/td][td]
Davolio​
[/td][td]
$6,544​
[/td][/tr]
[/table]

Data Range
[Table="class: grid"][tr][td] [/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][/tr]
[tr][td]
1​
[/td][td]
Type​
[/td][td]
Salesperson​
[/td][td]
Sales​
[/td][/tr]

[tr][td]
2​
[/td][td]
<>Beverages​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
3​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
4​
[/td][td]
Type​
[/td][td]
Salesperson​
[/td][td]
Sales​
[/td][/tr]

[tr][td]
5​
[/td][td]
Meat​
[/td][td]
Davolio​
[/td][td]
$450​
[/td][/tr]

[tr][td]
6​
[/td][td]
produce​
[/td][td]
Buchanan​
[/td][td]
$6,328​
[/td][/tr]

[tr][td]
7​
[/td][td]
Produce​
[/td][td]
Davolio​
[/td][td]
$6,544​
[/td][/tr]
[/table]
 
Upvote 0
nmss18 said:
Hmm. that didnt work.
I'll have to take a closer look at the link you sent me.
Click to expand...

[TABLE="width: 207"]
<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>[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]Beverages[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"] [/TD]
[/TR]
<FORM id=aspnetForm method=post name=aspnetForm action=http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP010073942.aspx></FORM>[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]<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="class: xl65, width: 102, bgcolor: transparent"]Salesperson[/TD]
[TD="class: xl65, width: 66, bgcolor: transparent"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]<>Beverages[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]<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="class: xl65, width: 102, bgcolor: transparent"]Salesperson[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]Beverages[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"]Suyama[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]$5,122 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]Meat[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"]Davolio[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]$450 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]produce[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"]Buchanan[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]$6,328 [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 106, bgcolor: transparent"]Produce[/TD]
[TD="class: xl63, width: 102, bgcolor: transparent"]Davolio[/TD]
[TD="class: xl64, width: 66, bgcolor: transparent"]$6,544 [/TD]
[/TR]
</TBODY>[/TABLE]

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
nmss18 said:
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?
Click to expand...

Maybe this:

[TABLE="width: 650"]
<tbody>[TR]
[TD="class: xl66, width: 56, bgcolor: transparent"]Type
[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Salesperson
[/TD]
[TD="class: xl66, width: 40, bgcolor: transparent"]Sales
[/TD]
[TD="class: xl66, width: 115, bgcolor: transparent"]Email
[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 285, bgcolor: transparent"]CalcCriteria
[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 56, bgcolor: transparent"]Type
[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Salesperson
[/TD]
[TD="class: xl66, width: 40, bgcolor: transparent"]Sales
[/TD]
[TD="class: xl66, width: 115, bgcolor: transparent"]Email
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Beverages
[/TD]
[TD="class: xl65, bgcolor: transparent"]Suyama
[/TD]
[TD="class: xl65, bgcolor: transparent"]$5,122
[/TD]
[TD="class: xl65, bgcolor: transparent"]suyama@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow, align: center"]FALSO
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buchanan
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,328
[/TD]
[TD="class: xl65, bgcolor: transparent"]buchanan@email.com
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Meat
[/TD]
[TD="class: xl65, bgcolor: transparent"]Davolio
[/TD]
[TD="class: xl65, bgcolor: transparent"]$450
[/TD]
[TD="class: xl65, bgcolor: transparent"]davolio@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buchanan
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,328
[/TD]
[TD="class: xl65, bgcolor: transparent"]buchanan@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow"]Formula
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Davolio
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,544
[/TD]
[TD="class: xl65, bgcolor: transparent"]davolio@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow"]=AND(D2<>{"davolio@email.com";"suyama@email.com"})
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"]***********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********************
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]******************************************************
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"]***********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********************
[/TD]
[/TR]
</tbody>[/TABLE]

Or this:
[TABLE="width: 650"]
<tbody>[TR]
[TD="class: xl66, width: 56, bgcolor: transparent"]Type
[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Salesperson
[/TD]
[TD="class: xl66, width: 40, bgcolor: transparent"]Sales
[/TD]
[TD="class: xl66, width: 115, bgcolor: transparent"]Email
[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 285, bgcolor: transparent"]CalcCriteria
[/TD]
[TD="class: xl65, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 56, bgcolor: transparent"]Type
[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Salesperson
[/TD]
[TD="class: xl66, width: 40, bgcolor: transparent"]Sales
[/TD]
[TD="class: xl66, width: 115, bgcolor: transparent"]Email
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Beverages
[/TD]
[TD="class: xl65, bgcolor: transparent"]Suyama
[/TD]
[TD="class: xl65, bgcolor: transparent"]$5,122
[/TD]
[TD="class: xl65, bgcolor: transparent"]suyama@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow, align: center"]FALSO
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buchanan
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,328
[/TD]
[TD="class: xl65, bgcolor: transparent"]buchanan@email.com
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Meat
[/TD]
[TD="class: xl65, bgcolor: transparent"]Davolio
[/TD]
[TD="class: xl65, bgcolor: transparent"]$450
[/TD]
[TD="class: xl65, bgcolor: transparent"]davolio@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Buchanan
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,328
[/TD]
[TD="class: xl65, bgcolor: transparent"]buchanan@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]davolio@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Produce
[/TD]
[TD="class: xl65, bgcolor: transparent"]Davolio
[/TD]
[TD="class: xl65, bgcolor: transparent"]$6,544
[/TD]
[TD="class: xl65, bgcolor: transparent"]davolio@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]suyama@email.com
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow"]Formula
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: yellow"]=COUNTIF($F$4:$F$5,D2)=0
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"]***********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********************
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]******************************************************
[/TD]
[TD="class: xl65, bgcolor: transparent"]**
[/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[TD="class: xl65, bgcolor: transparent"]***********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]********************
[/TD]
[/TR]
</tbody>[/TABLE]



Markmzz
 
Last edited:
Upvote 0
nmss18 said:
I realized what I did wrong. It works now.
Click to expand...

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.
Click to expand...

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?
Click to expand...

Try to follow the following set up...


[TABLE="width: 479"]
<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>[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]Beverages[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Type[/TD]
[TD="class: xl67, width: 98, bgcolor: transparent"]Salesperson[/TD]
[TD="class: xl68, width: 74, bgcolor: transparent"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]produce[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Meat[/TD]
[TD="class: xl65, width: 98, bgcolor: transparent"]Davolio[/TD]
[TD="class: xl66, width: 74, bgcolor: transparent"]$450 [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl70, width: 106, bgcolor: #fde9d9"][/TD]
[TD="class: xl70, width: 102, bgcolor: #fde9d9"]Salesperson[/TD]
[TD="class: xl70, width: 66, bgcolor: #fde9d9"]Sales[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, width: 106, bgcolor: #fde9d9, align: center"]FALSE[/TD]
[TD="class: xl71, width: 102, bgcolor: #fde9d9"] [/TD]
[TD="class: xl71, width: 66, bgcolor: #fde9d9"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 106, bgcolor: transparent"]Type[/TD]
[TD="class: xl67, width: 102, bgcolor: transparent"]Salesperson[/TD]
[TD="class: xl68, width: 66, bgcolor: transparent"]Sales[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]Beverages[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"]Suyama[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]$5,122 [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]Meat[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"]Davolio[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]$450 [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]produce[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"]Buchanan[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]$6,328 [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 106, bgcolor: transparent"]Produce[/TD]
[TD="class: xl65, width: 102, bgcolor: transparent"]Davolio[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent"]$6,544 [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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
nmss18 said:
Markmzz,
I am trying to figure out what you did here. Could you please clarify?
Thanks,
nmss18
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Advanced filtering by "not in this column" criteria
Replies
7
Views
458
Aladin Akyurek
Aladin Akyurek
D
  • Question Question
How to extract just numbers if i know the start point but not the end in a text string.
2
Replies
17
Views
1K
Rick Rothstein
Rick Rothstein
W
  • Question Question
Pass Parameters to Javascript Webpage
Replies
1
Views
1K
whose2know
W

Share this page

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