Comparing contents of two spreadsheets and outputting results to a

[brx]

Hi!

I have the following - a large contact database is XLS format with address
details.
I also have a second XLS of Australian post (zip) codes and their
corresponding towns.
I want to check the postcodes in the contact database against the postcode
list. If the town and postcode do not match - i want the entire contact
entry (row) to be output to a third sheet so I can investigate.

Is this possible at all? PRAYING it is

 

[Max]

Perhaps something along these lines ..

Assuming

... second XLS of Australian post (zip) codes

is in Sheet1, zip codes in col A from row2 down

Zip Town
1111 Data1
1112 Data2
1113 Data3
1114 Data4
1115 Data5
etc

and > ... large contact database is XLS format
is in Sheet2, cols A to D from row2 down,
with zip codes in col A

Zip Field1 Field2 Field3
1110 Data1 Data1 Data1
1113 Data2 Data2 Data2
1114 Data3 Data3 Data3
1116 Data4 Data4 Data4
etc

Using an empty col to the right, say col F

Put in F2:

=IF(A2="","",IF(ISNA(MATCH(A2,Sheet1!A:A,0)),ROW(),""))

Copy F2 down to cover the data in cols A to D,
say down to F10000 ?

In Sheet3
------------
Copy > paste over the same headers from Sheet2
into A11, viz.: Zip Field1 Field2 Field3

Put in A2:

=IF(ISERROR(SMALL(Sheet2!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet2!A:A,MATCH(SMA
LL(Sheet2!$F:$F,ROWS($A$1:A1)),Sheet2!$F:$F,0)))

Copy A2 across to D2, fill down to D10000
(cover the same range as in Sheet2)

Sheet3 will return the desired results

For the sample data in Sheets 1 and 2 above, you'll get:

Zip Field1 Field2 Field3
1110 Data1 Data1 Data1
1116 Data4 Data4 Data4
(rest are blank: "")

Only rows with zip 1110 and 1116 from Sheet2
will be returned since these do not match
with the zips in Sheet1

Freeze the results in Sheet3 with
a copy > paste special > values elsewhere as needed

Adapt to suit ..

 

[br0x]

Thanks Max - pretty much exactly what I needed!
You have saved me MANY hours of work!

/me buys Max the drink of his choice

 

[brx]

Actually - its almost there but I think one thing might have been missed :

sheet1 (offical list of correct postcodes and their corresponding town)
Zip Town

sheet2 (my contact database that i am trying to correct)
Zip Town

I want entries from sheet2 checked against sheet1.
I know there are a number of entries in sheet2 where the postcode and town
do not match (user error or laziness). This causes us trouble when we do
mass mailouts.
I want each postcode and town (sheet2) checked against sheet1 (which are all
correct).
e.g.
Zip Town
3000 Melbourne <- this is correct - no action
9999 Melbourne <- this is incorrect, indicate on sheet3
3000 Sometown <- this is incorrect, indicate on sheet3

Thanks for your help!

 

[Max]

Glad to hear you got it working there !
Thanks for the feedback

/me buys Max the drink of his choice

Thanks! vintage this somewhere in the cellar for me <g>

 

[Max]

Think just a change in the formula in Sheet2's col F will do it

Instead of

Put in F2:
=IF(A2="","",IF(ISNA(MATCH(A2,Sheet1!A:A,0)),ROW(),""))

Put in the formula bar for F2:

=IF(A2="","",IF(ISNA(MATCH(A2&B2,Sheet1!$A$2:$A$5000&Sheet1!$B$2:$B$5000,0))
,ROW(),""))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy F2 down to cover the data in cols A to D
say down to F10000 (as before)

Adapt the ranges for Sheet1 in the formula to suit, i.e.:
.... Sheet1!$A$2:$A$5000
.... Sheet1!$B$2:$B$5000

Note that both ranges must be identical

For calc efficiency, use a number just large enough to cover the range in
Sheet1 (I used 5000 above, if 1000 is sufficient to cover, then change it to
1000)