Find Duplicate Entries Across Worksheets

[keith0528]

Greetings,

I'm trying to create a tool that will identify duplicates across worksheets but not within the same worksheet. If "X" exists in worksheets 2, 3, and 4 and it also exists more than once within worksheet 2, i only care that it was also found on sheets 3 and 4. In other words I don't care about dups within a sheet, only across sheets. I know there is an easy solution for this but I can't get it working. Can someone show me the way?

Here is my code:

Sub SDupDel()
Dim ColumnNumber1 As Integer
Dim ColumnNumber2 As Integer
Dim Found1 As Range
Dim Found2 As Range
Dim NumtoCol As String

'Application.ScreenUpdating = False
FirstWS = 1 + 1
LastWS = Worksheets.Count - 2
Worksheets(FirstWS).Activate
Set Found1 = Cells.Find(What:="Ticket", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False)
       
       Found1.Select
       ColumnNumber1 = Selection.Column
       NumtoCol = ConvertToLetter(ColumnNumber1)
Log1_Range = Range(NumtoCol & "65536").End(xlUp).Row

For WkSht_Range = FirstWS To LastWS                             '<------worksheet loop
    Worksheets(WkSht_Range).Activate
    LI1 = Range(NumtoCol & "65536").End(xlUp).Row
    
    
    For row_number = 2 To Log1_Range                                    '<------row loop
        'Worksheets(FirstWS).Activate
        cell_value1 = Cells(row_number, ColumnNumber1).Value
                
        Next_Sheet = WkSht_Range + 1
        'Worksheets(NI).Activate
        Worksheets(Next_Sheet).Select '   <-------go to next worksheet
        
        Set Found2 = Cells.Find(What:="Ticket", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
       
        Found2.Select
        ColumnNumber2 = Selection.Column
        NumtoCol = ConvertToLetter(ColumnNumber2)
        LI2 = Range(NumtoCol & "65536").End(xlUp).Row
        
        j = 2
        
            For i = 2 To LI2
            cell_value2 = Cells(i, ColumnNumber2).Value
                If cell_value2 = cell_value1 Then
                
                
                
                'Place report on Instructions tab
            Sheets("Instructions").Cells(1, 10) = "Duplicates found Across Worksheets"
            RepNum = 2
            
                Sheets("Instructions").Cells(RepNum, 10) = RowNum
                RepNum = RepNum + 1
            Else
                'don't do anything
            End If
                
                
                
                    j = j + 1
                    Worksheets(Next_Sheet).Select
                    
              Next i
            
         Worksheets(WkSht_Range).Select   'go back to 1st sheet being checked
            
    Next row_number
Next WkSht_Range
If WkSht_Range < FirstWS Then
    FirstWS = FirstWS + 1
End If
'Application.ScreenUpdating = True
End Sub



'converts column numbers to letters
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

thank you

 

[JLGWhiz]

maybe this will give you an idea.
Assuming you have a list of values in column A that you want to check for duplicates in other sheets.

Sub dupesNshts()
Dim c As Range, lr As Long
lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'get last row with data in column A
For Each c In Sheets(1).Range("A2:A" & lr) 'Loop down column A, Sheet1
    For i = 2 To Sheets(Sheets.Count).Index 'Loop through sheets
        If Application.CountIf(Sheets(i).UsedRange, c.Value) > 0 Then 'Test for value anywhere in other sheets
            x = x & ", " & i 'Identify sheet index if found
        End If
    Next
    MsgBox c.Value & " was found in sheets 1" & x 'Display sheets where dupes were found.
    x = ""
Next
End Sub

 

[keith0528]

maybe this will give you an idea.
Assuming you have a list of values in column A that you want to check for duplicates in other sheets.

Sub dupesNshts()
Dim c As Range, lr As Long
lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'get last row with data in column A
For Each c In Sheets(1).Range("A2:A" & lr) 'Loop down column A, Sheet1
    For i = 2 To Sheets(Sheets.Count).Index 'Loop through sheets
        If Application.CountIf(Sheets(i).UsedRange, c.Value) > 0 Then 'Test for value anywhere in other sheets
            x = x & ", " & i 'Identify sheet index if found
        End If
    Next
    MsgBox c.Value & " was found in sheets 1" & x 'Display sheets where dupes were found.
    x = ""
Next
End Sub

Thanks GWhiz - I'll try to adapt that to my needs.