Advanced filtering by "not in this column" criteria

[dabenavidesd]

Hi, I wanted to filter by "not in this column criteria" in boolean form ... OR ... OR like this:
[TABLE="width: 267"]
<TBODY>[TR]
[TD]Type</SPAN>[/TD]
[TD]Vendor</SPAN>[/TD]
[TD]Sales</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]<>Davolio</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]<>Buchanan</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]=Suyama</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Type</SPAN>[/TD]
[TD]Vendor</SPAN>[/TD]
[TD]Sales</SPAN>[/TD]
[/TR]
[TR]
[TD]beverage</SPAN>[/TD]
[TD]Suyama</SPAN>[/TD]
[TD]5122 $</SPAN>[/TD]
[/TR]
[TR]
[TD]Meet</SPAN>[/TD]
[TD]Davolio</SPAN>[/TD]
[TD]450 $</SPAN>[/TD]
[/TR]
[TR]
[TD]food</SPAN>[/TD]
[TD]Buchanan</SPAN>[/TD]
[TD]6328 $</SPAN>[/TD]
[/TR]
[TR]
[TD]food</SPAN>[/TD]
[TD]Davolio</SPAN>[/TD]
[TD]6544 $</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

And it turns out it doesn't filter anything.
Note 1: I can't make this example smaller, because it will work if I just leave 1 criteria like <>Davolio, but I want dismiss hundreds of criteria from thousand rows table.
Note 2: Actual contents of criteria tange is:
="<>Davolio"
="<>Buchanan"
="=Suyama"
Can you explain me why is this behavior ocurring?
Thanks in advance

 

[ShawnPCooke]

I think it may be the use of OR. "<>Davolio" OR "<>Buchanan" will be true for Davolio (because it doesn't equal Buchanan) and true for Buchanan (because it doesn't equal Davolio.) I'm guessing that what you want is an AND between them, which would exclude both of them?

 

[bruderbell]

What do you think the result should be? ShawnPCooke has given a good explanation of using <> in an OR fashion - you get all results.

Also, you don't need the cell to be ="<>Davolio"
Just <>Davolio works. It's only equalities where you have to get creative - see the help article. Filter by using advanced criteria - Excel

Your method works, but seems to be more laborious.

 

[dabenavidesd]

I think it may be the use of OR. "<>Davolio" OR "<>Buchanan" will be true for Davolio (because it doesn't equal Buchanan) and true for Buchanan (because it doesn't equal Davolio.) I'm guessing that what you want is an AND between them, which would exclude both of them?

Correct, that was the problem, and yes, I want to AND them, but how can I do that? By having hundreds of Vendor columns? Or is there any sort of packed formula I can write to do that, I haven't found any examples with "<>" - not equal operator. Can you enlighten me?

Thanks in advance

 

[ShawnPCooke]

To help you further, we really need to get a better understanding of what results you're looking for. Could you describe what you specifically want to include, or what you specifically want to exclude?

 

[dabenavidesd]

To help you further, we really need to get a better understanding of what results you're looking for. Could you describe what you specifically want to include, or what you specifically want to exclude?

Yes, I want to exclude all that is in criteria column (that's to say all names listed before <> sign; I just put ="=Suyama" for illustration purposes to show I wanted to include it after applying filter), but as I said I wanted to apply the filter on thousands range list by hundreds of "not equal" range criteria for specific column, is there a way of doing this by ANDs or formula in one cell or is it need to be hundreds of those columns in the range criteria?

Thanks in advance

 

[dabenavidesd]

Yes, I want to exclude all that is in criteria range from specified column data (that's to say names listed before <> sign; I just put ="=Suyama" for illustration purposes to show I wanted to include it after applying filter), but as I said I wanted to apply the filter on thousands list range by hundreds of "not equal" criteria range from specified column data to display only what is not shown in criteria range, is there a way of doing this by ANDs/formula in one cell or is it just need to be hundreds of those columns in the criteria range to accomplish that?

Thanks in advance

 

[Aladin Akyurek]

[TABLE="width: 225"]
<COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4750" width=134><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl64, width: 102, bgcolor: white"]Davolio[/TD]
[TD="class: xl63, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"]Buchanan[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #ffffcc"]CRIT[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 102, bgcolor: #ffffcc"]TRUE[/TD]
[TD="class: xl65, width: 134, bgcolor: white"][/TD]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"] [/TD]
[TD="class: xl64, width: 134, bgcolor: white"] [/TD]
[TD="class: xl64, width: 64, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 102, bgcolor: white"]Type[/TD]
[TD="class: xl65, width: 134, bgcolor: white"]Vendor[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"]beverage[/TD]
[TD="class: xl64, width: 134, bgcolor: white"]Suyama[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]5122 $[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"]Meet[/TD]
[TD="class: xl64, width: 134, bgcolor: white"]Davolio[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]450 $[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"]food[/TD]
[TD="class: xl64, width: 134, bgcolor: white"]Buchanan[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]6328 $[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 102, bgcolor: white"]food[/TD]
[TD="class: xl64, width: 134, bgcolor: white"]Davolio[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]6544 $[/TD]
[/TR]
</TBODY>[/TABLE]

A1:A2 houses a list of vendors to exclude.

A3:A4 constitutes the criteria range, where CRIT in A3 is field which does not occur as a header value in A8:C8. Leaving A3 empty is a good option.

A4 houses the following formula:

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

A8:C12 houses the data, subject to filtering.

You just need to run Advanced Filter on A8:C18 with A3:A4 as the criteria range.