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

Copper Contributor

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:41 2023 1 1/31/2023 20:41 2023 2 1/31/2023 20:46 2023 3 1/31/2023 20:49 2023 4 2/1/2023 11:20 2023 5 2/1/2023 11:39 2023 6 2/1/2023 15:28 2022 1 2/1/2023 15:52 2022 2

thanks

Tony

2 Replies

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

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.

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

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

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

and not rely on position.