Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Check For Repeated Numbers Across All Sheets

1. I agreed to these rules
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.

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. Super Moderator
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

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

ActiveSheet.Name = "Index"
Resume Next
End Sub

```

3. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
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)

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

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