Feb 04 2023 05:50 AM
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
Feb 04 2023 06:02 AM
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.
Feb 04 2023 06:05 AM
Typically you would need to incorporate the full date/time into the formula
= 1 + COUNTIFS(year, year, date,"<"&date)
and not rely on position.