With multiple levels of sorting, you can find the last row in each group in Google Sheets. I am using SORT and SORTN as a combination formula to extract the last entered row of each group. It works in multiple-column grouping also.
We can identify the group from a column of identical values. Please see the below example dataset and pay attention to column B.
In this sample data, Apple, Mango, and Orange in column B represent three groups.
The last row in each group of this data is row # 5 for Apple, row # 9 for Mango, and row # 14 for Orange. I only want to extract these rows from the data set.
I have a simple SORT+SORTN combo formula to find the last row in each group in Google Sheets.
In row # 15, if you have a new entry for Apple, the formula will consider this entry as the last entry, not row # 5.
That means the records in the group may not be required in adjoining rows. They can be in distant rows.
The Formula to Find the Last Row in Each Group in Google Sheets
I’ve used the following SORT and SORTN combination in cell F2 to extract the last row from each group of items in Google Sheets.
=SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)Before explaining how to code a formula like this, let me explain to you how to modify this formula to meet your dataset requirements.
You may want to group data based on any other column, not column B.
In my formula, B2:B contains the fruit names. It is the first column in the range, and I want to group this column range.
The # 1 in the formula indicates column B. You must change this number based on the grouping column number in your data range.
Also, the grouping column range B2:B appears in the formula. Replace it with your grouping column range.
Accordingly, tweak my Google Sheets formula to return the last row entry in each group in your dataset.
Can You Explain to Me How This Formula Extracts the Last Row in Each Group?
Why not?
Here is the logic behind the formula that finds the last row entry in each group in Google Sheets.
Step 1
I have the existing data in the range B2:D (a total of three columns).
We should sort this in range based on row numbers. And the sort order in the SORT function should be descending. That will place the latest entries on the top of each group.
I assume the latest entries are at the bottom of the sheet, not at the beginning.
Syntax: SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])
Formula:
SORT(B2:D,ROW(B2:B),0)The sort output may contain a bunch of blank rows at the top. That won’t make any issue.
Step 2 (Final Formula)
After QUERY, the SORTN is one of my favorite functions in Google Sheets.
Using this function, we can form a killer combination formula in Google Sheets for removing duplicates in data ranges.
The below SORTN formula returns the last entry in each row in Google Sheets.
Syntax: SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])
Formula:
SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)Where;
n: COUNTA(UNIQUE(B2:B))
As per my sample data, we have three groups. So we can specify the number 3 instead of the above COUNTA. But it’s not a dynamic way.
The above COUNTA returns the count of unique values in the group column. That’s equal to the number of rows we want in the result.
display_ties_mode: 2
Use this tie mode to retrieve the first n records in a given range after removing duplicate records.
We have applied SORTN in a data range sorted in descending order based on row numbers.
So the output of SORTN will be the last entry row in each group.
sort_column: 1
The formula removes duplicates based on sort_column. So it must be the group column index. It’s 1 in our case.
is_ascending: TRUE
It won’t make much difference as it’s for sorting the group column. You can specify FALSE also.
What About Multiple Columns in Grouping?
That’s also possible. Let’s group based on columns B and D in the above sample data.
You can then use the following formula.
=LET(
srtd,
SORT(B2:D,ROW(B2:B),0),
LET(
result,
SORTN(srtd,9^9,2,CHOOSECOLS(srtd,1)&CHOOSECOLS(srtd,3),1),
FILTER(result,CHOOSECOLS(result,1)<>"")
)
)
This time, I will explain what changes you should make to adapt the above formula, which returns the last entry in each group in a multi-column group.
The sorting part, i.e., SORT(B2:D,ROW(B2:B),0), is the same as earlier (step 1).
I am grouping data based on columns 1 (B2:B) and 3 (D2:D). Here is how I have specified them in the formula.
CHOOSECOLS(srtd,1) – determines the first group column.
CHOOSECOLS(srtd,3) – determines the second group column
You may want to modify 1 and 3 in these formulas as per the group column indexes in your data.
Tips:
The purpose of LET is to assign a name to the sorting (step 1 formula), i.e., srtd, and CHOOSECOLS is to select specific columns from the sorted result.
Related:
Thank you
Hi Prashanth
I have a question here.
How do I extract the last row of “Apple” by the latest timestamp in an unsorted table? Let’s say;
24-Apr 2018 09:00:21 Apple
22-Apr 2018 16:08:43 Apple
24-Apr 2018 09:00:21 Orange
24-Apr 2018 15:00:09 Apple
22-Apr 2018 08:00:21 Apple
Answer : 24-Apr 2018 15:00:09 Apple
Hi, Desmond Lee,
Assume the above table range is A1:B, where column A contains timestamps and column B contains the fruit names.
So the formula you may use is –
=index(sort(filter(A:B,B:B="Apple"),1,0,2,1),1,0)The formula may convert the timestamp to a datevalue. So format it back to timestamp (Format > Number > Date time).
To get all fruits (latest).
=sortn(sort(A:B,1,0,2,1),9^9,2,2,1)Dear Prasanth,
Thank you for your great blogs, this helps a lot!
Would you maybe be able to assist me with the following query?
We use google sheets daily in our hotel to manage our minibar in all the hotel rooms. Each room has 3 products (actually more, but this is easier for the example below). Basically, we use google forms whenever we replace an item in a room, to keep track of the expiry date of all items in all rooms.
The sheet is structured as follows:
A B C D
Timestamp Room Item Expiry date of new item
04/07/2019 10:01:02 104 Product 1 15/03/2019
Each time a product is replaced in one of the rooms, a new line will be added to the Google Sheet. This means that the old entries will remain in the sheet (and actually become obsolete, because we replaced the product). We then use filter views to get the data we want.
However, for 1 particular thing, we do not manage the correct view/formula. What we like to do, is get a daily list of items which are expiring in the coming 10 days (so that we can replace them before they actually expire). I have already created a filtered view on the D column, however, the problem is that we always need to have the LATEST entry only for a certain product in a certain room, because the old rows will remain in the file over time.
So basically, I need to have a list first of each room with 3 unique products with the most recent time stamp for each (column A). It would look like this:
A B C D
Timestamp Room Item Expiry date of new item
4/07/2019 10:01 101 Product 1 25/03/2019
8/07/2019 11:31 101 Product 2 1/07/2019
29/06/2019 12:18 101 Product 3 25/03/2019
5/07/2019 11:24 102 Product 1 1/07/2019
9/07/2019 12:24 102 Product 2 29/01/2019
4/07/2019 10:01 102 Product 3 1/07/2019
8/07/2019 11:31 103 Product 1 25/03/2019
29/06/2019 12:18 103 Product 2 1/07/2019
5/07/2019 11:24 103 Product 3 29/01/2019
I could then apply the filter view mentioned before, on this list to get the list of items I want.
Hoping this makes sense? You would really be of great help if we can solve this.
Many thanks in advance.
Kind regards,
Max
Hi, Max,
Assume the above data is in A1:D in “Sheet1” (A1:D1 contain the headers).
If you are ready to manually sort this data (the timestamp column) in descending order, then you can use the below formula in the same sheet (in cell F2). The formula will return the ‘recent’ unique three products in each room.
=sort(sortn(A2:D,9^9,2,B2:B&C2:C,0),2,1,3,1)If you are not ready to sort the data, then in “Sheet2”, sort the data using the below formula in cell A2.
=sort(Sheet1!A2:D,1,0)Then in cell F2 in “Sheet2” use the first formula.
Best,
Prasanth, Thank you for the wonderful tutorial.
Your formula is giving the full total last row, but I only want to add a column next to G, i.e in column H I would like to get the last value that is in column G. How should I get this?
Can you please advise?
Thank you.
Rekha
Hi, Rekha,
First, modify the earlier formula as below.
=sortn(sort({if(len(A2:A),row(A2:A),""),A2:G},1,false),9^9,2,4,false)Here are the changes made:
1. Removed the ArrayConstrain (earlier used to remove the virtual column that contains the row numbers).
2. Removed the ArrayFormula. When using SORT, ArrayFormula is not required. In earlier formula also, it’s not necessary to use.
3. Moved the row number virtual column from the last to the first.
Now in the next step, we can use this formula as the range in a Vlookup. So the final formula that you can use in cell H2 is;
FINAL FORMULA (cell H2)
=ArrayFormula(IFERROR(vlookup(row(A2:A),sortn(sort({if(len(A2:A),row(A2:A),""),A2:G},1,false),9^9,2,4,false),8,0)))
The index number 8 indicates column G (see the last part of the formula). If you want to extract the values from any other column, just change that number accordingly.
Best,