Find the Last Entry of Each Item from Date in Google Sheets

Finding the last entry of a single item is just child’s play in Google Sheets. Just sort the date column in descending order. The top row will contain the last entry. By saying last entry, I mean the row that contains the latest date.

But what about the last entry of each item or a group of items? In Google Sheets, it’s easy.

Of course, sorting is not a solution. I should say ‘normal’ sorting is not a solution. Do you know why?

Please follow my example. I have hidden the answer below.

Before that, one more thing. Do you know where comes such a requirement? Why does one want to find the last entry of each item based on a date column?

The Purpose of Finding the Last Entry of Each Item

In Accounts, you can follow this method to find the last invoice submitted by each company. The below screenshot is in line with this concept.

If you are in the transport business, in a flash, you can find the halting time of your trucks/busses/rental cars on a specified day.

In HR & ADMIN, you can use it to track the latecomers.

In countless similar scenarios, you can use my formula to find the last entry in each group in Google Sheets.

Steps to Find the Last Entry of Each Item from Date in Google Sheets

Sample Data:

pick the latest entry in each group in google sheets

In this example, I have separated each group with red-colored lines.

The cyan highlighted rows are the latest entry in that group. I mean the latest based on the “Invoice Date” column, the fourth column in the range A2:E.

Please go through the dates in each group, and you can understand this.

From this dataset, I want to extract/retrieve the highlighted rows separately. How?

It’s not much complicated as you think. Here is that killer formula.

The Formula to Extract the Last Entry of Items in Google Sheets

Master Formula

=SORTN(SORT(A2:F,4,0,2,1),9^9,2,2,1)

I’ve used two Google Sheets functions in this master formula: SORT and SORTN.

Here is how they retrieve the last entry of each item based on the date column.

Formula Explanation and Logic

First, I want the last entry of items at the top of the groups. See this example with one fruit item.

25/05/2018 Apple

26/05/2018 Apple

23/05/2018 Apple

In this, the dates are in column 1, and the text strings “Apple” are in column 2.

If I sort column 1, i.e., the date column, in descending order, the above data will be as below.

26/5/18 Apple

25/5/18 Apple

23/5/18 Apple

It is what the SORT function in the above formula does. Here is that SORT formula part only.

=SORT(A2:E,4,0,2,1)

In this formula part, the number 4 is the column index number of the date column in our dataset. The number 0 indicates sorting this column in descending order.

In addition, I’ve sorted column 2 in ascending order as we have multiple text strings (customers in column 2). It helps group them.

As I’ve told you, we only want to retain one item from the group. I mean the top row in each group. The SORTN formula does this part wonderfully. How?

Please scroll back and see my formula that finds the last entry of each item.

Now see this SORTN syntax.

SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])

In this syntax of the SORTN function, “range” is the just above-provided SORT formula.

The “n” argument represents the number of rows in the SORTN result.

Since we are unsure about the number of rows after removing each repeated item, I’ve opted to put 9^9, which is an arbitrarily large number.

The display_ties_mode is 2. It removes repeated items. It works based on the “sort_column.”

The “sort_column” must be the column index number of the group column, i.e., 2.

Conclusion

You can find the last entry of each item in Google Sheets in many ways. Among them, the use of the SORT and SORTN combination is a tricky one.

It seems Google Sheets users are yet to widely use this combo.

Related:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

3 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.