22-May-2009 NEW UTILITY: Merge 2 Data Sets

Nick's picture

This ulility can save you an enormous amount of time if you have a task involving merging 2 data sets.

If you work in Data or Operations, this could be the file for you !

  • You list the 2 sources of the data
  • This can be Excel or text files.
  • You then list where the data is on those data sources
  • The utility will then open both, merge the data, and save an output file.

Here's the main control panel:



So in our example, we merge this:


With this:


To produce this:


Note: An important thing to enter is "Calculate Last Row From Column"

  • This column should be the one that contains the most data.
  • If it isn't, your data might not be aligned in the output file.

Note Also:

  • The utility works with the columns in any order
  • It assumes that Data Source 1 contains the headings you want to use in your output file.
  • There is no error checking, so this utility will break if you do something unexpected.
  • Works with Excel 2007... I'll produce a 2003 version is someone wants to test it.
  • If you're really clever, you will have already worked out that you can merge any number of data sets together by continually using the output file as one of the source files.

To replicate:

  1. SAVE all 3 files to your C drive, and click the "Merge" button"
    • If you don't save the files in your own drive, you will get an error when the utility tries to save the output file.
  2. Click on the link to the output file

Training Video on how to Merge 2 Data Sets in Excel:

vba-tips-merge-the-2-data-sets.xls56.5 KB
workbook1.xls25.5 KB
workbook2.xls29.5 KB