Deleting all cells that appear more than once

[Guest]

I have a list of NY State zip codes I provide service in. I combined that
with a list of all zip codes in NY State. I need to show just the zips I
don't provide service in.
How do I do that quickly.

 

[David M. Marcovitz]

If you put all your zip codes that you service in column A (starting at
row 2) and all the zip codes in NY in column B, then you can put the
following in cell C2 and fill down:

=IF(COUNTIF(A$2:A$100,B2)=0,"X","")

This will put an X in column C next to all the zips you don't service.
Then you can either sort based on column C or autofilter to only choose
those in column C.

--David

--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

 

[David M. Marcovitz]

One more quick note is that in my last response I only had it check from
rows 2 to 100 for your list of zip codes you service. If you have more than
99, just change the 100 to something larger.
--David

--
David M. Marcovitz
Microsoft PowerPoint MVP
Director of Graduate Programs in Educational Technology
Loyola College in Maryland
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

 

[Guest]

Here is an example. Say your codes are in column A from A1 thru A20. Say
the NY codes are in column B from B1 thru B100. In C1 enter:

=COUNTIF($A$1:$A$20,B1)

and copy down to match all the B's. A zero in C means its not in your list.
A one in C means its in your list. Just sort B&C by C. All the zeros will
be at the top.

 

[Guest]

Worked like a charm. Thanks so much. I was able to figure out substituting
2205 zips in NY for your 100 value.

 

[Guest]

Thanks Gary. Yours worked too.

Here is an example. Say your codes are in column A from A1 thru A20. Say
the NY codes are in column B from B1 thru B100. In C1 enter:

=COUNTIF($A$1:$A$20,B1)

and copy down to match all the B's. A zero in C means its not in your list.
A one in C means its in your list. Just sort B&C by C. All the zeros will
be at the top.