Data Comparison

[SammySpaceman]

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?

 

[RichardS]

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]

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