Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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

    VB:
    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