Advanced Filter Critera using formula not working

[KKPeters]

In my list I am trying to filter out Shippers (to my company) that are
charging
us for shipments to other companies. I have a list of our locations and
want to use either the city or zipcode to filter out the shipments that are
not to our company

I have not had alot of luck using formulas with the Advance Filter Critera

The below formula gives me when they are shipping to my company by city

=ISNA(MATCH('Wrong Vendor'!$H$2,FedEx!$I$1:$I$312,0))

I need the reverse of this. So I get a list of shipments that are NOT to my
company.

Thanks, Krystal Peters

 

[Max]

Try replacing ISNA with ISNUMBER in the expression

 

[KKPeters]

Tried ISNUMBER suggestion; and it did not work. There may be a CITY name (or
ZIPCODE) but it will not be in my list of store. What I need is that if the
City is not in the list that it will show up using the Advance Filter
Criteria. I need to isolate the shipments that are not going to any of my
stores.

Thanks, Krystal Peters

 

[Max]

Tried ISNUMBER suggestion; and it did not work.

It should have, as-in ISNUMBER is the "reverse"
that you mentioned you wanted for your expression which worked:

=ISNA(MATCH('Wrong Vendor'!$H$2,FedEx!$I$1:$I$312,0))
I need the reverse of this ..

so, perhaps it's a problem with your data, or something else

Maybe try Debra's page at:
http://www.contextures.com/xladvfilter02.html


---

 

[KKPeters]

I am afraid I was not clear. I am looking to pull all the Cities that does
not match the cities in the range - so I am still working with text. Thanks,
Krystal

 

[Max]

I am afraid I was not clear. I am looking to pull all the Cities that does
not match the cities in the range - so I am still working with text.

Here's a simpler alternative using Autofilter on a helper col
which can achieve the same objectives

Suppose you have a defined range
MyCities =Sheet2!$A$1:$A$20
which lists all "your" cities

In any other sheet,
Assume you have cities listed in B2 down
Put in C2: =IF(B2="","",ISNUMBER(MATCH(B2,MyCities,0)))
Copy C2 down all the way, then just autofilter col C for FALSE to gather all
the lines with cities that do not match those within MyCities (TRUE would be
those lines that match)

---