Results 1 to 7 of 7

Thread: Duplicate Data Check
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Duplicate Data Check

    Hi everybody,

    I haven't been able to figure this one out on my own.

    I'll try to keep this as simple as possible. I need to search the rows of data for duplicate entries of Last Name (Col A), First Name (Col B) and Location (Col AB). I have 3 radio buttons used to select the sorting of the ComboBox drop-down list, which works fine.

    The 3 sort buttons used are First Name, Last Name, and Location.

    When the user clicks on a selection from the drop-down list it populates numerous labels with the associated information from a row in the Excel sheet. This works fine as well. However, when the user selects a line from the drop-down list, I also need it to search from A2 to last row looking to see if there are any duplicate last name, first name, and location entries (columns A, B and AB respectively).


    Example:

    A B AB
    1 Last Name First Name Location
    2 Smith Joe Site A
    3 Doe Jane Site D
    4 Claus Santa Site B
    5 Smith Joe Site C
    6 Claus Santa Site B


    In the above example, rows 2 and 5 match First Name and Last Name.
    Also, rows 4 and 6 match completely.



    • If Last Name and First Name match, I need to set the ".Value = true" on my radio button DuplicateFoundButton.
    • If all 3 match, I need to set the ".Value = true" on my radio button DuplicateFoundButton and change a labels ForeColor (WarnOfMultipleLocations.ForeColor = &HFF&).



    To anyone that tries to help...THANK YOU!!!
    Mike Dion

  2. #2
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicate Data Check

    I've done some work on my own and was able to come up with this.

    Code:
    Sub FindDupLocs()
    
        Dim ws As Worksheet
        Dim i1 As Long, i2 As Long, i3 As Long
        Dim LastRow As Long
        Dim FoundMatch As Long
    
        Set ws = Sheets("DataSheet")
        LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        FoundMatch = 1
        PayrollInformationForm.DuplicateLocations.Visible = False
    
        SelectedRow = PayrollInformationForm.ComboBox1.ListIndex + 2
        
        With PayrollInformationForm.ComboBox1
          For i1 = 2 To LastRow
            If SelectedRow <> i1 Then
              'Not same row as selected in ComboBox1 so check for matching last names
              If Cells(i1, 2).Value = Cells(SelectedRow, 2).Value Then
                'The last name matches the last name in the ComboBox selection
                For i2 = 2 To LastRow
                  If SelectedRow <> i2 Then
                    'Not same row as selected in ComboBox1 so check for matching first names
                    If Cells(i2, 3).Value = Cells(SelectedRow, 3).Value Then
                      'The first name matches the first name in the ComboBox selection
                      FoundMatch = FoundMatch + 1
                      For i3 = 2 To LastRow
                        If SelectedRow <> i3 Then
                          'Not same row as selected in ComboBox1 so check for matching locations
                          If Cells(i3, 28).Value = Cells(SelectedRow, 28).Value Then
                            'The location matches the location in the ComboBox selection
                            'This employee has been entered into the system more than once at the same location!
                            PayrollInformationForm.DuplicateLocations.Visible = True
                          End If
                        End If
                      Next i3
                    End If
                  End If
                Next i2
              End If
            End If
          Next i1
        End With
        PayrollInformationForm.NumberOfAssignedLocations = " " & FoundMatch
    End Sub
    It does work, but would anyone happen to know if there's a way to shorten this up and make it faster?

    Thanks.
    Mike Dion

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Duplicate Data Check

    How about
    Code:
    Sub FindDupLocs()
    
       Dim ws As Worksheet
       Dim i1 As Long
       Dim LastRow As Long
       Dim FoundMatch As Long
       
       Set ws = Sheets("DataSheet")
       LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
       FoundMatch = 1
       PayrollInformationForm.DuplicateLocations.Visible = False
       
       SelectedRow = PayrollInformationForm.ComboBox1.ListIndex + 2
        
       For i1 = 2 To LastRow
          If SelectedRow <> i1 Then
             'Not same row as selected in ComboBox1 so check for matching last names
             If ws.Cells(i1, 2).Value = ws.Cells(SelectedRow, 2).Value And ws.Cells(i1, 3).Value = ws.Cells(SelectedRow, 3).Value Then
                FoundMatch = FoundMatch + 1
                If ws.Cells(ii, 28).Value = ws.Cells(SelectedRow, 28).Value Then
                   'The location matches the location in the ComboBox selection
                   'This employee has been entered into the system more than once at the same location!
                   PayrollInformationForm.DuplicateLocations.Visible = True
                End If
             End If
          End If
       Next i1
       PayrollInformationForm.NumberOfAssignedLocations = " " & FoundMatch
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicate Data Check

    Hi Fluff,

    Thanks so much for your response!

    When I ran your code I got the following error at this line of code.

    Code:
    If ws.Cells(ii, 28).Value = ws.Cells(SelectedRow, 28).Value Then

    Run-time error '1004':

    Method '_Default' of object 'Range' failed
    Mike Dion

  5. #5
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicate Data Check

    I just noticed that you have "ii" instead of "i1"

    I'll try that change.
    Mike Dion

  6. #6
    Board Regular TAPS_MikeDion's Avatar
    Join Date
    Aug 2009
    Location
    Massachusetts
    Posts
    489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Duplicate Data Check

    Worked perfectly!!!

    Thank you!
    Mike Dion

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,130
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Duplicate Data Check

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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