Many Excel users have found themselves wondering how could they possibly combine multiple sheets into one without going into too much trouble.
The usual answer would be to copy and paste the sheets, but this is not exactly such an easy task and takes a lot of manual work.
Luckily Excel, as always, has some helpful tips to work around this issue.
Combine Multiple Worksheets with Consolidate
The simplest way to combine multiple sheets is with the Excel Consolidate option. Now, for this feature to be useful, a couple of things have to be in order.
For our example, we will suppose that we have three sheets, all with the statistics for several NBA players in major categories (points, rebounds, assists, and turnovers) for three nights: Monday, Wednesday, and Sunday.
Now, data for each night is in a separate sheet. To merge them all with Consolidate option, several things have to be honored:
- Every data set has to be in a separate worksheet. This means that you do not want to put any data into your output sheet. The best thing is to create a separate sheet where all of the merged data will reside.
- Every sheet has to be similar, i.e. has to have the same layout. Each column needs to have a header, and data in all sheets should be similar
- No blank rows and columns are allowed in the range that we are going to consolidate.
We are going to create a new sheet called “Main File” and then go to Data tab >> Data Tools >> Consolidate:
Once we click on it, a window will appear with several fields to populate:
In the function field, we can choose among 11 functions with which we can merge our sheets, among them Sum, Count, Average, Min, and Max.
In our example, we will use the Sum function.
In the Reference field, we click on the Collapse Dialog icon (a little box at the end of the right) and then select the range of the first sheet that we want to merge. We use this icon since our sheets are located in this workbook. If there were any sheets in the other workbooks, we would use the Browse button to find them.
We then click on the Add button.
We repeat these steps for Wednesday and Sunday sheets. Since all of the headers and first column values are the same and we want to use them in the Main file, we click on the Top row and Left column in Use labels in part of the window.
For the last part, we will click on Create links to the source data box to update our main file whenever any of the data in our sheets changes.
Once we click OK, Excel will automatically calculate (sum in our case) all of the statistical numbers for every player for three nights, i.e. from the three sheets:
Since we click on Create links to source data, Excel created links to our worksheets and the outline that we see above.
We can click on the plus symbol and then on any cell with the value, and we will see the link in the formula bar.
Although Consolidate option is pretty great, it has its limitations. This option only works with numeric values. Furthermore, it can only manipulate data in a certain way, i.e. with 11 formulas on disposal, being Sum, Count, or one of the other ones.
Combine Multiple Worksheets with Vlookup
One of the ways in which we can combine the data from multiple sheets in the Excel VLOOKUP function. This function searches for a certain value in a column so that it could return a value from a different column in the same row.
It has several parameters:
- lookup_value. The value that we are looking for.
- table_array. Here we select the range of cells from which we want to derive data from.
- column_index_num. The sequence number of the column where our results are.
- range_lookup (optional). Matching mode with two values. TRUE stands for approximate and FALSE stands for exact.
To show VLOOKUP in our example, we will create a new sheet, simply call it “Vlookup” and paste the first column, the one with the player’s list. Then, we will create three headers: Points on Monday, Points on Wednesday, and Rebounds on Sunday:
In a cell B2, we will input the following formula:
We want to find the statistics for players, so logically, the values that we want to find are going to be a player’s name. These values are located in the first column.
For the next step, we go to the sheet “Monday”, in which the player’s names are also located in column A. We have to make sure that the first column in our table_array contains the value from our lookup_value .
VLOOKUP only functions from left to right. If, for example, our lookup_value was in the B column in the “Monday” sheet and the data for points was in the A column, we could not use VLOOKUP to retrieve the results. VLOOKUP only functions from left to right. If, for example, our lookup_value is in the B column in the “Monday” sheet and the data for points was in the A column, we could not use VLOOKUP to retrieve the results.
Points for each player in sheet Monday are located in the B column, so we will only have columns A and B for our table_array.
Next, we have to define our column_index_num. Results that we need are located In the B column, and that is the second column in our table_array, so we will define 2 as the column_index_num.
For our range_lookup, we will define 0, which stands for false. This means that our lookup range can be in any order. In this case, Excel returns a value from the first value where the lookup_value is equal to the range_value. If we have selected 1, or True, the lookup_range should be in ascending order from top to bottom. In this case, Excel returns the last row where the range value is less or equal to the lookup_value.
For our final step, we will copy and paste the formula till the end of our range (cell B10).
Our table looks like this:
To retrieve the Wednesday results, all we have to do is replace Monday with Wednesday in our formula, since our data is ordered in the same way in every sheet:
And our table now looks like this:
Now, for Sunday, we decided to find the rebounds for every player. We will replace Wednesday with Sunday in our formula and we will change the table_array and column_index_num. Our formula will be:
Since the data for rebounds is located in column C in the Sunday sheet, we have to define our array to be from column A (where names of the players are found) and C (where rebounds are found). Our data is in the third column from starting point, so our column_index_num is 3.
Finally, our table looks like this:
Combine Multiple Worksheets with VBA
As always, there is a way in which you could do this using VBA code. We will present the code in full here, and explain the code in detail in the text below. To fully understand this code, you need some knowledge about the For…Each loop, IF function, and variables. We will explain them, but not in detail in this example.
Sub CombineWorksheetsIntoOne() Application.ScreenUpdating = False Dim wb As Workbook Dim ws As Worksheet Dim ms As Worksheet Dim rng As Range Dim colCount As Integer Set wb = ActiveWorkbook Set ms = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)) ms.Name = "Main" Set ws = wb.Worksheets(1) colCount = ws.Cells(1, 255).End(xlToLeft).Column With ms.Cells(1, 1).Resize(1, colCount) .Value = ws.Cells(1, 1).Resize(1, colCount).Value .Font.Bold = True End With For Each ws In wb.Worksheets If ws.Index = wb.Worksheets.Count Then Exit For End If Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(65536, 1).End(xlUp).Resize(, colCount)) ms.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next ws ms.Columns.AutoFit Application.ScreenUpdating = True End Sub
In the first part of our code, we define that we want our screen updating to be off. When VBA code executes, it can be seen in the background. We do not want this so we type in:
Application.ScreenUpdating = False
Next, we have to define all the variables that will be used in our code:
- Dim wb As Workbook– Dim is short of dimension. It is used to define our variable, in this case, “wb” for our workbook. It is important that this is an object variable since workbooks are objects.
- Dim ws As Worksheet– We define a variable for our sheets as well since we are going to loop through them.
- Dim ms As Worksheet– We have to create our main sheet where the data from the rest of the sheets are going to be. For this, we create a variable as a placeholder for our sheet.
- Dim rng As Range– When speaking about data, we can observe it as ranges. This is why we created this variable as well.
- Dim colCount As Integer– This variable will count the columns in our tables in all worksheets.
After we defined our variables, we can get to work.
Set wb = ActiveWorkbook
First, we set our wb variable to be equal to the active workbook, so we can start working from there.
Set ms = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)) ms.Name = "Main"
This part of the code sets our variable ms (we said this variable is for our main sheet), and adds it at the end. It clearly states to add worksheets after you count all the worksheets. Next up, we define the name of our sheet, and set it to be “Main”.
Set ws = wb.Worksheets(1) colCount = ws.Cells(1, 255).End(xlToLeft).Column
In the line of code above, we go to the first worksheet in our file to get column headers. Then we count the columns in this sheet with colCount.
With ms.Cells(1, 1).Resize(1, colCount) .Value = ws.Cells(1, 1).Resize(1, colCount).Value .Font.Bold = True End With
Next, we use the VBA With function to retrieve the columns from our first sheet to the main sheet. No copy and paste methods are used and needed in this step. We then bold these columns.
For the final part, we will use the For Each loop to go through all of our sheets. We will explain this loop in more detail.
For Each ws In wb.Worksheets
The first part of the loop defines what object are we going to work on. Since we need to loop through all of our worksheets, we define that for each ws (worksheets) in wb.worksheets (in our workbook) we are going to do some action.
If ws.Index = wb.Worksheets.Count Then Exit For End If
This upper part of the code is an IF function that is nested into our For Each loop. This part checks whether we have looped through all of the sheets. If the sheet that we are looping is equal to our last sheet (Master sheet) loop stops executing.
Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(65536, 1).End(xlUp).Resize(, colCount))
At the start of the execution, we are on our first sheet. The line of code above sets the range to start from the second row (we do this since we all copied the first row in the Main sheet).
ms.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
This line of code makes sure that we put the ranges that we defined above to our Main sheet.
This is the final line of code. It is integral to every For Each loop. It says that we should repeat all of these steps in the loop until the conditions are met (in this case, until we go through all of our sheets but the last one).
We adjust the width of the column in the Main sheet.
Application.ScreenUpdating = True
For the last part, we activate the screen updating again.
We will use our code for the three sheets that we had at the beginning. We will create a new Excel file and present the data about the players and their statistics.
We will save this Excel file as Macro-Enabled Workbook and then insert our code. Now, when we execute our code, we will get another sheet:
In the Main sheet that we got, we have column names as in the first sheet, and the list of all players and their statistics for three previous nights.
This code can get some modifications, for example, to create another column and add the name of the original sheet from which the data originates, or any other aspect. But, this code certainly serves as a great starting point.