Combining Data Sets (MS Excel 2003)

Topic

New Reply

I have multiple employee listings (MS Excel Exports) from various databases. I would like to combine them and remove duplicates and set up the new database (the combined data) to refresh and add only new records when a new export is imported.

What is the most optimal way of doing this? Should I use MS Access instead.

I was thinking of importing the separate worksheets into one workbook. Then on a new worksheet in that workbook, I envision setting up some kind of query formula that will populate the worksheet with only unique entries.

Could someone recommend a general idea of attacking this problem?

Reply | Quote

Replies

I would say this would be much easier in Access where you can use linked tables, UNION queries and the DISTINCT keyword.
HTH

Reply | Quote

All listings should have the same structure, i.e. the same number of fields, and fields in the same order.

In Excel, you could place all exports below each other in one worksheet (with field names/column headers in the first row only, not repeated further down). You can then use Data | Filter | Advanced Filter to copy unique records to another location.

In Access, you could import one of the listings, set a unique key on the field or combination of fields that uniquely define the records, and import the other listings into it. Because of the unique key, Access won’t import duplicates, so you’ll end up with a combined list of unique entries.

Reply | Quote