How to assign unique number to column duplicates?

[Heather]

Hi All,
I need to assign a unique number to a set of duplicates all in one column in
Excel 2007.

so columnA will has about 9000 numbers, some of them unique, and others are
duplicates of 2-4 approx.

I used to conditional formatting to show which are duplicates, but need to
be able to assign a unique number to each set duplicates, that will be in
sequential order...

e.g.
ColumnA ColumnB(unique ID)
01233 0001
01233 0001
01234 -
01255 0002
01255 0002
etc....

Any ideas please? I don't know how to do programming, just formulas in excel.
Thanks
Heather

 

[Don Guillett]

This should do it even if you have more than 2 dups. Must be SORTED

option explicit
Sub uniquenums()
Dim i As Long
Dim un As Long
Dim mc As Double
un = 1
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
mc = Application.CountIf(Columns(1), Cells(i, 1))
If mc > 1 And Cells(i - 1, 1) <> Cells(i, 1) Then
Cells(i, 2).Resize(mc) = un
un = un + 1
End If
Next i
Columns(2).NumberFormat = "0000"
End Sub

 

[Herbert Seidenberg]

Excel 2007 Table
Choice of six different formulas.
http://www.mediafire.com/file/wdhcztdyu3m/12_23_09.xlsx

 

[Heather]

Hi Herbert,
thanks for that, it looks like exactly what I need. I've tried copying the
formula into my table and then substituting your "NyNB" for my array, which I
drag teh cursor over to select. Excel doesn't seem to like this!?

How did you select your "NyNB"?

Thanks
Heather