Automated program to merge two excel sheets

Hi all,

I was directed here from the general excel forum and I am hoping this is a better place to ask my question. I also hope that I can clearly articulate what my question is.

The short version of the question is that I have two excel files that I need to merge into one sheet.

This is more complicated than simply copying and pasting into one sheet. I use two databases to give me information as to what drugs are in development for certain diseases. These databases give me the data as an xls file. The problem is that neither database is, in of itself, comprehensive. I need to be able to use both of them. It is a huge time sink now to compare both databases and manually create a new spreadsheet with the aggregation of the two databases.

Another contributing factor to the complexity is that in one file the name of the drug is called "Drug_Name" while in the other it is called "Drug Name" This isn't too bad, but for the company in one sheet it is called Lead_Company and in the other there is Originator and Licensee, which both contribute to the lead developing company. Also, in the file titled BioMedTracker, the Drug_Name can only be one thing, where additional names are given in a separate column. In the one titled Citeline, every single name by which the therapy has ever been called is included in one cell.

I know this may not be an easy problem. But this could save myself and my company thousands of hours over the years if I can figure out how to make this work. I am looking for any ideas on how to proceed.

I am envisioning a process where I could input the two excel sheets and have returned to me a new sheet where I have defined the relevant columns that I need that has the aggregation of the two sources.

I am looking for any solutions or even ideas of how I might go about finding a solution. I am happy to program in whatever language necessary to get this done.

Thank you in advance for any help or perspective.

Hello,

Solving your first problem - merging two files - I think the best way to do it is to use ADO DB library in VBA code. I don't know if you are familiar with this technique (?). E.g. it provides you with functions which enable you working on an Excel file as a database(workbook) with tables(sheets). You can write SQL query to select proper columns from each file and merge them into one result. (I could give you an example file but not sooner than the weekend )

Regards,
Alex