Excel Merge Data From Multiple Sheets Based On Key Column

Excel Merge Data From Multiple Sheets Based On Key Column
  1. Excel Merge Columns Into One Column
  2. Merge Data From Two Excel Spreadsheets

Traditionally, VLookup has been one of the most important tools for merging data in Excel, but the process requires multiple steps and can easily tire analysts who must merge multiple columns across many datasets. Instead, let’s take a look at how we can do this same process all within the Excel.

The Best Possible Way for Combining Excel Files by Merging data into ONE Workbook - POWER QUERY Power Query is the best way to merge or combine data from multiple Excel files in a single file. You need to store all the files in a single folder and then use that folder to load data from those files into the power query editor. With Power Query, working with data dispersed across worksheets or even workbooks has become easier. One of the things where Power Query can save you a lot of time is when you have to merge tables with different sizes and columns based on a matching column. Below is a video where I show exactly how to merge tables in Excel using Power Query. To summarize and report results from separate worksheets, you can consolidate data from each sheet into a master worksheet. The sheets can be in the same workbook as the master worksheet, or in other workbooks. When you consolidate data, you assemble data so that you can more easily update and aggregate as necessary. On your Excel ribbon, go to the Ablebits tab Merge group, and click the Combine Sheets button: Select all the worksheets you want to merge into one. If you'd like to combine just one table, not all data, hover over the sheet's name, and then click the Collapse dialog icon on the right to select a range.

Excel Merge Data From Multiple Sheets Based On Key Column

Excel Merge Columns Into One Column

How do I fill-in sheet 1 with data from sheets 2, 3, 4 … ?

Like Samuel L. Jackson in Jurassic Park, this one’s a classic. You and your team are sharing a single Workbook, with each of you operating on your own Sheet. Once everybody is done, you need to combine the data from each Sheet into a single, continuous Sheet for import into a different program. (Or a final pivot table. Or a report to your manager. Or a what-the-flip-ever …)

Let’s dissect a real example. Suppose you have a Workbook like this, with data Sheets that have the same headers (but different info on each). You want to combine this data onto the “Import” sheet, which has the exact same headers too.

All Sheets have the same headers but varying rows

Cool. Here’s the code that gets the job done:


Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Let’s review the code using our 4-step VBA process as a guide:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

Excel Merge Data From Multiple Sheets Based On Key Column

Step 1 – Setup takes place from lines 11-13, where we make three assignments:

Data
  • wksDst, the “Import” Worksheet
  • lngDstLastRow, the last-occupied row on the “Import” Worksheet
  • lngLastCol, the last-occupied column on the “Import” Worksheet

We know that all the data Worksheets have the same shape, so lngLastCol is going to be the same value for the duration of the script.

(Wondering about the functions LastOccupiedRowNum and LastOccupiedColNum? They’re in the VBA Toolbelt and are also defined on lines 45-87… You will use these functions constantly in Excel, so get used to defining them in your macros!)

Merge Data From Two Excel Spreadsheets

The final setup step occurs on line 16, where we assign the initial Destination — this is where our first paste will start.

Next up is Step 2 – Exploration, which occurs inside the loop from lines 19-25. The For Each wksSrc in ThisWorkbook.Worksheets loop iterates through all Worksheets in this Workbook. (LOVE this syntax… so easy to read and understand!)

Line 22 ensures that the “Import” Worksheet is skipped (since that Worksheet is the destination, NOT a source). This phase ends on line 25, where the last occupied row on the source Worksheet is identified — again, leveraging LastOccupiedRowNum from the VBA Toolbelt.

We’re onto Step 3 – Execution! Short and sweet here, from line 28 to 31.

Excel Merge Data From Multiple Sheets Based On Key Column

The source Range is assigned on line 29, taking advantage of the last row, lngSrcLastRow, which we figured out on line 25 above, and lngLastCol, which we identified wayyy back up on line 13. Finally, we use the Range.Copy method on line 30 to move the data to the destination Range — smooth!

Lines 34-35 are a quick switch back to Step 2 – Exploration, this time taking place on the destination (“Import”) Worksheet. Since new data has been added (in the Execution step above), we recalculate the last-occupied row, reset the destination range and continue the loop on to the next Worksheet.

That’s it, no Step 4 – Cleanup necessary!

Here’s a 4-minute guide to the code above, with an emphasis on the Exploration and Execution steps:

Combining multiple Sheets into one Sheet with VBA like a boss? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.

Get the VBA Toolbelt!

Quit digging through old projects and forums like a chump! Download the VBA Toolbelt and start with the most common Excel tasks already done for you.