[thelist] Merge two Excel databases?

John.Brooking at sappi.com John.Brooking at sappi.com
Wed Oct 27 15:51:09 CDT 2004

Theodore Serbinski said:
> Hey all! So I've just been given two Excel databases, that
> are holding similar data (names, addresses) but don't have
> all the same fields (one has like work number, other
> doesn't, etc). The data for both is capped at ~1600 records,
> because that is how many people there are in the database,
> but both just have different information on each of these
> and one is missing some names from the other.
> My question therefore, is there an easy way to compare both
> of these Excel databases and merge the data as necessary and
> add fields from one to the other?

For only 1600 records, I'd just read them both into the same spreadsheet
side by side, identify a common field (ideally an ID# or something else that
you know will be an exact match, which a name or address field are not
guaranteed to be, unless they came from the same source), and sort each set
of columns by that field. (Careful not to sort whole rows, just the columns
of each file.) Now you can see fairly easily which ones match and which ones
don't, according to the common field. (You can make this easier for yourself
by putting the common field from each section side by side, or creating a
formula to compare them on each row.) Where there's no match, insert blank
cells into one or the other set of columns until the row after it matches
again. So you'll end up with something like the following (view in a
non-proportional font):

   ID1   Name1       Addr1          ID2   Phone    Fax
   ----- ----------- -------------  ----- -------- ---------
   345   John Smith  2 Pine St.     345   888-1212 888-6548
   346   Jane Doe    10 Main St.
                                    347   800-4565 845-5456
   348   Joe Schmoe  3 Penny Ln.    348   886-8795 800-4568
Do a bit of cleanup, and there's your merged file!

If your data is not this cooperative and this approach proves to be too much
work, you could read both into a database and use SQL to join them, and/or
do some other tricks to try to identify common ones. I started a message
going down that road, but decided it might be overkill for your situation.
If you'd like to hear more about it, let me know, perhaps offlist, along
with more information about the tools you have available.

- John

This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like. 

More information about the thelist mailing list