Combine Tables by using Data Consolidate in Excel

To combine similar Data Tables from different worksheets within the same workbook or different worksheets from different workbooks (but same table header), you can take help of Consolidate option from Data menu in Excel. This option allows you to combine all similar data into one single table where you apply the Consolidate command. It will be more clear if I tell you an example through others function.
 
Assume that, You have Sheet1, Sheet2 and Sheet3. Sheet1 has "District vs Month" wise rainfall measurement record in Bangladesh in 2015. Similarly Sheet2 has the same data table with same header but 2016 years. Now in Sheet3 you need to AVERAGE the "District vs Month" wise rainfall measurement record. So, what will you do?
 
Generally You will go to the Sheet3 and Click the cell where you want to get the AVERAGE of a District and type the formula:
 

=SUM(Sheet1!B3+Sheet2!B3)/2

 
Yes this is correct. But problem is, if you use this formula then, your District name must stay in the same sequence in Sheet1 and in Sheet2. Otherwise you will get wrong average.
 

No, if you think that, SUMIFS function can do it, then you are wrong. This function not allowed here due to it becomes a 3D range. So, if District name is not in the same sequence or may be absent, then how can you combine these 2 data table in a single table in Sheet3? The answer is Consolidate option.


What is Consolidate?

Consolidate is an option in Excel under Data menu, that allows you to combine 2 or more similar data table into a single data table from different or same worksheet in the same workbook or different workbooks.
 

 
Here in below, I would like to show an example about how you can do it through Consolidate option in 2 different ways:

• Different worksheets within the same workbook
• Different worksheets within different workbooks

(1) Different worksheets within the same workbook:

Assume that, you have a workbook TotalScore.xlsx with 3 sheets ("2015", "2016" and "Report" are 3 sheets). In sheet 2015 you have below data table:

Image 1: Data Table in Sheet "2015"

In sheet 2016 below is the data table:

Image 2: Data Table in Sheet "2016"

In Sheet "Report" we will combine these 2 data tables into 1 data table from B4 cells in "Report" sheet.

Image 3: View of "Report" sheet

Now before going to first step you can use a Name Range for "2015" and "2016" data tables for time saving. But you can also do it without Name Range. Lets start doing this without using Name Range which as described in below:

Step 1:
Click on B4 cell of "Report" sheet to place the cursor there.
 
Step 2:
Click on Data | Consolidate to open the dialog box.

Image 4: Consolidate dialog box

Step 3:
Select Sum from Function drop down list. Click on Collapse button (red arrow) under Reference option and Select the cell range D5:K14 from sheet "2015".

 

Please note that, you need to select from Header Row and Header Column

So, the range is '2015'!$D$5:$K$14 and then press Enter or Click the Collapse button (down red arrow) again to back in the Consolidate dialog box.

Step 4:
Now Click on Add button to add the range.
 

Image 5: After adding the "2015" data in Consolidate dialog box.

Step 5:
In the same way Click the Collapse (red arrow) button and Select the 2nd data table from sheet "2016". The data range is '2016'!$B$3:$I$18 for the 2016 sheet. And then Click the Collapse button (down red arrow) again to back in the Consolidate dialog box. Click on Add button. Now this will add the data table stayed in sheet2 like below:
 

Image 6: After adding the 2nd range in Consolidate window

Step 6:
Mark the Top row and Left column options to get the Row and Column Header in the final report. And Click on Ok. This will show the result like below in Report sheet:

Image 7: Combine report of 2 different tables within the same workbook

Please note that, in Report sheet you will find all the Player's SUM of sheet "2015" and sheet "2016" ODI runs. Example: G Smith (SAF) got Total Score 246 in Result sheet. Because in "2015" this player scored 218 in Jan and 28 in "2016". So, 218 + 28 = 246 in Jan.

This example would be clear enough to you if you use another option before Clicking the Ok button in Consolidate dialog box. Mark the create links to source data option. And then Click on Ok in Consolidate dialog box. Now the report will display like below image with Collapse (+) button. This will allow you to view the source data of every single result.

Image 8: Source data included

Now if you wish to audit why G Smith (SAF) got 246 score in Jan, then just Click on the Collapse button (+) left side to the 40 row. And it will show a breakdown like below image:

Image 9: Breakdown of G Smith (SAF)

If you look the formula bar, then you will see that, it automatically used a SUM function and automatically taken the range. You can use another 10 functions instead of SUM function if you wish. To do it, you need to Select the options you want from Functions option under Consolidate dialog box:

Image 10: Others function available in Consolidate dialog box

 
 

(2) Different worksheets within different workbook:

Assume that, you have 3 different workbooks. 2015.xlsx, 2016.xlsx and FinalScore.xlsx. As previously discussed, 2015 data table is in 2015.xlsx workbook with sheet name same 2015. Same for 2016.xlsx and FinalScore.xlsx. Now what is the process if you wish to combine these 2 files into 1 FinalScore.xlsx?
 

Image 11: 3 different workbooks in Report folder

We will use Browse button to Select the workbook and then use the Collapse (red arrow) button to select the range in Consolidate dialog box.

But the problem is, while you Select the first workbook 2015.xlsx by clicking on Browse button and then you are going to select the range from "2015" sheet from that file, then it will not allow you to Double Click to open that 2015.xlsx file. As a result you will not be able to select the range from "2015" sheet in 2015.xlsx workbook. 

To make it simple, before Clicking on Browse button in Consolidate dialog box, just open all 3 workbooks in Taskbar like below image:

Image 12: 3 different workbooks opened in Taskbar

Now you need to follow below steps to combine these "2015" and "2016" data table into FinalScore file.

Step 1:
Click on B4 cell in "Report" sheet of FinalScore.xlsx file.

Step 2:
Click on Data | Consolidate in the FinalScore.xlsx file. While Consolidate dialog box appeared, Click on Browse button to and Select 2015.xlsx file. This will select the file path of that 2015.xlsx file. Do not Click on Add button now, because you have not selected the cell range from 2015.xlsx files. To do it, Click on Collapse button (Red arrow) and Click on 2015.xlsx file and then Click on "2015" sheet and then Select the range D5:K14 like below image:
 

Image 13:After selected the cell range by clicking on Collapse button (red arrow)

Step 3:
Now Click again that, Collapse button (down red arrow) to go back into the Consolidate dialog box and then Click on Add button to add the range like below image:

Image 14: Range selected correctly

If you failed to select the range correctly then a dialog box will appear "Consolidate reference is not valid" after Clicking on Add button in Consolidate dialog box which is as below:

Image 15: Consolidate reference is not valid

After selecting the ranges correctly, the Consolidate dialog box will looks like below image:

Image 16: Ranges selected correctly (Also marked "Left column" option under "Use labels in")

Step 4:
Now Click on Ok. And you are done like below image:
 

Image 17: Total Result

Please note that, I have selected "create links to source data" option here, to make you ensure that, Excel can use this option in different situations. But sometimes your Excel might be stopped working while you choose this option specially for "different workbooks". It depends on the location of your files.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw