Incrimental numbers, sort text then add, serialization

Hi there.

I want excel to group a range of coded text values and apply an incremental number to the output 2000+ items.
I can get excel to apply the number incrementally to the list but want it to incrementally apply it to the Group not the whole.

How it is:         How it needs to be:
BE-ARA-001     BE-ARA-001
BE-BEL-002      BE-BEL-001
BE-BEL-003      BE-BEL-002
BE-ARA-004     BE-ARA-002
BE-ARA-005     BE-ARA-003
BE-BEL-006      BE-BEL-003
BE-BED-007     BE-BED-001
BE-BED-008     BE-BED-002
BE-BRU-009     BE-BRU-001
BE-BRU-010     BE-BRU-002
BO-BOO-011    BO-BOO-001
BE-ARA-012     BE-ARA-004
BO-BOO-013    BO-BOO-002
CO-URI-014     CO-URI-001
CO-COO-015    CO-COO-001
CO-URI-016     CO-URI-002
CO-URI-017     CO-URI-003

Any help would be greatly appreciated.
File attached.
HERO UPLOAD Master V4 unlocked V3.xlsx