Rank duplicates oldest to newest

Chris G

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.

Kelly Moore

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

Kelly Moore

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

Jess A

I just wanted to say thank you! This thread helped me resolve my issue as well.