Formula to assign consecutive sub IDs to parent IDs like example below?

I am trying to find a formula that can add consecutive sub numbers to a parent id then reset when there is a new parent id like example below. I need it to be flexible so that it's not just limited to one sequence.

Does anyone know how to do this? You're an absolute rockstar if you can help me figure this out

Parent IDs Sub IDs
21-44-003 21-44-003-001
21-44-003 21-44-003-002
21-44-003 21-44-003-003
21-44-004 21-44-004-001
21-44-004 21-44-004-002
21-44-004 21-44-004-003
21-44-008 21-44-008-001
21-44-008 21-44-008-002
21-44-008 21-44-008-003
21-44-008 21-44-008-004
21-44-008 21-44-008-005
21-44-009 21-44-009-001
21-44-009 21-44-009-002
21-44-009 21-44-009-003
21-44-009 21-44-009-004
21-44-009 21-44-009-005
21-44-009 21-44-009-006
21-44-009 21-44-009-007

I can't test this out as you haven't attached a sample Excel workbook (see yellow banner at the top of the screen for details of how to), but try this in B2:

=IF(A2="","",A2&"-"&TEXT(COUNTIF(A$2:A2,A2),"000"))

Copy down to the bottom of your list, or beyond.

Hope this helps.

Pete

Originally Posted by Pete_UK

I can't test this out as you haven't attached a sample Excel workbook (see yellow banner at the top of the screen for details of how to), but try this in B2:

=IF(A2="","",A2&"-"&TEXT(COUNTIF(A$2:A2,A2),"000"))

Copy down to the bottom of your list, or beyond.

Hope this helps.

Pete

WOW NICE! IT WORKS PERFECTLY

YOU'RE A MIRACLE WORKER - I've been stuck on this for a while now.

THANK YOU SO MUCH!

Dav

Glad to help, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.


Pete