If your dataset contains timestamp column, you can quite efficiently extract the earliest or latest record in each category in Google Sheets. Before jumping into the solution, let me explain the problem in detail first.

You are going to get two formulas here.

1. A formula to Filter/extract LATEST records in each category.
2. A formula to Filter/extract EARLIEST records in each category.

Let me begin with extracting the latest records first and that also in detail.
I’ll provide you that formula first. Then you can tweak that formula simply to change latest to earliest.

Assume you have a project in hand and you are updating your Spreadsheet (here Google Sheets) with the progress of each activity frequently.

Then you may have possibly a table with multiple records for the same activity. It might look something like below.

Multiple Activities in Each Category – Example

In this, the data is not in any order. You can use data in any order, I mean sorted or unsorted.

The solution, that I am going to share with you, will work seamlessly in both types of datasets. But there must be a timestamp column in your dataset.

Does the solution work in a date column? No doubt, column A (as per the above sample data) can contain date or timestamps.

I am preferring a timestamp column instead of a date column with a purpose. What’s that?

In case of multiple entries of the same category during the same day, the timestamp will come in handy. With that, we can differentiate which is the latest record.

Here is the details of the sample data.

It’s common that there will be multiple categories/tasks under a project. That means your data entry of project progress in a Spreadsheet, here in Google Sheets, may have the following peculiarities.

• Contain multiple categories or tasks.
• The progress of each task in multiple rows and multiple times.

That is what the above screenshot depicts. Also, note that the data is not sorted categories/tasks wise. That is why you are not seeing the tasks/categories as a group.

If you want to assess the progress of each task or categories, you may need to filter or extract the latest record in each category.

Example to Extract the Latest Record in Each Category

As I have mentioned above, if you have a timestamp column, things are easy for you to extract the latest record in each category based on that date/timestamp column.

Here is my expected result that based on my sample data above. In this, I have only the latest record in each category.

To extract or filter the ‘required’ records from a dataset, you can normally use the Filter or Query function in Google Sheets. But don’t jump into any conclusions here.

We can’t use the functions Filter or Query for our purpose here or you can say ‘effectively’ here.

We can follow a different approach using the function SORTN. Instead of filtering, we can think about removing the unwanted rows in the output. That’s what I am going to do in this example with the help of the SORTN function.

How to Filter the Latest Record in Each Category in Google Sheets – Timestamp Column Based

Here is the formula that I have used in cell F1 in my example above (please see the just above screenshot).

={A1:D1;sortn(sort(A2:D11,1,false),9^9,2,2,true)}

I know many of you are new to this. But I have used this type of formulas in some of my earlier tutorials too.

If you have missed that, don’t worry. I have include one such link at the end of this post (point # 1 under related reading).

Formula Explanation:

To learn a combo formula just look at the middle portion of that formula. From there it expands. Here is that middle portion.

=sort(A2:D11,1,false)

This SORT formula sorts the data based on the timestamp column (column A) in descending order. So the record with the latest dates and time (timestamp) will be on the top. We have not yet sorted the categories/tasks (column B).

The above SORT formula acts as the range to sort in the below SORTN. What the SORTN does?

=sortn(sort(A2:D11,1,false),9^9,2,2,true)

The SORTN formula sorts the column 2 in ascending order (it doesn’t matter which order you choose here) and remove the duplicate categories/tasks. For further clarification, please take a look at this picture. Give special attention to the color pattern.

In this pic, I have explained everything. But I know you may have doubt in the case of the last two points that represent the texts in Green and Magenta colors.

1. Tie mode – The number 2 is the tie mode in SORTN to delete duplicate entries in any column. Want to learn more about tie modes in Sheets SORTN? Here you go – SORTN Tie Modes in Google Sheets.
2. Sort column – Other than the tie mode number, you must specify a column to sort in SORTN also. You can set any sort order this time. This column number plays a key role in eliminating the repeating record. It’s column # 2, the category column here.

The above formula returns our required data. I mean it extracts the latest record in each category. But one thing is missing, that is the header row.

That I have combined with the above formula output using the open clause Curly Braces in the final formula.

How to Extract the Earliest Record in Each Category in Google Sheets

I have already mentioned there is only a minor tweak required here. It’s not in the SORTN formula. It’s in the SORT formula that we have used as the range in SORTN.

Since we want the earliest record from the multiple records, that also in each category, sort the timestamp column in ascending order. That means the earliest dates will then come on the top. Earlier it was in descending order.

Here is that tweaked formula to filter the earliest record in each category.

={A1:D1;sortn(sort(A2:D11,1,true),9^9,2,2,true)}

Conclusion:

Using this method (the last formula) will help you to format the data properly for GANTT charts or knowing the latest status (the first formula) of your project quickly.

Related Reading:

1. How to Find the Last Row in Each Group in Google Sheets.
2. The Formula to Group Dates by Quarter in Google Sheets.
3. How to Find the Highest N Values in Each Group in Google Sheets.
4. Extract First n Rows From Each Group in Google Sheets.