Announcement

Collapse
No announcement yet.

Check For Repeated Numbers Across All Sheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Check For Repeated Numbers Across All Sheets

    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.

  • #2
    Re: Check If A Number Is Repeated

    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

    Code:
        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
    Bill
    Tip: To avoid chasing code always use Option Explicit.

    Comment


    • #3
      Re: Check For Repeated Numbers Across All Sheets

      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.

      Comment


      • #4
        Re: Check For Repeated Numbers Across All Sheets

        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)
        Bill
        Tip: To avoid chasing code always use Option Explicit.

        Comment

        Working...
        X