Serial for unique entries in a column

Question

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.

can anyone help with a macro?

Can you include some sample data? – Andrew Mortimer Mar 05 '17 at 09:18 — Andrew Mortimer, Mar 05 '17 at 09:18
i have added a sample results of serial required – sapna Mar 05 '17 at 09:32 — sapna, Mar 05 '17 at 09:32

score 0 · Answer 1 · answered Mar 05 '17 at 10:04

0

You would need extra column. In E2 write

=MATCH(D2,D:D,0)

To find first instance of each SHOE

Then in new column (F2) write:

=SUMPRODUCT(--(E2>$E$2:$E2),1/COUNTIF($E$2:$E2,$E$2:$E2))+1

This will give you desired serial number.

answered Mar 05 '17 at 10:04

OES

301
2
6

can we do via macro – sapna Mar 05 '17 at 10:07

score 0 · Answer 2 · answered Mar 05 '17 at 10:19

0

Here is VBA solution:

Sub SHOP_Serial()
Dim mtc As Long

On Error Resume Next

For i = 2 To ActiveSheet.Range("D65536").End(xlUp).Row
mtc = 0
mtc = WorksheetFunction.Match(Range("D" & i), Range("D1:D" & i), 0)
    If Cells(mtc, "E").Value = 0 Then
        Cells(i, "E").Value = WorksheetFunction.Max(Range("E1:E" & i - 1)) + 1
    Else
        Cells(i, "E").Value = Cells(mtc, "E").Value
    End If

Next i

End Sub

answered Mar 05 '17 at 10:19

OES

301
2
6

thanks but what if SHOE column is not at 'D' and it only works if next column E is empty cant we make dynamic i.e. macro finds header SHOE and insert column with header serial . BTW number works fine. – – sapna Mar 05 '17 at 10:35

score 0 · Accepted Answer · answered Mar 05 '17 at 11:14

0

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

answered Mar 05 '17 at 11:14

OES

301
2
6

does great job, Can you tell how to put header on serial column? – sapna Mar 05 '17 at 17:08
It will search word SHOE in a first row. If you need another row or another word change this part: shoe = WorksheetFunction.Match("SHOE", Range("A1:IV1"), 0) – OES Mar 05 '17 at 17:33
Means where it put serial number its header remains empty can we put SN on top? – sapna Mar 05 '17 at 17:47

Serial for unique entries in a column

3 Answers3