Duplicate Data Check

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
619
Office Version
  1. 2011
Platform
  1. MacOS
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:

ABAB
1Last NameFirst NameLocation
2SmithJoeSite A
3DoeJaneSite D
4ClausSantaSite B
5SmithJoeSite C
6ClausSantaSite B

<tbody>
</tbody>


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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
          [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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I just noticed that you have "ii" instead of "i1"

I'll try that change.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top