[thelist] Merge two Excel databases?

Carolyn Jewel Carolyn.Jewel at LEGACYNET.COM
Wed Oct 27 13:50:13 CDT 2004

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?

Are you stuck with pretending excel is a database? If so, you have some
headaches coming up.

I'd import the two spreadsheets into Access (as separate tables) add a
primary key (automumber) to both, and then set about the process of
identifying bad data, duplicates and choosing the most correct data
between the two tables. Match the records on the most reliable common
field between the two (hope you have one!) Then you'll have to hand
match the leftovers. Create cross-reference foreign keys based on the PK
you created. You may need to (further) denormalize your base tables to
contain corrected data in new fields (always preserve the original
data!) Then, once you've cleaned up your data you can start normalizing
(obviously into new tables.)

If you have to stick with excel, then write a query from your sparkling
new normalized data and export a single result set back into excel.

Good luck with your project.


More information about the thelist mailing list