Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Data Sets (MS Excel 2003)

    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Combining Data Sets (MS Excel 2003)

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

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data Sets (MS Excel 2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •