Excel Merge Tables From Different Sheets

  1. Excel Merge Tables From Different Sheets Download
  2. Excel 2010 Merge Tables From Different Sheets

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. The easiest method to merge Excel spreadsheets is to simply take the entire sheet and copy it from one workbook to another. To do this, start off by opening both Excel workbooks. Then, switch to the workbook that you want to copy several sheets from. Consolidating data is a useful way to combine data from different sources into one report. For example, if you have a PivotTable of expense figures for each of your regional offices, you can use a data consolidation to roll up these figures into a corporate expense report. 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. Combining data from many sheets into a single sheet? It’s something you’ll do all the time, and you can check out tutorials on how to accomplish this task when all the sheets have the same column order as well as when you need to skip certain sheets but combine others.

How do I fill-in a new sheet with data from sheets 2, 3, 4 … when those sheets have different columns (or are in different order)?

Combining data with different columns? Easier than kitten mittens

Combining data from many sheets into a single sheet? Love it.

It’s something you’ll do all the time, and you can check out tutorials on how to accomplish this task when all the sheets have the same column order as well as when you need to skip certain sheets but combine others.

But what about when you have different columns on each sheet? Or when the columns share similarities, but are in different order?

It’s a pain in the ass, but by using a Scripting.Dictionary to track column names (as Keys) and numbers (as Items) you can ensure that your data lines up appropriately for an easy pivot table.

Let’s check out an example, featuring my favorite sales teams of all time: Dennis, Mac, Frank, Charlie, Sweet Dee, and Artemis from It’s Always Sunny in Philadelphia. You’ll notice that the sheets have some columns in order, some shared columns, and some NON-shared (i.e. totally different) columns:

Each sheet has similarities and differences in columns!

Cool!

Before we go any further, you will need to make sure you have the Microsoft Scripting Runtime added to this project (if you have not already).

This is how to add the Microsoft Scripting Runtime Reference

This 13-second gif walks you through the steps, but in case it is not working here is a quick step-by-step guide:

  1. Open the VBA Editor window
  2. Click “Tools” from the File menu
  3. Select “References” from within the Tools menu
  4. Scroll down until you find “Microsoft Scripting Runtime”
  5. Check the box next to the “Microsoft Scripting Runtime”
  6. Click OK

Phew! Now we can get back to the task at hand… combining data!

Here’s the scoop y’all — our It’s Always Sunny sales data can be combined with this macro:


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 the 4-step VBA process as our guide:

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

Step 1 – Setup is a cinch, and we knock it all out on lines 14-18. We:

  1. (line 15) Make sure the Scripting.Dictionary is set to vbTextCompare, which means the Keys will be case-INsensitive
  2. (line 16) Assign lngFinalHeadersCounter to 1, since we do not have any column headers… yet
  3. (line 17) Assign lngFinalHeadersSize to the .Count of dicFinalHeaders, because we will need to know when new columns are added (and will use this variable for comparisons)
  4. (line 18) Create a new Worksheet and set it to wksDst — this will be our Destination Worksheet, where all of the data will be combined

Smooth! With our set up out of the way, we’ll accomplish Step 2 – Exploration and Step 3 – Execution in two phases:

  1. Phase 1: assemble the final headers Scripting.Dictionary and prepare the Destination Worksheet
  2. Phase 2: copy each column from each Worksheet to the appropriate place on our Destination Worksheet

Let’s dive into Phase 1!

The Step 2 – Exploration of Phase 1 takes place between lines 26-40.

First, we start looping through all of the Worksheets in ThisWorkbook on line 26, ignoring the Destination Worksheet (wksDst) on line 29.

Once we are sure we are NOT on the Destination Worksheet, we identify the last-occupied column on line 35 using LastOccupiedColNum from the VBA Toolbelt. You’re using the VBA Toolbelt, right? Please download it, use it as your new project template, and save yourself TONS of repetitive coding…

But let’s move on, as our Step 2 – Exploration for Phase 1 is done!

Line 36 kicks off a For…Next loop through this Worksheet’s occupied-columns, which is where our Step 3 – Execution takes place for Phase 1. Inside this loop, we will repeat the next 4 steps for each column header:

  1. (line 40) Assign strColHeader to be the leading-and-trailing-spaces-removed column header name
  2. (line 41) Check dicFinalHeaders to see if it already contains this column name (i.e. strColHeader)
  3. (lines 42-43) If that column name is NOT in the Scripting.Dictionary from step #2 above, add it as the Key, with lngFinalHeadersCounter, representing the target column number, added as the Item
  4. (line 44) Increment the lngFinalHeadersCounter variable so the next new column header name points to the next column number

Since we are inside the For Each wksSrc In ThisWorkbook.Worksheets loop, those steps are repeated for each Worksheet as well!

The last bit of Step 3 – Execution for Phase 1 happens on lines 58-60, which is where we set up the Destination Worksheet with the header column names we just collected.

Line 58 starts by kicking off a For Each loop to iterate through each Key in dicFinalHeaders.

Finally, on line 59, we write each header column name to its appropriate column number on wksDst, our Destination Worksheet — a cinch, since dicFinalheaders(varColHeader) gives us the column number.

Boom! That wraps up Phase 1 and sets us up for an easy Phase 2 — take a moment to celebrate and enjoy this gif of Charlie shooting a gun.

Get excited like Charlie y’all, we’re almost done!

The Step 2 – Exploration in Phase 2 takes place between lines 71-85.

Much like Phase 1, we use a For Each loop on line 71 to iterate through each Worksheet, and on line 74 we make sure that the final Destination Worksheet is skipped.

Excel merge tables from multiple sheets

So far, so good!

On lines 80 through 85, we assign three variables to make our copy / paste (which is the next step in Phase 2, Execution) work smoothly:

  1. (line 80) lngLastSrcRowNum is the last-occupied row on the Source Worksheet, which is where we will copy data FROM
  2. (line 81) lngLastSrcColNum is the last-occupied column on the Source Worksheet, which determines the bounds of our (eventual) loop through all of the data columns
  3. (line 85) lngLastDstRowNum is the last-occupied row on the Destination Worksheet, which is where we will paste data TO

That wraps Step 2 – Exploration for Phase 2, which means it’s time to jump into Step 3 – Execution!

Line 90 kicks off a For loop through each of the columns on our Source Worksheet. (Remember, we repeat this for each Worksheet that is not the final Destination Worksheet, just like in Phase 1.)

Line 91 assigns strColHeader, the name of this particular column header. (We will use this name in the next step, to get the right destination column number from dicFinalHeaders.)

Lines 95-96 set rngDst, the cell target on our final Destination Worksheet, using two things:

  1. lngLastDstRowNum + 1, since we want to send our data one row below the last-occupied row on the Destination Worksheet
  2. **dicFinalHeaders(strColHeader), which as you know will return the appropriate column number

Easy peasy!

Lines 97-98 set rngSrc, the column of data from our Source Worksheet. Since we know the column number (lngIdx, as we’re looping through the columns) as well as the last-occupied row on the Source Worksheet (lngLastSrcRowNum), we can create this Range using these cells.

And finally, the copy / paste happens on line 104, where we call the Copy method on rngSrc with a Destination parameter of rngDst.

And with that, you’re done! Time to celebrate y’all, as you have solved a seriously challenging problem in a VERY flexible way.

The last little bit of this script is our Step 4 – Cleanup, which takes place on line 115. All we’re doing here is throwing a MsgBox to the user, letting him or her know that the data has been combined. Wahoo!

Want to see this code in action? Here’s a 12-minute guide to the script, most of which is spent illustrating exactly how each column of data gets lined up appropriately on the Destination Worksheet:

Are you combining multiple Sheets with out-of-order (or completely different) columns into a single Sheet like a pro? 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.

How to merge two tables by matching a column in Excel?

Supposing you have two tables in two different sheets, one is main table, and the other is new data table. Now you want to merge these two tables by a matching column and update the data as below screenshot shown, how can you quickly solve it in Excel? In this article, I will introduce the tricks on combining two tables by a column quickly.

Merge two tables by a column with VLOOKUP
Merge two tables by a column with Tables Merge function
Merge multiple tables by column headers into one sheet with Kutools for Excel

Merge two tables by a column with VLOOKUP

To merge two tables by a column matching, you can apply VLOOUP function.

1. Select a blank cell next to the main table you, C2 for instance. See screenshot:

2. Enter this formula =IFERROR(VLOOKUP($A2,Sheet2!$A$1:$C$6,COLUMN(A1),FALSE),') into it, then drag the autofill handle right until blank cell appears, and then drag the fill handle down to the cells needed this formula. See screenshot:

3. You can add the new column headers to the merged table. See screenshot:

Merge two tables by a column with Tables Merge function

The LOOKUP function will be little troublesome for you, but fortunately, there is a helpful function Tables Merge in Kutools for Excel which can quickly add and update new items in the main table based on another table.

with more than 300 handy functions, makes your jobs more easier.

1. Select the main table you want to use to update and merge with another, click Kutools Plus > Tables Merge.

2. Then in the Tables Merge wizard dialog, select the lookup table in Select the lookup table textbox, check the options if the tables have headers.

3. Click Next, in step 2 of the wizard, check the key column you want to merge based on.

4. Click Next, in step 3 of wizard, check the column(s) you want to update the data.

5. Click Next to next step, check the new column(s) you want to add to the main data.

6. Click Next, in the last step, you need to set some options for the new items, such as highlighted the updated ones with background color.

7. Click Finish. The main table has been updated and added with new items.

Merge multiple tables by column headers into one sheet with Kutools for Excel

If you want to merge multiple tables across sheets or workbooks by column headers into a single sheet as below screenshot shown, you can apply the powerful Combine function of Kutools for Excel to quickly solve it.

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Enable Excel and click Kutools Plus > Combine, and in the popping dialog, check Combine multiple worksheets from workbooks into one worksheet. See screenshot:

2. Then click Next to go to Step 2 of 3 dialog, and add the workbooks by clicking Add button, then select the sheet names you want to merge from Worksheet list section. See screenshot:

3. Click Next to go to Step 3 of 3 dialog. In the last step of the dialog, specify the title row number into it, here is one, also you can specify other setting options as you need. See screenshot:

4. Click Finish, a dialog pops out for choosing a folder to place the merged new workbook. And click Save to go to first one Kutools for Excel dialog which remind you whether open the combined workbook, click Yes to open it or no, and then the second Kutools for Excel dialog pops out for reminding you to save this combine scenario, click any options as you need.

Now the sheets have been merged into one single sheet based on column headers.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Excel Merge Tables From Different Sheets Download

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Excel 2010 Merge Tables From Different Sheets

or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    HERBAL DR OKUOFOH WHO PREPARE HERBAL MEDICINE TO CURE ALL KINDS OF DISEASES INCLUDING HERPES DISEASE.
    I have been battling this Herpes disease for almost A year now....I tried all possible means to get cure from my Herpes Disease but all to be in vain until i saw a post in a health forum about a herbal Dr. Okuofoh who prepare herbal medicine to cure all kind of diseases including Herpes Disease, at first i doubted if it was real but decided to give it a try...when i contact Dr Okuofoh via his email ([email protected] ) write him and reply me explain how the process work so after ordering for the medicine I got it within 3/4 working days through DHL Delivery and I took it according to the way Dr Okuofoh instructed, I was so happy after 2 week I took the medicine there was very big change in my health when I was done with the process I go for test, I found out I am negative...Herpes patients should also get in touch with this herbalist Dr. Okuofoh to get rid of these Herpes Virus forever his whatsapp number +2349050141449.
  • To post as a guest, your comment is unpublished.
    there is no option for table merge in kutools plus as i can see , ihave downloaded it just now yet there isnt any option like that, Lame explanation
    • To post as a guest, your comment is unpublished.
      Hello, satwik. The Merge Table function is released in KTE 17.00 and later version, you can free download one of them to have a try.
  • To post as a guest, your comment is unpublished.
    these directions are terrible. I am an engineer, and I have no DAMN clue how to use this tool. You leep and skip steps and explanations. Awful ability to train.
    • To post as a guest, your comment is unpublished.
      Sorry the tutorial is too complex for you to understand. For more detail on Tables Merge, you can visit this site: https://www.extendoffice.com/product/kutools-for-excel/excel-merge-tables-by-column.html
      Hope it can help you.