How to Find the Last Row in Each Group in Google Sheets

Do you want to extract the last row in each group in Google Sheets? With multiple levels of sorting, you can easily achieve this. In this article, I will show you how to use the SORT and SORTN combination formula to find the last entered row of each group, even in multiple-column grouping scenarios.

Identifying Groups in Google Sheets

To begin, let’s take a look at an example dataset. In column B, we have three groups represented by “Apple,” “Mango,” and “Orange”. Our goal is to extract the last row in each group from this dataset.

For this task, I have a simple SORT+SORTN combo formula that works like magic. It considers the latest entry in each group, even if the records are not in adjoining rows.

The Formula to Find the Last Row in Each Group in Google Sheets

Here is the formula I used to extract the last row from each group in Google Sheets:

=SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)

Let me break down the formula for you and explain how you can modify it to suit your dataset requirements.

First, you may want to group data based on a different column instead of column B. In the formula, B2:B represents the grouping column range. Change this number to match the grouping column in your data range.

Additionally, ensure that the grouping column appears in the formula. Replace B2:B with your grouping column range.

By tweaking the formula according to your dataset, you can efficiently extract the last row entry in each group.

How Does This Formula Work?

Let’s talk about the logic behind extracting the last row entry in each group using this formula.

Step 1

We start with the existing data range B2:D. To ensure that the latest entries are on top of each group, we sort the range based on row numbers in descending order using the SORT function. This places the latest entries at the top of each group.

Syntax: SORT(range, sort_column, is_ascending, [sort_column2, ...], [is_ascending2, ...])

Formula: SORT(B2:D,ROW(B2:B),0)

Don’t worry if the sorted output includes empty rows at the top. It doesn’t affect the formula.

Step 2 (Final Formula)

After sorting the data range, we use the powerful SORTN function to extract the last entry in each group.

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)

Let’s break down the parameters:

• n: COUNTA(UNIQUE(B2:B))
  • This represents the count of unique values in the group column. It tells the formula how many rows we want in the result. You can manually specify the count if you know it in advance.
• display_ties_mode: 2
  • Use this mode to retrieve the first n records in a given range after removing duplicate entries.
• sort_column: 1
  • The formula eliminates duplicates based on the sort column. In our case, it is the group column (column B), so we set it to 1.
• is_ascending: TRUE
  • This parameter determines the sorting order of the group column. It won’t make a significant difference, but you can specify FALSE if needed.

Grouping with Multiple Columns

It’s also possible to group data based on multiple columns. Let’s consider an example where we group based on columns B and D from the previous dataset.

You can 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)<>"") ) )

To adapt this formula to your dataset, make the following changes:

• Specify the group column indexes in the CHOOSECOLS formulas:
  • The first CHOOSECOLS formula determines the first group column (B2:B in our case).
  • The second CHOOSECOLS formula determines the second group column (D2:D in our example).
  • Update the column indexes according to your dataset.

Remember, each LET function assigns a name to a specific part of the formula, making it easier to read and understand.

Start extracting the last row in each group and make your Google Sheets analysis much more powerful and efficient!

For more helpful Google Sheets tips and tricks, don’t forget to visit Crawlan.com. Stay tuned for more juicy secrets from your ultimate Google Sheets guru!