If your dataset contains a timestamp column, you can efficiently extract the earliest or latest record in each category in Google Sheets. This article will provide you with two formulas: one to filter the latest records in each category and another to filter the earliest records. Let’s dive into the details of this problem and the solutions.
The Problem: Multiple Records for the Same Activity
Imagine you have a project and you frequently update your Google Sheets spreadsheet with the progress of each activity. As a result, you may end up with a table containing multiple records for the same activity, like the one shown below:
In this example, the data is not in any specific order. However, the solution I’m about to share with you will work seamlessly regardless of whether the data is sorted or not. The key is to have a timestamp column in your dataset.
Timestamp vs. Date Column
You may wonder why I prefer using a timestamp column instead of a date column. The reason is that a timestamp allows us to differentiate between multiple entries of the same category during the same day. With a timestamp, we can determine which is the latest record.
Extracting the Latest Record in Each Category
To extract the latest record in each category based on the timestamp column, we can’t simply use the Filter or Query function in Google Sheets. Instead, we’ll use a different approach using the SORTN function. Here’s the formula I used in cell F1 in the example above:
={A1:D1;sortn(sort(A2:D11,1,false),9^9,2,2,true)}The above formula sorts the data based on the timestamp column in descending order. Then, using the SORTN function, it sorts column 2 (the category column) in ascending order and removes duplicate categories/tasks. The result is the latest record in each category.
Extracting the Earliest Record in Each Category
To extract the earliest record in each category, we need to make a minor tweak to the formula. Instead of sorting the timestamp column in descending order, we’ll sort it in ascending order. Here’s the modified formula:
={A1:D1;sortn(sort(A2:D11,1,true),9^9,2,2,true)}With this formula, the earliest record in each category will be extracted.
Conclusion
By using these formulas, you can easily extract the earliest or latest record in each category based on the timestamp in Google Sheets. This method is particularly useful for formatting data for GANTT charts or quickly assessing the latest status of your project.
For more related articles and tutorials, visit Crawlan.com.
