Duplicate Data Check

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
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!!!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

TAPS_MikeDion

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,613
Office Version
365
Platform
Windows
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
 

TAPS_MikeDion

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

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
I just noticed that you have "ii" instead of "i1"

I'll try that change.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,613
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,007
Messages
5,465,976
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top