Generating unique codes

[Oladapo Sorinola]

Hi, please i have a challenge i want you to help with. I have a list of vendors and i am generating a new set of codes from the current one

I will give you an example. I have the following vendors with codes
Description Code
MURPHY SHIP.AND COMM.SERV. LTD VMUR0001
MURPHY SHIP.AND COMM.SERV. LTD VMUR0001
MURI AND CO. VMUR0002
MURI AND CO. VMUR0002
WEATHERFORD NIG. LTD. VWEA0001
WEATHERFORD NIG. LTD. VWEA0001
WEATHER LIMITED VWEA0002
WEATHERFORD SERVICES LTD VWEA0003

There is a constant "V" for each and every Vendor,followed by the first 3 letters of the Vendors name. followed by a serial number 0001 then 0002 for the next vendor with same first 3 letters. When the first 3 letters are not same, the serial number defaults to 0001
on and on and on. I have the vendor list starting from "A" to "Z". Thank you

 

[bosco_yip]

In B2, formula copy down :

="V"&LEFT(A2,3)&TEXT(SUMPRODUCT((LEFT(A$2:A2,3)=LEFT(A2,3))/COUNTIF(A$2:A2,A$2:A2)),"000")

Regards
Bosco

 

[Oladapo Sorinola]

Hello Bosco,

That was excel-lent!

It worked...Thanks a lot