Forum Discussion
Create sequence numbers based off of dates and IDs
Hi, I am looking for some help.
I am looking to insert a column with a formula in my sheet of employees to provide the sequence number of dates for employee records from most current to oldest.
The formula needs to match the same employee numbers, look at all the record dates for that specific employee and provide a sequence number for the date of the record.
Info I have:
| A | B | C | D | |
| 1 | Emplid | Name | Record Date | Seq Num |
| 2 | 300000296 | CA | 2021/12/04 | |
| 3 | 300000296 | CA | 2021/12/26 | |
| 4 | 300000296 | CA | 2022/01/23 | |
| 5 | 300000296 | CA | 2022/12/25 | |
| 6 | 300000296 | CA | 2023/10/08 | |
| 7 | 300000296 | CA | 2023/11/26 | |
| 8 | 300000509 | MC | 2013/09/08 | |
| 9 | 300000509 | MC | 2014/06/15 | |
| 10 | 300000509 | MC | 2014/08/17 | |
| 11 | 300000509 | MC | 2014/12/07 | |
| 12 | 300000509 | MC | 2015/02/01 | |
| 13 | 300000509 | MC | 2015/03/15 | |
| 14 | 300000509 | MC | 2015/06/14 | |
| 15 | 300000509 | MC | 2015/08/02 | |
| 16 | 300000509 | MC | 2016/01/03 | |
| 17 | 300000509 | MC | 2017/01/01 | |
| 18 | 300000509 | MC | 2017/06/11 | |
| 19 | 300000509 | MC | 2018/06/10 | |
| 20 | 300000509 | MC | 2019/06/09 |
Want:
| A | B | C | D | |
| 1 | Emplid | Name | Record Date | Seq Num |
| 2 | 300000296 | CA | 2021/12/04 | 6 |
| 3 | 300000296 | CA | 2021/12/26 | 5 |
| 4 | 300000296 | CA | 2022/01/23 | 4 |
| 5 | 300000296 | CA | 2022/12/25 | 3 |
| 6 | 300000296 | CA | 2023/10/08 | 2 |
| 7 | 300000296 | CA | 2023/11/26 | 1 |
| 8 | 300000509 | MC | 2013/09/08 | 13 |
| 9 | 300000509 | MC | 2014/06/15 | 12 |
| 10 | 300000509 | MC | 2014/08/17 | 11 |
| 11 | 300000509 | MC | 2014/12/07 | 10 |
| 12 | 300000509 | MC | 2015/02/01 | 9 |
| 13 | 300000509 | MC | 2015/03/15 | 8 |
| 14 | 300000509 | MC | 2015/06/14 | 7 |
| 15 | 300000509 | MC | 2015/08/02 | 6 |
| 16 | 300000509 | MC | 2016/01/03 | 5 |
| 17 | 300000509 | MC | 2017/01/01 | 4 |
| 18 | 300000509 | MC | 2017/06/11 | 3 |
| 19 | 300000509 | MC | 2018/06/10 | 2 |
| 20 | 300000509 | MC | 2019/06/09 | 1 |
Thank you
In D2:
=COUNTIFS($A$2:$A$1000,$A2,$C$2:$C$1000,">="&$C2)
Adjust the ranges if you have more than 1000 rows of data
Fill down.
