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.
我们的组织在所有50个州都设有多个邮政编码办公室。每年都会开设一些新办事处，一些现有办事处将关闭。几乎忘了：我们有自己的办公室名称。
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.
我在2009年的现有办公室有一份邮政编码清单。此邮政编码列表在2010年有所不同。
Is there a straightforward way to compare the zip code lists and report on which respective offices have been closed/opened/moved?
是否有一种直接的方法来比较邮政编码列表和报告关闭/打开/移动的各个办事处？
1 个解决方案
#1
7
Say you want to find those in col. B with no match in col. A. Put in C2:
假设你想在col中找到那些。 B在col中没有匹配。 A.投入C2：
=COUNTIF($A$2:$A$26;B2)
This will give you 1 (or more) if there's a match, 0 otherwise.
如果匹配，这将给你1（或更多），否则为0。
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.
您还可以单独对两个列进行排序，然后选择两者，Goto Special，选择Row Differences。但是这将在第一个新项目之后停止工作，并且您将不得不再次插入一个单元格dans。
#1
7
Say you want to find those in col. B with no match in col. A. Put in C2:
假设你想在col中找到那些。 B在col中没有匹配。 A.投入C2：
=COUNTIF($A$2:$A$26;B2)
This will give you 1 (or more) if there's a match, 0 otherwise.
如果匹配，这将给你1（或更多），否则为0。
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.
您还可以单独对两个列进行排序，然后选择两者，Goto Special，选择Row Differences。但是这将在第一个新项目之后停止工作，并且您将不得不再次插入一个单元格dans。