5

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?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Imaginativeone
  • 107
  • 1
  • 2
  • 8

1 Answers1

7

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Upvoted, but things will get a bit weird if you have multiple offices in one zip code. You really need to compare the office code list from both years to be fully accurate I would suspect. – Jody Feb 19 '11 at 15:10
  • @Jody: sure, you have to do it both ways to find out all the diffrences. – iDevlop Feb 19 '11 at 17:27
  • Would it be pretty easy in VBA? – Imaginativeone Feb 19 '11 at 17:47
  • @Remou: Thanks for the edit; looks good. How did you fix my post? – Imaginativeone Feb 19 '11 at 17:51
  • 1
    @Doug: I think it's quicker without VBA in this case. – iDevlop Feb 19 '11 at 18:00
  • @iDevlop: Thanks for the answer. What do I do to acknowledge your help? – Imaginativeone Feb 19 '11 at 19:21
  • @Doug You can use the code button {} on the toolbar. VBA would be very easy, and you can use ADO with Excel, which gives you all the power of queries. – Fionnuala Feb 19 '11 at 20:45
  • 1
    @Remou: with joins, etc ??? have you got a "favorite link" for ADO queries on XL ? – iDevlop Feb 20 '11 at 08:22
  • 2
    @iDevelop ADO and VBA, you understand, yes with joins and so forth. [How To Use ADO with Excel Data from Visual Basic or VBA](http://support.microsoft.com/kb/257819) [Compare / Match rows](http://stackoverflow.com/questions/4379213/excel-macro-match-and-lineup-rows/4381166#4381166)[Various notes](http://stackoverflow.com/questions/2643860/please-suggest-ways-to-manipulate-excel-spreadsheet-without-using-excel-object-as) – Fionnuala Feb 20 '11 at 09:59
  • [With MySQL](http://stackoverflow.com/questions/4933544/import-excel-data-to-relational-tables-at-mysql/4934841#4934841) or [With SQL Server](http://stackoverflow.com/questions/4564532/mvc-load-data-from-excel-to-sqlserver/4564779#4564779) – Fionnuala Feb 20 '11 at 10:03
  • Still a mess s/b: [How To Use ADO with Excel Data from Visual Basic or VBA](http://support.microsoft.com/kb/246335) also [Compare / Match rows](http://stackoverflow.com/questions/4379213/excel-macro-match-and-lineup-rows/4381166#4381166) and [Various notes](http://stackoverflow.com/questions/2643860/please-suggest-ways-to-manipulate-excel-spreadsheet-without-using-excel-object-as) – Fionnuala Feb 20 '11 at 10:08