Search code examples
excelvisual-studio-macrosvba

Serial for unique entries in a column

Hi I have specific column with Header 'SHOE' which contains repetitive numbers ~50000 I would like to have adjacent column having serial number for each unique number. there can be a situation that same SHOE number can be present multiple time in entire column.

further macro should only run if SHOE column is present.

enter image description here

can anyone help with a macro?

Solution

  • This will put result in FIRST EMPTY COLUMN based on data in column with SHOE header:

    Sub SHOE_Serial()
Dim mtc As Long
Dim shoe As Long
Dim LastColumn As Long

On Error Resume Next

LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column + 1
shoe = WorksheetFunction.Match("SHOE", Range("A1:IV1"), 0)

For i = 2 To ActiveSheet.Cells(65536, shoe).End(xlUp).Row
mtc = 0
mtc = WorksheetFunction.Match(Cells(i, shoe), Range("A1:A" & i).Offset(, shoe - 1), 0)
    If Cells(mtc, LastColumn).Value = 0 Then
        Cells(i, LastColumn).Value = WorksheetFunction.Max(Range("A1:A" & i).Offset(, LastColumn - 1)) + 1
    Else
        Cells(i, LastColumn).Value = Cells(mtc, LastColumn).Value
    End If

Next i

End Sub