+ Reply to Thread
Results 1 to 6 of 6

Comparing contents of two spreadsheets and outputting results to a

  1. #1
    brx
    Guest

    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



  2. #2
    Max
    Guest

    Re: Comparing contents of two spreadsheets and outputting results to a

    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" <brx@discussions.microsoft.com> wrote in message
    news:1FE51D77-0301-432A-AA52-1A3F35CAC1A6@microsoft.com...
    > 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
    >
    >




  3. #3
    br0x
    Guest

    Re: Comparing contents of two spreadsheets and outputting results

    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" <brx@discussions.microsoft.com> wrote in message
    > news:1FE51D77-0301-432A-AA52-1A3F35CAC1A6@microsoft.com...
    > > 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
    > >
    > >

    >
    >
    >


  4. #4
    brx
    Guest

    Re: Comparing contents of two spreadsheets and outputting results

    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" <brx@discussions.microsoft.com> wrote in message
    > > news:1FE51D77-0301-432A-AA52-1A3F35CAC1A6@microsoft.com...
    > > > 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
    > > >
    > > >

    > >
    > >
    > >


  5. #5
    Max
    Guest

    Re: Comparing contents of two spreadsheets and outputting results

    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" <br0x@discussions.microsoft.com> wrote in message
    news:B3EFFFD3-0382-4385-B342-EA8CE8040C26@microsoft.com...
    > Thanks Max - pretty much exactly what I needed!
    > You have saved me MANY hours of work!
    >
    > /me buys Max the drink of his choice




  6. #6
    Max
    Guest

    Re: Comparing contents of two spreadsheets and outputting results

    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" <brx@discussions.microsoft.com> wrote in message
    news:DBD0D2C5-EB28-4FC5-A811-45391E3FB749@microsoft.com...
    > 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!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1