Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    firstAddress = c.Address
    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Š
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub


    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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
  •