Rank duplicates oldest to newest

Chris G
Chris G ✭✭✭
edited 05/09/24 in Formulas and Functions

Hi Team,

I have searched previous posts but can't get a formula that works.

I have multiple sheets copying to a master sheet. I need a formula that ranks the row IDs from newest to oldest. This formula then marks a checkbox, which triggers an automation that moves duplicate rows.

Checkbox formula and automation is sorted.

I need a robust ranking formula that works on row ID to rank newest to oldest based on timestamp/date.

To note, there could be multiple duplicates per day.

See the below picture. Rank column has been manually entered.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Chris G

    The RankEQ or RankAVG functions require numeric values thus a helper column is required to convert the timestamp into values that are relative to one another. The helper column formula below creates a value in the format of YYYYMMDD.HHmm .

    =VALUE(YEAR(Created@row) + "" + MONTH(Created@row) + DAY(Created@row) + "." + IF(FIND(":", Created@row) - 1 - FIND(CHAR(32), Created@row) = 1, 0 + MID(Created@row, FIND(CHAR(32), Created@row) + 1, 1), MID(Created@row, FIND(CHAR(32), Created@row) + 1, 2)) + MID(Created@row, FIND(":", Created@row) + 1, 2))

    The RANKEQ function should rank the Row Id sets as you desired.

    =RANKEQ([Helper Date Value]@row, COLLECT([Helper Date Value]:[Helper Date Value], [Row Id]:[Row Id], [Row Id]@row), 0)

    Will this work for you?
    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Chris G

    The RankEQ or RankAVG functions require numeric values thus a helper column is required to convert the timestamp into values that are relative to one another. The helper column formula below creates a value in the format of YYYYMMDD.HHmm .

    =VALUE(YEAR(Created@row) + "" + MONTH(Created@row) + DAY(Created@row) + "." + IF(FIND(":", Created@row) - 1 - FIND(CHAR(32), Created@row) = 1, 0 + MID(Created@row, FIND(CHAR(32), Created@row) + 1, 1), MID(Created@row, FIND(CHAR(32), Created@row) + 1, 2)) + MID(Created@row, FIND(":", Created@row) + 1, 2))

    The RANKEQ function should rank the Row Id sets as you desired.

    =RANKEQ([Helper Date Value]@row, COLLECT([Helper Date Value]:[Helper Date Value], [Row Id]:[Row Id], [Row Id]@row), 0)

    Will this work for you?
    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!