Duplicate values

[szadoian]

Hi

I have an workbook that has 31 identical pages which each page refer
to one day of the month. every day my helpers enters some information
in thoes pages.

I want to have code that will help me to search my work book (the 31
Pages) and find the client numbers in the range B4:B37 that has been
duplicated, And create a summary sheet that will show the client
number, how many times the this client number has been duplicated and
the first date the client number has been entered.

Thank you for any help in advance.

 

[Guest]

This is a start

Dim aWS As Worksheet, WS As Worksheet
Dim aWSRange As Range, WSRange As Range


For Each aWS In ActiveWorkbook.Worksheets
Set aWSRange = aWS.Range("B4:B37")
For Each WS In ActiveWorkbook.Worksheets
If aWS.Name <> WS.Name Then
Set WSRange = WS.Range("B4:B37")
For Each r In aWSRange
Debug.Print WS.Name, r.Address, r.Value
If Not IsEmpty(r) Then
For Each rng In WSRange
Debug.Print aWS.Name, r.Address, r.Value, WS.Name,
rng.Address, rng.Value
If r.Value = rng.Value And Not IsEmpty(rng) Then
MsgBox ("The value " & r.Value & " exists on
worksheet " & aWS.Name & " and " & WS.Name)
End If
Next rng
End If
Next r
End If
Next WS
Next aWS

End Sub


It does compare each sheet twice and I'm sure there's a way have it check
only once, but this should get you part of the way.

 

[Earl Kiosterud]

Take a look at "Data across multiple sheets" at
http://www.smokeylake.com/excel/excel_truths.htm.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already
top-posted, the thread gets messy. When in Rome.

 

[Guest]

Here is a correction so that it only checks each WS pair once

Sub Testing()
Dim aWS As Worksheet, WS As Worksheet
Dim aWSRange As Range, WSRange As Range


For Each aWS In ActiveWorkbook.Worksheets
Set aWSRange = aWS.Range("B4:B37")
For Each WS In ActiveWorkbook.Worksheets
If aWS.Name <> WS.Name And WS.Index > aWS.Index Then
Set WSRange = WS.Range("B4:B37")
For Each r In aWSRange
Debug.Print WS.Name, r.Address, r.Value
If Not IsEmpty(r) Then
For Each rng In WSRange
Debug.Print aWS.Name, r.Address, r.Value, WS.Name,
rng.Address, rng.Value
If r.Value = rng.Value And Not IsEmpty(rng) Then
MsgBox ("The value " & r.Value & " exists on
worksheet " & aWS.Name & " and " & WS.Name)
End If
Next rng
End If
Next r
End If
Next WS
Next aWS

End Sub