Auto fill row base on info from other row

[nrm0504]

So I have two rows in this sheet the rest don't matter right now. "TAG" and "LENGTH"

what I have to do is go in and give each separate length and label. as you can see in the attached file the label is B1-XX with the B1 being a constant and the last two numbers represent the length.

so if I had lengths of 12,14,16,18 for example I would want excel to autofill in the TAG column

[#2]

@nrm0504 

You have length like 40.375, in your sample tag against it is B1-01. Why? Or it shall be B1-40.375 ?

[#3]

@Sergei Baklanso these are tags that get imported into autocad. Each different length gets a new tag

with B1 being a constant. i need excel just to number in numerical order (B1-01, B1-02, B1-03.ETC...)

ive attached a completed sheet to help see it more. I would like to label the first length B1-01 and then autofill down changing the last two number each time a new length comes. the last two number do not have to say the length 

[#4]

@nrm0504 
1) I added it to column H to comapre with your sample

2) My assumption is that lengths go sequentially, e.g. l1,l1,l2,l2,l2,l3,l3 etc, not as l1,l2,l1,l3,l1,l2, etc

3) I'd suggest to add first tag manually (B1-01 in H2). That gives more flexibility, at any time we may change B1 on something else. However, if not suitable we may complicate the formula a bit and make H2 also calculatable with B1 hardcoded

Formula in B3 could be

= LEFT(H2,3) & TEXT(RIGHT(H2,2)+(COUNTIF($D$2:$D3,$D3)=1),"00")

and drag it down.

4) In couple of place formula result is differ form the sample, I marked one them in blue. Here length is changed, I guess we have to change number in tag as well.

[#5]

yea I think you got and those two numbers you marked are the exact reason why I want excel to label them (human error lol) the normal list of these pcs are a thousand lines sometimes i dont habe time to manually do this. It look like your code is working wonderfully however i must admit im an excel novice. im  not sure how to apply the code to a sheet

[#6]

@Sergei BaklanI GOT IT WORKING THANKS SO MUCH!!!

[#7]

@nrm0504 , you are welcome, glad to help