When you want to filter the last status change rows, there is no standalone function that you can depend on in Google Sheets. Then?
We must use a combination formula, and my preference is a Filter and Sortn-based formula.
I have a flexible formula, and we can easily convert it to find changes in any column in a row and subsequent rows.
Please see the two screenshots below to understand what I meant by status change rows.
Status Change in One Column (example # 1):
In this, the status of the employees “Rosa” and “Silvia” changed in March.
Regarding “Ben” and “Gary”, there are no changes in their status.
Status Changes in Multiple Columns (example # 2):
Here the formula evaluates the status change in two columns, i.e., in columns C and D.
The result is the same as above except for the employee Ben.
Please see the last column for the country.
Important:
First and foremost, please ensure that you have sorted the data by Name (column B) and then by Date (column A) in Asc orders.
It’s applicable to single as well as multiple column status change examples.
Filter Last Status Change Rows – Single Column
Let me walk you through the steps first, so that you can understand the formula later.
We will start with example # 1, which involves one column. Then it’ll be easy for us to include multiple columns.
Either enter the sample data in A1:C17 as per screenshot # 1 or scroll down to the bottom of this tutorial and copy it from my sample sheet.
Once done, you may concentrate on the below steps to understand filtering the last status change rows in Google Sheets.
Combining Ranges (One Key Point)
As I have mentioned, Sortn will be the main formula other than Filter.
Within Sortn, we may require to combine column ranges.
Usually, to combine the column range B2:B and C2:C, we would use B2:B&C2:C (within ArrayFormula).
The ampersand in between will do the trick.
An alternative method to combine these ranges is a TRANSPOSE and QUERY combination method and that is transpose(query(transpose(B2:C),,9^9)).
We will follow this method in our formula, not the ampersand one mainly because of its flexibility and performance enhancement.
Related:- The Flexible Array Formula to Join Columns in Google Sheets.
Here are the steps to filter the last status change rows in Google Sheets.
Steps to Filter Last Status Change Rows
Step 1 – Eliminating Duplicates Based on Name and Status
In the first step, we will eliminate the duplicate rows.
We will consider columns B and C for identifying duplicates.
Please see the highlighted part, i.e., combining column ranges, in the formula and the subsequent formula explanations.
=sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1)
Let me start with the SORTN.
Syntax: SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])
range – {A2:C,row(A2:A)} – added row numbers with the range (as the last column).
n – 9^9 (an arbitrarily large number)
display_ties_mode – 2 (to eliminate duplicates)
sort_column – transpose(query(transpose(B2:C),,9^9)) – combine column ranges B2:B and C2:C.
There are four columns in the output. Please see the image above.
The outer SORT sorts the output in the order 4,0,2,1. That means column 4 descending and column 2 ascending.
The purpose of this sort is to bring the latest records to the top by row numbers and then by names.
We have completed the first step to filter the last status change rows in Google Sheets.
Step 2 – Eliminating Duplicates Based on Name
Please see screenshot # 3 above for the Sortn result.
We will use that as the range within another Sortn.
=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)This time the formula eliminates duplicates based on column 2, i.e., Names.
So we will have one unique record from each group.
The purpose of Array_Constrain is to remove the last column, i.e., the row numbers.
Step 3 – Identifying Last Status Change Rows
With steps 1 and 2, we have a table now that contains one unique row from each group.
Note:- There are four unique names in the source range B2:B, which means there are four groups.
If there is a status change in a group, we will have that row. If not, we will have one unique row from that group.
We just want the status change rows. How to get it?
First, allocate the above output to the corresponding rows using VLOOKUP.
Syntax: VLOOKUP(search_key, range, index, [is_sorted])
Generic Formula: Vlookup(combined_column_range_A2:C,combined_column_range_of_step_2_outupt,1,0)
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)))The IFNA supports the Vlookup to remove N/A (not available) errors.
Please refer to column Q.
With the help of ISTEXT, let’s now convert allocated values to 1 and all blanks to 0 (zero).
Please refer to column R in the image above. I have the following formula in R2.
=ArrayFormula(--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))))In S2, I’ve inserted the following running count array formula to return the running sequence of names in B2:B.
=ArrayFormula(countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B))This will help us to find the last status change rows. You can find the details below.
Final Step – Filter Last Status Change Rows in Google Sheets
First, use the below FILTER formula.
=filter(A2:C,R2:R*S2:S>1)Replace R2:R and S2:S with the corresponding formulas above.
Note:- While doing so, I’ve omitted the Array_Formula functions as it’s not required within Filter.
=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)This way, we can filter the last status change rows. I’ve used this formula in cell E2 (please scroll up and refer to screenshot # 1).
Filter Last Status Change Rows – Multiple Columns
Please refer to the sample data in screenshot # 2.
In the first example, the data is in A1:C17. Here it is in A1:D17.
We want to find the status change in columns C and D.
You require minimal changes in the above E2 formula to get this. What are they?
- Change A2:C to A2:D.
- B2:C becomes B2:D.
- Sort column 4 becomes 5 (bolded and highlighted in pale pink in the below formula).
- Constrain 4 columns instead of 3 (bolded and highlighted in light green in the below formula).
=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)It’s the G2 formula as per screenshot # 2 above.
Now I guess you may know what changes you require to make to include additional status columns. If not, please feel free to ask in the comments.
Finally, we can solve the above two problems using different logic!
You may please find those solutions in cells ‘test 1’!I2 and ‘test 2’!L2 in my sample sheet below.
I’ll explain those formulas in a later tutorial Here.
That’s all. Thanks for the stay. Enjoy!
