Increment a row based on another column with duplicates possible

[Sabosis]

Hello-

I have header info in row 1, and want to add a "record number" in column A.I want the number in column A to increment based on value in column J, butduplicate itself if duplicates are found in J. This is the last step to the spreadsheet, all other formating and sorting has been done to this point.

column A column J
1 15326
2 85632
3 25415
3 25415
3 25415
4 65268
5 45874

Any help is greatly appreciated

Thanks

Scott

 

[Jason Lepack]

Sub uniqueIndex()
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set wb = ActiveWorkbook
Set ws1 = ActiveSheet
Set ws2 = wb.Sheets.Add

ws1.Range("J:J").AdvancedFilter ACtion:=xlFilterCopy,
CopyToRange:=ws2.Range("A1"), unique:=True
ws2.Range("B1").Value = "1"
ws2.Range("B1").AutoFill ws2.Range("B1",
ws2.Range("A65536").End(xlUp).Offset(0, 1)), xlFillSeries

ws1.Range("A1").Formula = "=vlookup(J1,'" & ws2.Name & "'!A:B,
2,false)"
ws1.Range("A1", ws1.Range("J65536").End(xlUp).Offset(0,
-9)).FillDown
ws1.Range("A:A").Copy
ws1.Range("A1").PasteSpecial xlPasteValues

Application.DisplayAlerts = False
ws2.Delete
Application.DisplayAlerts = True
Set ws2 = Nothing
Set ws1 = Nothing
Set wb = Nothing
End Sub