Data consolidation is the process of combining values from multiple ranges in the same worksheet, multiple worksheets, or multiple workbooks into one new range. In this article, we will specifically focus on how to consolidate only the last row in multiple Sheets using Google Sheets. But before diving into the details, let’s first understand the concept of data consolidation.
Understanding Data Consolidation
In Google Sheets, we commonly use the Query function for data consolidation. Excel, on the other hand, has a dedicated data consolidation command under the Data menu, which is quite useful for beginners. The key to successful data consolidation lies in the structure of the data. It must be identical across the sheets, meaning the same number of columns, same data types in each column, and shared column names. However, there is no restriction on the number of rows.
To illustrate this, let’s take a look at the following 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)
In the example above, we have consolidated “Sheet1” and “Sheet2” into “Sheet3”. We have summed column 2 (Qty) and column 4 (Amt). However, you’ll notice that we used the Max Query scalar function for column 3 (Rate). This is because we don’t want to sum the rate. By using the Max or Min function, we can return the rate as it is. It’s a cool tip, right?
How to Consolidate the Data in the Last Row in Multiple Sheets
If you have a total row, which is the last row containing the total of a column or columns in multiple identical sheets, then you may want to consolidate them. The only obstacle here is finding the last row in each sheet dynamically. Fortunately, I have the correct formula for that – the Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel. It works equally well in Google Sheets.
Let’s walk through the step-by-step instructions to consolidate only the last row in multiple sheets:
Step 1: Finding the Last Row Number to Offset
In “Sheet1”, use the following formula:
=ArrayFormula(MATCH(2,1/(Sheet1!A:A<>""),1))
In “Sheet2”, use the following formula:
=ArrayFormula(MATCH(2,1/(Sheet2!A:A<>""),1))
Enter these formulas in “Sheet3”, where we will consolidate the last row from “Sheet1” and “Sheet2”. The first formula will return 5, and the second formula will return 6.
Step 2: Extracting the Last Row Using Index by Row Offset
We can use the Index function along with the formulas from Step 1 to offset 5 and 6 rows in each sheet and return the total (last) row. When using Index, you can omit the ArrayFormula function used in Step 1.
Syntax:
INDEX(reference, [row], [column])
In this case, we have already obtained the row offset. Simply put 0 as the column offset to return the entire last row content.
Use the following formulas to extract the last row in each sheet:
Formula 1 for “Sheet1”:
=index(Sheet1!A1:D,MATCH(2,1/(Sheet1!A:A<>""),1),0)
Formula 2 for “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 last rows from both sheets before consolidating. Make sure to include the title row as well.
Combine the formulas and the title row using the following formula:
={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
Finally, use the formula from Step 3 as the data for the Query function and summarize it as follows:
=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.
If you have any doubts about adding more sheets or any other part of this tutorial, please feel free to ask in the comments. Thanks for reading and enjoy consolidating your data!
