Help with formula to add prefix letters and -001 for each duplicate number listed.

[mcvideo]

Greetings Everyone.

Any help with the following formula that will help me save time. I am tasked with the following. I have pasted an example of what I am trying to accomplish. I have a list of project numbers. Each project number listed twice is a sub project that is linked to the main project number. I need to create a new proj# keeping the proj# in tack but adding "PGB" in front of it and -001 for each sub project that is linked. So if I have one project# listed then all I need is PFG-17658-001. If a project is listed twice then the only thing that changes is the -001 for the first project # listed then adding a 002 for the second and so on if there was a third entry for a project. Hope this helps. Please let me know if this is not clear.

My file has thousand of rows so this will be a formula I use and copy straight down.

Thank you and have a great day!

Proj # new Proj #
176589 PGB-176589-001
176589 PGB-176589-002
176538 PGB-176538-001
176345 PGB-176345-001
176345 PGB-176345-002
176345 PGB-176345-003
175478 PGB-175478-001
175478 PGB-175478-002

Image1.JPG

[Fluff13]

How about

Please Login or Register  to view this content.

[mcvideo]

Hi @Fluff13. Testing it out now. I will report back.

[CRIMEDOG]

=COncatenate("PGB-",A1,Countif($A$1:$A1,$A1))

A1 is the Project number (columnA) $A$1:$A1 $A$1 is the first project number $A1 is the current Project number (as you copy it down - it will reflect A1, A2, A3...)

if you need the 00 I think =Concatenate("PGB-",A1,Text(Countif($A$1:$A1,$A1),"###"))

EDIT left out ,"-", before my Text(....

[CRIMEDOG]

good job fluff - I am a slow typist :D

[mcvideo]

How about

Please Login or Register  to view this content.

Thank you for the help.. worked great.

[Fluff13]

Glad to help & thanks for the feedback