Maybe...

Assumes the CO Number are grouped

Code:

Sub aTest()
Dim dic As Object, vdata As Variant, i As Long, s As Variant
Dim vResult As Variant
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
vdata = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
vResult = Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
For i = 1 To UBound(vdata) - 1
If dic.exists(vdata(i, 1)) Then
dic(vdata(i, 1)) = dic(vdata(i, 1)) + 1
vResult(i, 1) = "PPO/20/" & Right("0000" & s, 4) & "-" & dic(vdata(i, 1))
Else
s = s + 1
If vdata(i, 1) = vdata(i + 1, 1) Then
dic(vdata(i, 1)) = 1
vResult(i, 1) = "PPO/20/" & Right("0000" & s, 4) & "-" & dic(vdata(i, 1))
Else
dic(vdata(i, 1)) = ""
vResult(i, 1) = "PPO/20/" & Right("0000" & s, 4)
End If
End If
Next i
Range("B2").Resize(i - 1) = vResult
End Sub

M.

## Like this thread? Share it with others