Auto Numbering based on Impact and Date

Diana36

This is a follow up to my previously posted question.

https://community.smartsheet.com/discussion/137520/auto-numbering-question

I am trying to number just the gap items, but all the high impact should come first in date order, and then medium impact in date order continuing. I was able to get it to start over the count when the impact changes, but I would like it to continue and update if a new high impact item is added at a later date.

Green Column is what it should be

Test #

=IF(OR(ISBLANK([Date Added]@row), Category@row <> "Gap Item"), "", COUNTIFS([Impact Value]:[Impact Value], [Impact Value]@row, [Date Sequence]:[Date Sequence], <=[Date Sequence]@row))

Impact #

=IF(ISBLANK([Date Added]@row), "", IF(Category@row <> "Gap Item", "", COUNTIFS(Category:Category, Category@row, [Impact Sequence]:[Impact Sequence], <=[Impact Sequence]@row)))

Date # (original formula)

=IF(OR(ISBLANK([Date Added]@row), Category@row <> "Gap Item"), "", COUNTIFS(Category:Category, Category@row, [Date Sequence]:[Date Sequence], <=[Date Sequence]@row))

https://app.smartsheet.com/b/publish?EQBCT=04ee9edac8db4f6f97cd426f376a0d4a&_gl=11032wsj_gcl_auMjAzOTQ4NzE1MC4xNzM3OTE0OTA4_gaODcxOTAzMDEyLjE3MDY3MTg4MTc._ga_ZYH7XNXMZK*MTc0NDk4NzkxOS42NDQuMS4xNzQ0OTg4MTM1LjEzLjAuMA..

JYokoi

Hello @Diana36,

I was able to get it to be ordered based on the Impact and then date.

First for the dates, I created a new column to calculate the age.
=TODAY()-[Date Added]@row

Then I created 3 new rows to rank the Impacts based on the Age of the row. To make them go in order (High, Medium, Low), I just added them to the Max of the previous Impact Rank.
High
=IFERROR(RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "High"), 0), "")

Medium
=IFERROR(MAX(High:High) + RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "Medium"), 0), "")

Low
=IFERROR(MAX(Medium:Medium) + RANKEQ(Days@row, COLLECT(Days:Days, Category:Category, "Gap Item", Impact:Impact, "Low"), 0), "")

Then I just joined them all together to get the combined order numbering.
=JOIN(High@row:Low@row)

It will adjust the order if you add item later based on the Date and Impact.

Let me know if this works for you!