Duplicate Data Check

[TAPS_MikeDion]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]AB[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Last Name[/TD]
[TD="align: center"]First Name[/TD]
[TD="align: center"]Location[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Smith[/TD]
[TD]Joe[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Doe[/TD]
[TD]Jane[/TD]
[TD]Site D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Claus[/TD]
[TD]Santa[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Smith[/TD]
[TD]Joe[/TD]
[TD]Site C[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Claus[/TD]
[TD]Santa[/TD]
[TD]Site B[/TD]
[/TR]
</tbody>[/TABLE]


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!!!

 

[TAPS_MikeDion]

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

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
          [COLOR=#0000ff]'Not same row as selected in ComboBox1 so check for matching last names[/COLOR]
          If Cells(i1, 2).Value = Cells(SelectedRow, 2).Value Then
            [COLOR=#0000ff]'The last name matches the last name in the ComboBox selection[/COLOR]
            For i2 = 2 To LastRow
              If SelectedRow <> i2 Then
                [COLOR=#0000ff]'Not same row as selected in ComboBox1 so check for matching first names[/COLOR]
                If Cells(i2, 3).Value = Cells(SelectedRow, 3).Value Then
                  [COLOR=#0000ff]'The first name matches the first name in the ComboBox selection[/COLOR]
                  FoundMatch = FoundMatch + 1
                  For i3 = 2 To LastRow
                    If SelectedRow <> i3 Then
                      [COLOR=#0000ff]'Not same row as selected in ComboBox1 so check for matching locations[/COLOR]
                      If Cells(i3, 28).Value = Cells(SelectedRow, 28).Value Then
                        [COLOR=#0000ff]'The location matches the location in the ComboBox selection
                        'This employee has been entered into the system more than once at the same location![/COLOR]
                        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.

 

[Fluff]

How about

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

 

[TAPS_MikeDion]

Hi Fluff,

Thanks so much for your response!

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

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

Run-time error '1004':

Method '_Default' of object 'Range' failed

 

[TAPS_MikeDion]

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

I'll try that change.

 

[TAPS_MikeDion]

Worked perfectly!!!

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback