Comparing contents of two spreadsheets and outputting results to a

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

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 A1:D1, 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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"brx" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

/me buys Max the drink of his choice


"Max" wrote:

> 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 A1:D1, 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 ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "brx" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

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!








"br0x" wrote:

> Thanks Max - pretty much exactly what I needed!
> You have saved me MANY hours of work!
>
> /me buys Max the drink of his choice
>
>
> "Max" wrote:
>
> > 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 A1:D1, 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 ..
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "brx" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
> >

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>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"br0x" <[email protected]> wrote in message
news:[email protected]...
> Thanks Max - pretty much exactly what I needed!
> You have saved me MANY hours of work!
>
> /me buys Max the drink of his choice

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"brx" <[email protected]> wrote in message
news:[email protected]...
> 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!