You don’t need to use QUERY or FILTER to extract the last entry of each item by date in Google Sheets. Instead, you can use a simple combination of SORT and SORTN.

The SORT function is unnecessary if your data is already sorted by the item column and then by the date column. However, for flexibility, we will include it. This formula extracts the latest records from each group based on the date entries.

Where Is This Useful?

Extracting the last entry of each item by date in Google Sheets is useful in various real-life scenarios, such as:

• Tracking the last recorded stock update for each product.
• Finding the latest PO (purchase order) for each customer.
• Extracting the most recent payment or invoice details.
• Tracking the most recent leave request per employee/student.
• Extracting the most recent status change for ongoing projects.

Generic Formula

=SORTN(SORT(range, key_col, TRUE, dt_col, FALSE), 9^9, 2, key_col, TRUE)

Where:

• range – The data range containing the records.
• key_col – The column number determining the grouping (e.g., product name column number, employee name column number).
• dt_col – The column number containing the date entries.

Example 1: Extracting the Last Leave Request for Each Employee

Consider the following employee leave data:

To extract the last leave request record for each employee:

• key_col is column A (Employee Name).
• dt_col is column E (Leave Request Date).

Formula:

=SORTN(SORT(A2:E, 1, TRUE, 5, FALSE), 9^9, 2, 1, TRUE)

Output:

How This Works:

• SORT(A2:E, 1, TRUE, 5, FALSE): Sorts data by employee name (ascending) and then by request date (descending).
• This ensures that each employee’s latest leave request appears first in their group.
• SORTN(..., 9^9, 2, 1, TRUE): Removes duplicate rows based on employee names, keeping only the latest record per employee.

Example 2: Extracting the Last Stock Update per Product

Here is sample data for tracking the last recorded stock update for each product:

To extract the last entry of each Product by date:

=SORTN(SORT(A2:D, 2, TRUE, 4, FALSE), 9^9, 2, 2, TRUE)

Output:

To extract the last updated date by Category:

=SORTN(SORT(A2:D, 1, TRUE, 4, FALSE), 9^9, 2, 1, TRUE)

Output:

To extract the last updated date by Category and then by Product:

=SORTN(SORT(A2:D, 1, TRUE, 2, TRUE, 4, FALSE), 9^9, 2, 1, TRUE, 2, TRUE)

Output:

Conclusion

This tutorial demonstrated how to extract the last entry of each item by date in Google Sheets using SORT and SORTN. These formulas help efficiently retrieve the most recent records in various scenarios.

Related Articles

• Find the Last Row in Each Group in Google Sheets
• Retrieve the Earliest or Latest Entry Per Category in Google Sheets