Loading
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.

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

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

```

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.

I wait your reply.

Thanks in advance.

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)

#### Thread Information

##### Users Browsing this Thread

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