# Create sequence numbers based off of dates and IDs

Copper Contributor

# 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

2 Replies

# Re: Create sequence numbers based off of dates and IDs

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.

# Re: Create sequence numbers based off of dates and IDs

If it was me I would to do it with a PivotTable (attached)