# Thread: Determining Row of Duplicates (XP/SR3)

1. ## Determining Row of Duplicates (XP/SR3)

Is it possible to determine via code the row(s) of duplicate entries in a single column?

Example:

<table border=1><td>Row</td><td>Column A</td><td>row1</td><td>Hans</td><td>row2</td><td>Andrew</td><td>row3</td><td>Hans</td><td>row4</td><td>Charlotte</td></table>

I will be using the row number of the duplicate to perform some code on.

Thanks,
John

2. ## Re: Determining Row of Duplicates (XP/SR3)

In cell B1, enter this formula:
<pre>=IF(COUNTIF(\$A\$1:\$A\$4,A1)>1,ROW(),"")
</pre>

and fill down as far as needed, or double click the fill handle (the little black square in the lower right corner of the cell while it is selected).

The \$A\$1:\$A\$4 is for the example you posted; modify as needed.

3. ## Re: Determining Row of Duplicates (XP/SR3)

Hans,

Your suggestion works based on a formula which is then copied down from cell B1. I am looking for a solution in VBA. The following codes replicates what you suggested.

Sub LookForDuplicates()
With Sheets("Sheet1").Range("a1:a4")
Set c = .Find("Hans", LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Offset(0, 1) = c.Row 'This is where I have the issue. Should be passing to a variable(s) within the VBA code; not entering the row into the spreadsheet.
Set c = .FindNextŠ
End If
End With
End Sub

John

4. ## Re: Determining Row of Duplicates (XP/SR3)

This?

Sub LookForDuplicates()
Dim i As Long
Dim c As Long
Dim r As Long
For i = 1 To 4
c = WorksheetFunction.CountIf(Range("A1:A4"), Range("A" & i))
If c > 1 Then
' Duplicate found
r = i
' Do something with it
Debug.Print "Duplicate in row " & r
End If
Next i
End Sub

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•