Data consolidation means combining values from multiple ranges in the same worksheet, multiple worksheets or multiple workbooks into one new range. Even though this post is about how to consolidate only the last row in multiple Sheets, it’s better to have a clear picture of the data consolidation first.
We normally use Query for consolidating data in Google Sheets. In Excel, there is a data consolidation command under the Data menu which is quite useful for newbies.
One thing is important in data consolidation is the structure of data. The data must be identical in the sheets. I mean the same number of columns, same data types in each column and must share common column names. But there is no restriction in the number of rows.
I have an elaborated tutorial on Google Sheets Data Consolidation. Still, here I am going to give you an overview of data consolidation. After that, we can go to how to consolidate the last row in multiple Sheets in Google Sheets.
Formula, Sample Data in Two Sheets and the Output:
=query({Sheet1!A1:D4;Sheet2!A2:D4},"Select Col1, sum(Col2),max(Col3),sum(Col4) group by Col1",1)
See how I have consolidated “sheet1” and “sheet2” in “sheet3”. I have summed column 2 (Qty) and column 4 (Amt). But as it’s visible I have used the Max Query scalar function.
This is because I don’t want to sum the rate. Since there is no way to return the rate without summing, the only way is to use the Max or Min to return the rate as it is. A cool tip, right?
How to Consolidate the Data in the Last Row in Multiple Sheets
If you have a total row, the last row, containing the total of a column or columns in multiple identical Sheets, then you may want to consolidate them.
Here the only obstacle in front of you will be finding the last row in each sheet dynamically. I have the correct formula for that – Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel. It works equally well in Docs Sheets.
I am going to use that to consolidate only the last row in multiple Sheets in Docs Sheets.
To consolidate only the last row in multiple Sheets, please follow the below step by step instructions.
Step 1:
Finding the Last Row Number to Offset
Sheet 1 Formula:
=ArrayFormula(MATCH(2,1/(Sheet1!A:A<>""),1))Sheet 2 Formula:
=ArrayFormula(MATCH(2,1/(Sheet2!A:A<>""),1))Enter the above formulas in “sheet3” in which I am going to consolidate the last row in “sheet1” and “sheet2”. The first formula would return 5 and the second would return 6.
Step 2:
Extracting the Last Row Using Index by Row Offset
We can use the Index function with the above formulas to offset 5 and 6 rows in each sheet and return the total (last) row. When using Index, you can omit the function ArrayFormula which I have used with the above two formulas.
Syntax:
INDEX(reference, [row], [column])In this, we have already got the ‘row’ offset. Simply put 0 as ‘column’ offset to return the entire last row content.
Formula 1 to extract the last row in “sheet1”.
=index(Sheet1!A1:D,MATCH(2,1/(Sheet1!A:A<>""),1),0)Formula 2 to extract the last row in “sheet2”.
=index(Sheet2!A1:D,MATCH(2,1/(Sheet2!A:A<>""),1),0)Step 3:
Formula to Combine Last Rows of Multiple Sheets
Now you can combine the above two last rows prior to consolidating. I have included the title row too in the combining.
={Sheet1!A1:D1;index(Sheet1!A1:D,MATCH(2,1/(Sheet1!A:A<>""),1),0);index(Sheet2!A1:D,MATCH(2,1/(Sheet2!A:A<>""),1),0)}
Step 4:
Google Sheets Formula to Consolidate Only the Last Row of Multiple Sheets
Just use the above formula in Query as data and summarize as below.
=query({Sheet1!A1:D1;index(Sheet1!A1:D,MATCH(2,1/(Sheet1!A:A<>""),1),0);index(Sheet2!A1:D,MATCH(2,1/(Sheet2!A:A<>""),1),0)},"Select Col1,Sum(Col2),max(Col3),Sum(Col4) group by Col1",1)You can include more than two sheets in this type of last row consolidation.
Any doubt adding more sheets or any other part of this tutorial, please do ask in the comments. Thanks for the stay. Enjoy!
