Merge Two Excel Sheets By Column

1. Merge Two Excel Sheets By Column Without
2. Merging Two Excel Sheets Into One
3. Merge Two Excel Sheets By Column Excel
4. How To Merge Two Excel Spreadsheets

Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that Read more about Combine Multiple or All Sheets from an Excel File. Combine data with the Ampersand symbol (&) Select the cell where you want to put the combined data. Type = and select the first cell you want to combine.

Users of UW-Madison's institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

1. For the sake of the security, I will not use my own worksheet but the sample sheet in the solutions below. The two sample sheets have 5 columns and 10 rows (not including the first row). What we need do is to merge the two worksheets into one. I hope these solutions are also useful for you guys. Solution1: using Microsoft.Office.Interop.Excel.
2. Here with Kutools for Excel, you can apply the Tables Merge tool to quickly merge two tables in two sheets and update the contents. Kutools for Excel, with more than 300.

1. Locate where you want the data to go. Click that cell only once.
   
   
2. At the top, go to the Formulas tab and click Lookup & Reference.
   
   
3. Select vLookup
   
   
4. Excel's vLookup wizard will pop up. We'll walk through each part of the formula.
   
   
   
5. Lookup_value
   Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
   Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
   
   
   
6. Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
   
   
   In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
   Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student 1@wisc.edu is the email in one row and Student abc@wisc.edu in the other, Excel will choose one of the emails for you.
   
7. Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
   Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
   
8. Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:
   
   
   
9. Finally, copy and paste the formula to pull emails for the rest of the column.
   (Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
   

1. Type the beginning of the formula: =VLOOKUP(
   The formula guide will appear below.
   (Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
   
   
   
2. Follow the guide and enter each value. Remember to insert a comma between each value.
   
3. Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
   =VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE)
   
4. Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)

• To lock in the lookup value in cell B1, insert $ signs before the column and the row:
  =VLOOKUP($B$1,'[Spreadsheet2.xlsx]SheetName'!$B$1:$E:$11,3,FALSE)
• To lock in the column only, insert a $ before B only.
• To lock in the row only, insert a $ before 1 only.
  

Need More Information or Help?

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).

Merge Two Excel Sheets By Column Without

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:

Merging Two Excel Sheets Into One

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. Penny dreadful amazon prime.

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. 72 goetia sigils.

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.

Discord reddit. 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.

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.)

Merge Two Excel Sheets By Column Excel

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

How To Merge Two Excel Spreadsheets

Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that Read more about Combine Multiple or All Sheets from an Excel File. Combine data with the Ampersand symbol (&) Select the cell where you want to put the combined data. Type = and select the first cell you want to combine.

Users of UW-Madison's institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

1. For the sake of the security, I will not use my own worksheet but the sample sheet in the solutions below. The two sample sheets have 5 columns and 10 rows (not including the first row). What we need do is to merge the two worksheets into one. I hope these solutions are also useful for you guys. Solution1: using Microsoft.Office.Interop.Excel.
2. Here with Kutools for Excel, you can apply the Tables Merge tool to quickly merge two tables in two sheets and update the contents. Kutools for Excel, with more than 300.

1. Locate where you want the data to go. Click that cell only once.
   
   
2. At the top, go to the Formulas tab and click Lookup & Reference.
   
   
3. Select vLookup
   
   
4. Excel's vLookup wizard will pop up. We'll walk through each part of the formula.
   
   
   
5. Lookup_value
   Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.
   Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.
   
   
   
6. Go to the next field, Table_array (click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.
   
   
   In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.
   Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student 1@wisc.edu is the email in one row and Student abc@wisc.edu in the other, Excel will choose one of the emails for you.
   
7. Go to Col_index_num (click in it once). This identifies which column contains the information you want from Spreadsheet 2.
   Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column.
   
8. Go to Range_lookup (click in it once). Type FALSE to search for exact matches. The result will look something like this:
   
   
   
9. Finally, copy and paste the formula to pull emails for the rest of the column.
   (Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)
   

1. Type the beginning of the formula: =VLOOKUP(
   The formula guide will appear below.
   (Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.)
   
   
   
2. Follow the guide and enter each value. Remember to insert a comma between each value.
   
3. Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:
   =VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE)
   
4. Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)

• To lock in the lookup value in cell B1, insert $ signs before the column and the row:
  =VLOOKUP($B$1,'[Spreadsheet2.xlsx]SheetName'!$B$1:$E:$11,3,FALSE)
• To lock in the column only, insert a $ before B only.
• To lock in the row only, insert a $ before 1 only.
  

Need More Information or Help?

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).

Merge Two Excel Sheets By Column Without

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:

Merging Two Excel Sheets Into One

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. Penny dreadful amazon prime.

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. 72 goetia sigils.

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.

Discord reddit. 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.

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.)

Merge Two Excel Sheets By Column Excel

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

How To Merge Two Excel Spreadsheets

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.