Macro to create Sub-account numbers

[howard]

I import data and there are several account numbers in Col A on sheet "Imported data" that have the same account number that was downloaded from another program which stored the account number in one field and a sub-numbber in another field. When downloading the sub-field in not part of the download

I would like a macro to place a 01 to end of the number that appears a second time in Col A 02 to the number when it appears a second time 03 a third time etc etc eg if 7189 appears a second time 01 to be shown at the end 718901 , if it appears again 718902. If 7000 appears a second time 01 to be placed at the end 700001 a second time 700002 etc

Your assistance in this regard is most appreciated

 

[mirabeau]

Thanks for the help, it works perfectly (added sheets("import").select as I have several sheets)

I also have a sheets Fassets, that also has duplicate numbers that I need to add 01 at the end. However, the numbers are in column C. I tried copying your code to the end of the previous code and amending to suit my needs i.e Col C sheets (FASSETS), but cannot get it to work. It would be appreciated

Regards

Howard

Howard,

Is this referring to the code I provided?

If so, and you want it to work on column C, make the indicated changes

Sub lxxl() 'for howard

Dim a, d As Object, i As Long, rws As Long
Set d = CreateObject("scripting.dictionary")
rws = Cells(Rows.Count, "C").End(3).Row
a = Cells(1,"C").Resize(rws)

For i = 1 To rws
    If Len(a(i, 1)) > 0 Then
        If Not d.exists(a(i, 1)) Then
            d(a(i, 1)) = 0
        Else
            d(a(i, 1)) = d(a(i, 1)) + 1
            a(i, 1) = a(i, 1) & "0" & d(a(i, 1))
        End If
    End If
Next i
Cells(1,"C").Resize(rws) = a

End Sub

 

[howard]

Thanks Mirabeau

Your assistance and input is much appreciated

Regards

Howard

 

[mirabeau]

Thanks Mirabeau

Your assistance and input is much appreciated

Regards

Howard

hmm ...

after all that i'm really little the wiser as to the usefulness of whatever I provided

just one of the hazards, i guess, of trying to make useful contributions to forums of this kind

c'est la vie