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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Max (demechanik_at_yahoo.com)
Date: 03/10/05 

Date: Thu, 10 Mar 2005 17:56:39 +0800

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 *** so I can investigate.
>
> Is this possible at all?  PRAYING it is :)
>
>
Quantcast