Are you struggling to filter the last status change rows in Google Sheets? You’re not alone! Unfortunately, Google Sheets doesn’t offer a standalone function for this task. But don’t worry, I’ve got a solution for you! In this article, I’ll show you a combination formula that can help you filter the last status change rows easily.
The Challenge
Let’s start by understanding the challenge at hand. When you want to filter the last status change rows in Google Sheets, there are no built-in functions that can help you. So, what can you do? Well, you’ll need to use a combination formula to achieve the desired result. My preferred formula for this task is a combination of Filter and Sortn functions. It’s flexible and can be easily adapted to find changes in any column and subsequent rows.
To better understand what we mean by “status change rows,” take a look at these screenshots:
In this example, the status of employees “Rosa” and “Silvia” changed in March. However, there are no changes in the status of “Ben” and “Gary.”
In this example, the formula evaluates the status change in two columns: C and D. Again, there are no changes in the status of “Ben,” except for the country.
Let’s Get Started
Now that we know the challenge, let’s walk through the steps to filter the last status change rows in Google Sheets.
Step 1 – Eliminating Duplicates Based on Name and Status
The first step is to eliminate duplicate rows. We will consider columns B and C to identify duplicates. Here’s the formula we’ll use:
=sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1)The purpose of this step is to bring the latest records to the top by row numbers and then by names.
Step 2 – Eliminating Duplicates Based on Name
Once Step 1 is completed, we’ll use the result as the range within another Sortn formula. This time, we’ll eliminate duplicates based on column 2, which represents names. Here’s the updated formula:
=array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)The purpose of this step is to have one unique record from each group.
Step 3 – Identifying Last Status Change Rows
With Steps 1 and 2 completed, we now have a table that contains one unique row from each group. If there is a status change in a group, we will have that row. Otherwise, we will have one unique row from that group. To get the status change rows, we’ll use the Vlookup function. Here’s the formula:
=ArrayFormula(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))This formula will allocate the output to the corresponding rows using VLOOKUP.
Final Step – Filter Last Status Change Rows in Google Sheets
To filter the last status change rows, use the following formula:
=filter(A2:C,-istext(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))*countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B)>1)Make sure to replace the corresponding formulas from the previous steps in this formula.
Filter Last Status Change Rows – Multiple Columns
To filter the last status change rows in multiple columns, simply make a few adjustments to the previous formula. Here’s what you need to do:
- Change A2:C to A2:D.
- Change B2:C to B2:D.
- Change the sort column from 4 to 5.
- Increase the column constraint from 3 to 4.
=filter(A2:D,-istext(IFNA(vlookup(transpose(query(transpose(A2:D),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:D,row(A2:A)},9^9,2,transpose(query(transpose(B2:D),,9^9)),1),5,0,2,1),9^9,2,2,1),9^9,4)),,9^9)),1,0)))*countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B)>1)And that’s it! You can now filter the last status change rows in Google Sheets with ease. With these formulas, you can include additional status columns as needed.
Conclusion
Filtering the last status change rows in Google Sheets can be a daunting task without the right formula. But with the combination of Filter and Sortn functions, you can easily achieve this goal. Remember to follow the steps outlined in this article, and you’ll be able to filter the last status change rows like a pro!
For more Google Sheets tips and tricks, visit Crawlan.com. Happy filtering!
