Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    detecting duplicates on excel 2003

    I want to detect duplicates as follows:
    Cell A2:Cell G2(To type in my own numbers)
    Cell A4:Cell G4(To refer to CellA2:CellG2 for my duplicates)

    Any suggestions???

    quantumfreak

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you elaborate? I don't understand what you have nor what you want in the cells. Perhaps attaching an example sheet may help.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Quantum,

    Here is some code that is placed in the worksheet's Change event routine. When you type in values for cells in the range A4 to G4, it will automatically do a comparison to the values in the cells A2 to G2. If you entered a duplicate value you will be notified with a message pop up.

    Pic1.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell1 As Range
    Dim cell2 As Range
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A4:G4")) Is Nothing Then
        For Each cell1 In Range("A4:G4")
            For Each cell2 In Range("A2:G2")
                If cell1 = cell2 Then
                    MsgBox "You have entered a duplicate value that exists in Row 2"
                    Exit Sub
                End If
            Next cell2
        Next cell1
    End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-02-20 at 07:37.

  4. #4
    Lounger
    Join Date
    May 2003
    Posts
    29
    Thanks
    3
    Thanked 1 Time in 1 Post
    COUNTIF would do it - anything that appears in the list multiple times would return a value other than one

Posting Permissions

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