Help merging two excel sheets into one

creedjm

New member
Joined
Jan 20, 2015
Messages
3
Reaction score
0
Points
0
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,
Creed
 
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.
 
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
 

Attachments

  • AdvDrugSearchResults2037820151209323.xls
    26 KB · Views: 10
  • Drug Export.xlsx
    153.3 KB · Views: 9
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.
 
I haven't checked out Power Query before, but I am going to look into it.

Thank you.
 
Thanks for the Power Query tip, it looks like it will help me with my situation as well.
 
Back
Top