Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Check For Repeated Numbers Across All Sheets

  1. #1
    Join Date
    14th January 2008
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

  3. #3
    Join Date
    14th January 2008
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Identify 1st Occurences Of Repeated Numbers
    By Crusaders431 in forum EXCEL HELP
    Replies: 1
    Last Post: February 20th, 2008, 06:58
  2. Replies: 2
    Last Post: May 18th, 2007, 04:13
  3. Check If TextBox Has Spaces Or Numbers
    By Joe Derr in forum EXCEL HELP
    Replies: 5
    Last Post: October 18th, 2006, 16:37
  4. Check for existence of specified numbers
    By legaltrends in forum EXCEL HELP
    Replies: 3
    Last Post: March 24th, 2006, 22:41
  5. Replies: 8
    Last Post: June 14th, 2003, 07:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno