Row Number for Duplicates in Excel VBA

Recently I came across a situation to find the row number for duplicate values.
In SQL Server there is a inbuilt function ROW_NUMBER() read more but in Excel there is no direct function as such.

The requirement is as follows:

For duplicate Id’s we need Row numbers till the count and for next Id again start from 1 till the count(as shown in the image).

 

We can achieve the desired result using formulas and some logic but I’ve used VBA since I found the easiest way to do this. Following piece of code in VBA will help to achieve the output as required. I’ve provided comments as required. I’ll try to explain further.

1. For the first step we’ll be defining the variables and fetching the range of the data.

Sub RowNum()
'-----Defining Variables
Dim LastRow As Long
Dim LastCol As Long
'-----Sheets
Dim DSheet As Worksheet
Dim PSheet1 As Worksheet
Dim PSheet2 As Worksheet
'-----Define Data Range
LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
'-----Sorting
Range("A1:C" & LastRow).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
'-----Printing LastRow Values
Range("E1") = LastRow

2. Copying the Id field to new range and removing duplicates also getting the count of unique values to print the row numbers. Check image below for reference.

'----Copying values for which duplicates is present
Range("A1:A" & LastRow).Copy Range("F1:F" & LastRow)
'----Remove Duplicates
Sheets("Sheet1").Range("F1:F" & LastRow).RemoveDuplicates Columns:=1, Header:=xlYes
'----Lastrow for column F
LastrowF = Worksheets("Sheet1").Cells(Rows.Count, 6).End(xlUp).Row
'----Printing LastRow
Range("E2") = LastrowF
'----Declaring Range to countif the values in next login
xRange = "A1:A" & LastRow
'----finding Count of unique values from column F
For i = 2 To LastrowF
Range("G" & i) = Application.CountIf(Range(xRange), Range("F" & i))
Next i
'----Finding cummulative sum for H column
For i = 2 To LastrowF
Range("I" & i) = Range("G" & i) + Range("I" & i - 1)
Next i

In column F we are copying and removing duplicates to get unique values. In column G we’re getting the count of those values. Column I we are getting the values to assign end position.

3. Here we’re printing the values and clearing out the unnecessary values.

'----Final Printing
Range("D1") = "Row Number"
For i = 2 To LastrowF
For j = 1 To Range("G" & i).Value
Range("D" & j + 1 + Range("I" & i - 1).Value) = j
Next j
Next i
'----Clear Intermediate contents
With Sheets("Sheet1")
Range("E1:I" & LastrowF).ClearContents
End With
End Sub

Once you follow the steps properly you will get the output as shown below.

 

I tried to make the VBA code as dynamic as possible but you can modify the code as you want.

Download Code from here!

Download Excel Worbook from here!

 

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries.