How do I compare two columns in Excel?

Question

Our organization has offices in several Zip Codes in all 50 states. Each year, some new offices are opened, some current offices are closed. Almost forgot: we have our own designations for the offices.

Year   Office Code   Zip Code        Status
2009   Z10           20701           Open
2009   Z11           20703           Open
2009   Z12           20701           Open

2010   Z10           20702           Open
2010   Z11           moved to 20702  Open
2010   Z12           NA              Closed
2010   Z14           20707           Open - Added (didn't exist last year)

I have a list of the zip codes in 2009 of our existing offices. This list of zip codes is different in 2010.

Is there a straightforward way to compare the zip code lists and report on which respective offices have been closed/opened/moved?

Answer 1

Say you want to find those in col. B with no match in col. A. Put in C2:

=COUNTIF($A$2:$A$26;B2)

This will give you 1 (or more) if there's a match, 0 otherwise.

You can also sort both columns individually, then select both, Goto Special, select Row Differences. But that will stop working after the first new item, and you will have to insert a cell dans start again.