Data Comparison

SammySpaceman

Board Regular
Joined
Aug 18, 2002
Messages
57
I have two separate spreadsheet files. Each one contains a listing of zipcodes. I would like to compare the two and make sure that they both have the same listings. If one of the files has different zipcodes, I would like to know via a popup message, formatting, or data exporting. Any thoughts?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Could you use the MATCH function? If you sort both files, then in file 1 enter the formula in B1

=MATCH(A1,[File2]Sheet1!$A$1:$A$300,0)

and copy down, it will show the position in File2 that the entry in A1 is and so on. If they are all there, it will return 1,2,3,4 etc. If there is an additional entry in FIle2 somewhere, the results will fall out of sequence. To help find where, enter in C2

=B2-B1-1

and copy down. Result should always be zero.

If there is an entry missing in File2, it will return #N/A.

Any help?

Richard
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi,

in general, you can use formulas of the form:

=IF(ISNUMBER(MATCH(lookup_value,lookup_array,0)),"","Missing")

to flag items on one list that aren't on another. Do this to both lists.

paddy
 

Forum statistics

Threads
1,144,765
Messages
5,726,161
Members
422,658
Latest member
backbeach63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top