Countif and substitute

[PaulyboyUK]

Hi

I am trying to create an extra field on a file I receive periodically where I have to create account codes which are 4 text characters plus 01. If there are duplicates on 4 characters then next one replaces 01 with 02 and so on.
I have uploaded a file that hopefully makes sense - what I want to achieve is one calculated field that creates the acc code - not having to have any other helper fields is this possible?

 

[Haz]

Formula A2, then copy and paste:
=IF(D2="", "", UPPER(LEFT(SUBSTITUTE(D2, " ", ""), 4)) & TEXT(COUNTIF(A$1:A1, LEFT(SUBSTITUTE(D2, " ", ""), 4) & "*") + 1, "00"))

 

[PaulyboyUK]

Great stuff - easy when i break down your query - thankyou!