merging two large spreadsheets

[Ray]

I have a problem with merging two large spreadsheets with no real key field.
Each spreadsheet contains data information in the form of records. Each row
is a record and each column contains the relevant field information. Each
record looks something like:
1234 56 George McMaster 56 Nowhere St Anywhere AN1 1ZA
--
There would be additional data entered in subsequent columns (fields)
relating to ongoing work.
There would be about 50k to 60k records
The two spreadsheets would contain mainly the same information, only one
would be a more up to date version with a number of deletions and additions.
The newer version would not have the additional data, but I would like to
merge this on to it.

What I would like is to somehow merge the two so that I can have the
information entered on the older database updated with the new. And of course
a flag which would highlight new records and recordes which are on the old
but not on the new.
I could create a key field by concatenating the name and address fields and
using that as a key, but beyond that, I am lost. Any help would be very
welcome.

Ray
Ray Kennedy

 

[Otto Moehrbach]

Ray
How would you identify what row in one sheet needs to be merged with
what row in the other sheet? Are the row numbers the same? If you had to
do this manually, how would you do it? HTH Otto

 

[Pete_UK]

You can use VLOOKUP or an INDEX/MATCH combination to bring data from
one sheet to the other dependent on some unique identifier. You can
use this in both sheets to identify data which is not present in the
other sheet, using something like:

=IF(ISNA(VLOOKUP( ...,0)),"absent",VLOOKUP(...,0))

where VLOOKUP will be using your key field in one sheet to try to find
an exact match in the other sheet.

Of course, you will need to provide more information about where your
key field is in each sheet, and what fields (columns) you need to
bring across etc. if you want a more specific solution to your
problem. If you are using VLOOKUP your key field needs to be in the
left-most column of the table of data.

Hope this helps.

Pete