Forum Discussion

tonyxrc785's avatar
tonyxrc785
Copper Contributor
Feb 04, 2023

Assign sequence numbers to rows based on Year in a table and stick with the records

Hello,

 

I am looking to assign sequence numbers to rows based on Year, and but when a new year starts, restart numbers as well. Please see the example below, I was able to use =COUNTIF($B$2:B2,B2) to populate the number to each row, but the problem I have is how can I make the seq # to stick with each row when I sort the records. currently with countif, if I sort the record by timestamp either newest to oldest or vice versa, it will always stay as this sequence.

1/31/2023 20:4120231
1/31/2023 20:4120232
1/31/2023 20:4620233
1/31/2023 20:4920234
2/1/2023 11:2020235
2/1/2023 11:3920236
2/1/2023 15:2820221
2/1/2023 15:5220222

thanks 

Tony

 

 

excel
Formulas and Functions

2 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 04, 2023

    tonyxrc785 

    Typically you would need to incorporate the full date/time into the formula

    = 1 + COUNTIFS(year, year, date,"<"&date)

    and not rely on position.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 04, 2023

    tonyxrc785 

    If you want the numbers to stick, you cannot use formulas.

    So after using the formulas to generate the sequence numbers, select the column, copy it (Ctrl+C), then paste special (Ctrl+Alt+V) as values. This replaces the formulas with their result.

Resources