How to merge Excel files with different columns

If you have a number of Excel spreadsheets with differing columns in each, it’s likely that your best option to merge them (for free) is to use a VBA macro.

This article will build on the concepts and VBA code in another article in this series, How to merge Excel files with the same columns. The difference is that here you will be dealing with spreadsheets in different formats and so  will need to map values from columns in each spreadsheet into the correct column in your desired output spreadsheet.

A separate, but related, problem is merging the rows based on a common column in each spreadsheet. This is covered in a separate article, How to combine multiple Excel files into one whilst merging row data.

The first thing to do is to define the structure of the output spreadsheet that you need. This is often a sub-set of all the columns present in the spreadsheets you need to merge.

To explain how the merge works, we’ll use some example spreadsheets as follows:

  • The first spreadsheet has columns first_name, last_name, company_name, city, phone1, email:Merge Excel files differing columns File1
  • The second spreadsheet has columns email, company_name, phone2:
    Merge Excel files differing columns File2
  • The third spreadsheet has columns email, first_name, last_name, date_of_birth:
    Merge Excel files differing columns File3

Here, you’d like to merge the data from each spreadsheet into just one sheet in one spreadsheet with the same set of columns; first_name, last_name, company_name, city, phone1, phone2, email, date_of_birth:Merge Excel files differing columns Output


We can see that the mapping of columns from the source spreadsheets to the output would need to work as follows:

Spreadsheetfirst_namelast_namecompany_namecityphone1phone2emaildate_of_birth
ExcelFile1.xlsxfirst_namelast_namecompany_namecityphone1email
ExcelFile2.xlsxcompany_namephone2email
ExcelFile3.xlsxfirst_namelast_namephone2emaildate_of_birth

Using the example as described, we can start to define the VBA code we need to merge these spreadsheets.

Excel Document StackNot exactly what you need?
Have a look at The Ultimate Guide on how to Merge Excel files for some other ways to merge Excel files.

Setting up the VBA code

To keep things simple for this example, we’ll assume that all the Excel files to merge are located in a single folder. To ensure we can map the columns from the source spreadsheets to the output, we also need to know the names of each of the spreadsheets that will be merged.

If you would like to use a form to choose which Excel files to merge, you can also adapt the VBA code shown in How to merge Excel files with the same columns to include the mapping described in this article.

The example spreadsheet can be downloaded from the Downloads section at the end of this article.

  1. Create a folder for the Excel files to be merged
  2. Copy & paste all the source Excel files into this new folder
  3. Create a new Excel workbook in a location that is NOT the folder created in step 1
  4. In this new workbook, click on the Developer ribbon tab
    Excel Developer Ribbon Tab
    If the Developer ribbon tab is not present, follow these steps to get it
  5. Click the Macros button in the Developer tab (The Macros button on a Mac)
    Excel Macros Button
  6. In the dialog box which opens, type the name MergeExcelFiles and click the ‘Create’ button
    Excel Macros Dialog Box
  7. The Visual Basic Editor will open:
    Excel Visual Basic Editor
  8. Copy & paste (or re-type by hand if you prefer!) the code below between Sub MergeExcelFiles()  and End Sub  (if you’d like to know more about what this code does, see how the code works):
    Below the End Sub of Sub MergeExcelFiles(), paste this code:
     
  9. Look for the line shown below and replace <<Full path to your Excel files folder>> with the full path to the folder you created in step 1 (e.g. C:\Users\Martin\Documents\MyExcelFilesToMerge)
  10. We can now run this macro to merge the Excel files. There are 2 ways to do this:
    1. Click the Run toolbar button in the Visual Basic Editor
      Excel VBA Run Button
    2. In the Developer tab in the Excel ribbon, click Macros, select MergeExcelFiles and click the Run button
      Excel Run Macro
    3. If there are any errors reported, check that the VBA code is correct and try again
  11. If you’d like to save this spreadsheet with the macro you just created in it, you will need to save the file as a Macro-Enabled Workbook (an xldm file). Follow these steps to do this:
    1. Click File -> Save as…
    2. Select ‘Excel Macro-Enabled Workbook (.xlsm)’ from the Format list
      Excel File Format
    3. Click the Save button

Explanation of the column mapping

There are several parts of the code that are involved in the mapping of columns from each spreadsheet into the output. The first section of code to discuss is the that which allows us to map the columns for each spreadsheet. This code is defined in the function Function MapColumns(fileName As String) As Object

In this function, you can see a section for each Excel file we wish to merge, starting with a Case expression, e.g. Case "ExcelFile1.xlsx" . Following the Case expression, the columns are mapped by adding an entry to a collection for each column in the source spreadsheet. The Key  represents the column in the source spreadsheet and the Item  represents the column in the output spreadsheet. For example, taking the last mapped column in source spreadsheet ExcelFile1.xslx:

Here we are mapping column F, email, of the source spreadsheet, ExcelFile1.xlsx, to column G, email, of the output spreadsheet.

You will have different source spreadsheets and need to map different columns. This function, Function MapColumns(fileName As String) As Object , is where you would apply your own mappings for your specific data. To change this code to suit your needs, you should first define the mappings from your source spreadsheets to the output columns you need. A good way to do this is to draw up your own mapping table similar to that shown above.

With the mapping defined, there are a few places in the code where the mapping is used.

Whilst looping through the files to process, the specific column map for the file currently being processed is retrieved using this code:

The code then loops through the columns in the source spreadsheet and uses the mapping to copy all the values from the column in the source spreadsheet into the appropriate column in the output:

These lines select the column and rows in that column to copy:

And these lines select column and rows in that column to paste the values in the output:

Downloads

If you’d like a copy of the spreadsheet which already has this functionality built in, you can download it here.

This has a macro defined, MergeExcelFiles which performs the merge whilst mapping columns to the output.