Results 1 to 6 of 6

Thread: Help merging two excel sheets into one

  1. #1

    Help merging two excel sheets into one

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    This is my first post here on the forum. Thank you in advance for any help.

    long story short... I am looking to merge two .xls databases into one spreadsheet.

    context: I am working with two separate therapeutic pipeline databases that allow me to export as xls files. They have some different column names and a lot of information. It would save my company a lot of time to be able to merge these two databases by matching important column data across sheets and removing the duplicates.

    I would love to ideas on best practices and any helpful discussion.

    Thank you again,

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    You can copy the data from one sheet to the bottom of the data in the main sheet. Then select the whole range and go to Data tab, then click Remove Duplicates. You can select the columns to base the "duplication" criteriia on.

  3. #3
    The issue is that I would like this to be slightly more automated. A further problem being that not all columns are named the same in both sheets.

    I have attached sample files here...

    Thank you
    Attached Files Attached Files

  4. #4
    Have you looked at Power Query? Its an add-in from Microsoft that can prepare your data. With tables this big be sure you use a machine with a lot of memory.

    With this, you can set up a query to combine the two databases, changing or merging columns as needed. Occasionally you reach the limits of the UI provided power, but almost ANYTHING can be done in the M-code window. For this purpose, there are a couple of scripts that have already been published on the internet too.

    In a quick look at your data, there's also some cells that have more than one data entry in them, again power query can digest this.

  5. #5
    I haven't checked out Power Query before, but I am going to look into it.

    Thank you.

  6. #6
    Thanks for the Power Query tip, it looks like it will help me with my situation as well.

Posting Permissions

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