VBA to create unique ID

[Certified]

On my schedule each company has an ID such as...

1010
1020
1030
1040
1040
1040
1050
1050

and each company is owned by another company for example

1010 owned 100% by A
1020 owned 100% by A
1030 owned 100% by A
1040 owned 33.33 by A
1040 owned 33.33 by B
1040 owned 33.34 by B
1050 owned 50% by A
1050 owned 50% by B

I want created unique code each lower tier company utilizing their company ID. but I am running into an issue with company IDs with more than one owner. How could I produce the following using VBA?

1010_0 owned 100% by A
1020_0 owned 100% by A
1030_0 owned 100% by A
1040_1 owned 33.33 by A
1040_2 owned 33.33 by B
1040_3 owned 33.34 by B
1050_4 owned 50% by A
1050_5 owned 50% by B

I hope this makes sense.

Thanks

 

[NeonRedSharpie]

Why do you need to do it in VBA? Is the additional verbage (owned 100% by A) in a column somewhere? I would look at just using a helper column, and then concatenate the information.

 

[johnmpl]

Hey!

If you have the data in A2:A9, you could do it with formulas and 2 helper columns...

In B2, use this fórmula:
=LEFT(A2,4)
In C2, use this fórmula:
=IF(COUNTIF($B$2:$B$9,B2)=1,0,SUM(C1,1))
In D2, use this fórmula:
=B2&"_"&C2&RIGHT(A2,LEN(A2)-4)

And all three formulas copy it down.

Please comment!
I hope it helps! God Bless you!

 

[Certified]

Thanks! It works!!