Find most recent updated status for category

Heiko E. · 07/27/20

Hi all,

I'm searching for a solution on following idea:

I have a sheet with following four columns:

Created (date) | Category (Dropdown) | Previous Status (RYG balls) | Current Status (RYG balls)

Now I want to auto-populate "Previous Status" with the last "Current Status" of the same "Category" when a new row is entered. One example:

2020-02-15 | A | Green | Yellow

2020-02-15 | B | Green | Green

2020-02-18 | A | Yellow | Red

When editing a new row and selecting Category = A I want to see "Red" in "Previous Status".

Is that possible somehow?

Paul Newcome · 07/27/20

Are new rows added at the top or the bottom of the sheet?

Heiko E. · 07/27/20

At the bottom would be most conveinient.

If it's necessary for the solution to have this on top I would be fine with it as well.

Paul Newcome · 07/27/20

It is actually an easier solution with them being populated at the bottom. Put this in Row 1 and dragfill down.

=INDEX(COLLECT([Current Status]:[Current Status], Created:Created, @cell <= Created@row, Category:Category, @cell = Category@row), 1)

Heiko E. · 07/27/20

Hi Paul,

thank you for the formula.

It works for some entries. For others I'm guessing the time stamp is simply the blocker here?

I also find the behaviour that Prev. Stat. would populate the Curr. Stat. in case no Curr. Stat. was given.

Paul Newcome · 07/27/20

Yes. The issue is the time stamp. You have multiple rows for the same category with the same exact time stamp.

Heiko E. · 07/28/20

Ok, I understand. I will work something out with this solution.

Thank you for your support, Paul!

Paul Newcome · 07/28/20

Happy to help. 👍️

Answers