Formula to return a number based on the content of another cell

[canarycat123]

Hi there, I’m hoping someone can help me with this question.

I have a series of formula being used to generate unique reference numbers, finishing with CONCATENATE, as seen in the screencap attached. I am using a simple =IF(H60=””,””,ROW()-2) formula to generate the final four digits of the reference number. However, what I am trying to achieve is slightly more complex.

The first portion of characters, in this example KGV and MISC (produced in another cell using VLOOKUP), represent different geographical areas. What I would like to achieve is a formula that can identify the next number in the series related to each geographical area. So, in the attached example I would like MISC to return MISC-LS-020-001 (rather than -0058), as that would be the first KGV record in the worksheet. If I were then to add another MISC I want it to return 002 and so on. If I were then to add another KGV record it should start with the next number in the sequence related to KGV, 0058 in this example.

Any help/advice would be appreciated!

 

[canarycat123]

Do you mean that the last 4 numbers should increment solely based on the value in col A?

Yes - you said it far better than I did!

 

[Fluff]

Ok, how about

=TEXTJOIN("-",,A2:C2,TEXT(COUNTIFS(E$1:E1,A2&"*")+1,"0000"))

 

[canarycat123]

Ok, how about

=TEXTJOIN("-",,A2:C2,TEXT(COUNTIFS(E$1:E1,A2&"*")+1,"0000"))

That's perfect, thank you so much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.