Check For Repeated Numbers Across All Sheets

[Ciria]

Hi everyone!

This is my first post in ozgrid.com, I sincerely hope that you may help to me with a Excel macro.

I have a Excel file with several worksheets, all of them have the same format.
Each cell contains a number with 7 figures. Each number is a traceability / tracking number, therefore each one should be different to the rest.

My doubt: is it possible to make a Excel macro capable of checking all numbers in all worksheets and detect / notice repeated numbers?.

I have found some macros that try to solve this situation but most of whom only check numbers in the same worksheet.

Thanks in advance.

P.S. By the way, sorry for my english I am trying to improve it, so if you see any mistakes you can send me a private message with your corrections, I appreciate you.

[Bill Rockenbach]

Ciria,
Welcome to ozgrid

Place this code in the Thisworkbook module and run. It will add a worksheet called "Index" with the results of dublicate numbers. I assumed one traking number per sheet in cell A1

Dim sht As Worksheet 
Dim r As Integer 
Dim LastRow As Integer 
Dim i As Integer 
Dim j As Integer 
 
On Error Goto AddIndex 
Sheets("Index").Activate 
On Error Goto 0 
Range("B1") = "Sheet Name" 
Range("C1") = "Tracking #" 
 
 
Range("A2:D65536").Clear 
r = 1 
For Each sht In Worksheets 
    If sht.Name <> "Index" Then 
        r = r + 1 
        Cells(r, 2) = sht.Name 
        Cells(r, 3) = sht.Range("A1") 
    End If 
Next sht 
 
LastRow = Range("C65536").End(xlUp).Row 
For i = 2 To LastRow 
    For j = i + 1 To LastRow 
        If Cells(i, 3) = Cells(j, 3) Then Cells(j, 4) = "Dublicate" 
    Next j 
Next i 
Exit Sub 
 
AddIndex: 
Sheets.Add 
ActiveSheet.Name = "Index" 
Resume Next 
End Sub 

[Ciria]

Hi Bill.

I have used your code and it works, but I need something more complex. With your macro I can check all "A1 Cells" with the rest.

The main problem is that my worksheets have a lot of numbers per sheet. I have attached one images, with it I try to explain you better my problem.

I think that the best way to doing this is creating a buttom in each sheet, if you press a key or left click with your mouse, the macro check all numbers of this sheet with the rest, if some coincidence exist a new worksheet appear with the repeated numbers, otherwise a message appear saying "No Numbers repeated", for example. This force you to check each sheet one per one, but I think that macro could be more easy.

Image 1: I can see tracking numbers and file format.



I don't know how I have to follow, I'm blocked.

I wait your reply.

Thanks in advance.

[Bill Rockenbach]

Are the tracking numbers always in column A and D or do other columns have tracking numbers?

What is the maximum number of tracking numbers on any individule sheet. How many in a workbook?

Are there a header for the traking number column.

Please post an actual excel workbook sample exactly hoe it is used. (not an image)