How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

Solution 1:

Since you wanted them on one sheet, I think the above isn't what you were looking for.

I use Microsoft Access to merge data. Especially, if different sheets have a similar identifier (part number/contact person/ect).

You create a "table" for each spreadsheet to be merged You create a "query" which pulls the desired columns to one sheet

Solution 2:

Please see the Consolidate Worksheets Wizard add-in for Excel

The add-in has several merge modes, one of them does exactly what you need.

Please see this link for a detailed description of the mode (how to combine sheets with the same name to one)

The add-in is a shareware, but it has a 15-day fully-funtional trial version (download button at the top of the page), so you can merge thousands of your workbooks for free :)

Solution 3:

Here is a solution in python.

import glob
import pandas as pd

path = "<path to files>"
file_identifier = "*.xlsx"

all_data = pd.DataFrame()
for f in glob.glob(path + "/*" + file_identifier):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

Solution 4:

Today I came across this link RDBMerge, Excel Merge Add-in for Excel for Windows, which I think will serve the purpose.That is a free macro based tool.