Genius Needed: code to find duplicates across tabs

keith0528

Active Member
Joined
Apr 23, 2009
Messages
250
Greetings,

I have some code that attempts to locate duplicate entries, not within a worksheet, but across worksheets and then post sheet name of found dup in a sheet called "Instructions".

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_Carrier", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False)
       
       Found1.Select
       ColumnNumber1 = Selection.Column
       NumtoCol = ConvertToLetter(ColumnNumber1)
'RI = Range("a65536").End(xlUp).Row
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_Carrier", 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

I would be very grateful if someone could show me how this code should look.

thank you.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi keith0528,

Are you wanting to supply a search string and then have the code see if and where it is on all worksheets?

And if found you want a list on of such on the worksheet.

Regards,
Howard
 
Upvote 0
Hi keith0528,

Are you wanting to supply a search string and then have the code see if and where it is on all worksheets?

And if found you want a list on of such on the worksheet.

Regards,
Howard

Hi Howard,

No not a search string, rather starting in sheet 2 it will find the column "ticket_carrier" and grab the 1st value in cell...lets say G2, lets call the value 123_XYZ. Lets say there are 10 worksheets in the workbook, if we're in wksht 2, I want it to look at 3 thru 10 to see if "123_XYZ" is found again. But I'm not interested if it exists more than once in wksht 2, only other worksheets. Then lets it found "123_XYZ" in sheets 3, 7,and 9 i it would post on the "Instructions" tab "123_XYZ" sheets 3,7,9.
This would grab the value in cell G3 and go thru the process again, then G4, G5 etc to the end of range. Then jump to sheet 3 and start over.

I know it's alot. That's why I'm having trouble getting it off the ground. Do u think this is possible?

thanks
 
Upvote 0
This would grab the value in cell G3 and go thru the process again, then G4, G5 etc to the end of range. Then jump to sheet 3 and start over.

Each worksheet 2 to 10 will have a column G "ticket_carrier" list of values and all those values will be searched for in all sheets 2 to 10? And the list of all of the "finds" will be listed on sheet Instructions.

If so, yes, that is a lot. Very well may be above my grade, but I will give it a look.

Howard
 
Upvote 0
Each worksheet 2 to 10 will have a column G "ticket_carrier" list of values and all those values will be searched for in all sheets 2 to 10? And the list of all of the "finds" will be listed on sheet Instructions.

If so, yes, that is a lot. Very well may be above my grade, but I will give it a look.

Howard

Yep thats about the gist of it. the only other piece you left off was that I don't want to see duplicates within a worksheet. But I think I can resolve that by concatenating the cell value with the worksheet name, if the worksheet name is the same it ignores it.

If you can come up with a working solution or even partial, i'd be grateful.

thanks
 
Upvote 0
How many sheets would you be searching? Is the text going to be exact in everything you are searching? The first question is more for run time - while you could get something that does what you want, if it cannot execute in a reasonable time frame, its not really a good solution. I ask the second part because this seems like a simple loop through every sheet other than the current sheet with a match() formula to check for ANY matches. (this solution should be pretty quick to execute depending on how many values you're searching for).

You may need to come up with some algorithms here also that say you may only have 5 or 10 unique values on your current sheet so that you're not wasting massive processing time looping through every single row when you only have a few unique values to begin with.
 
Upvote 0
Got what may be a solution. Doing some tweaking and seeking help with code that has me way bogged down.

Howard
 
Upvote 0
How many sheets would you be searching? Is the text going to be exact in everything you are searching? The first question is more for run time - while you could get something that does what you want, if it cannot execute in a reasonable time frame, its not really a good solution. I ask the second part because this seems like a simple loop through every sheet other than the current sheet with a match() formula to check for ANY matches. (this solution should be pretty quick to execute depending on how many values you're searching for).

You may need to come up with some algorithms here also that say you may only have 5 or 10 unique values on your current sheet so that you're not wasting massive processing time looping through every single row when you only have a few unique values to begin with.

The number of sheets will vary. Could be 1 or 2 up to 40. I would expect to find only a handful of duplicates in any given file. It would be at least 1 loop embedded in another, possibly 3 loops. I want to get a structure set up so I can piggy back other functionality on it.

thanks
 
Upvote 0
How many sheets would you be searching? Is the text going to be exact in everything you are searching? The first question is more for run time - while you could get something that does what you want, if it cannot execute in a reasonable time frame, its not really a good solution. I ask the second part because this seems like a simple loop through every sheet other than the current sheet with a match() formula to check for ANY matches. (this solution should be pretty quick to execute depending on how many values you're searching for).

You may need to come up with some algorithms here also that say you may only have 5 or 10 unique values on your current sheet so that you're not wasting massive processing time looping through every single row when you only have a few unique values to begin with.

The number of sheets will vary. Could be 1 or 2 up to 40. I would expect to find only a handful of duplicates in any given file. It would be at least 1 loop embedded in another, possibly 3 loops. I want to get a structure set up so I can piggy back other functionality on it.

thanks
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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