How to correct the formula so the sequence is correct?

[Sampoline]

From the above picture, you can see that I'm trying to combine the name together. The 2nd column is sequence numbers. I want to convert it to a 4-digit format. I've got that part right. But not too sure how to proceed with correcting the sequencing. Any solutions?

 

[Anthony47]

Hummmm...
Basically you are asking to correct the formula so you get the right result, and let us guessing which is the right result.

Well, my guess is that this formula could do the job:

=S2&"_"&TEXT(T2,"000")

If that formula give you the correct answer, then you might consider this second formula, that doesn't need column T for creating the sequence:

=S2&""_""&TEXT(COUNTIF($S$2:S2,S2),"0000")

This counts the occourrences of the code to create the sequence

If my guess don't met your expectations then I suggest that you explain which results you are looking for

Bye

 

[maabadi]

Also you can Use this:

=S2&"_"&REPT("0",4-LEN(T2))

 

[Fluff]

@maabadi
Have you actually tested that formula?

 

[Anthony47]

@maabadi
Have you actually tested that formula?

Not to mention that my first formula misses one "0"...

 

[maabadi]

@Fluff , Sorry I think he wants only mix Cell S2 Data with 4 digit number from T2. This is Correct Formula:
=

=S2&"_"&REPT("0",4-LEN(ROW()-1))&ROW()-1

 

[Fluff]

If you try it, you'll see.

 

[maabadi]

I Correct It @Fluff