SEARCH FOR DUPLICATE VALUES ACROSS NUMEROUS WORKSHEETS

[stevembe]

Thanks for your time reading this post, hopefully somebody can help.

I have a workbook that has 12 worksheets each containing different data, different column names etc so a VLOOKUP will not work with what I am trying to achieve. The one common field that is repeated in worksheets is employee number although always in a different column. Can anybody advise if there is a way to search across the entire workbook and find where an employee number is contained in more than one worksheet but noting that in some worksheets the employee number is repeated so it has to be a search across individual worksheets i.e. not return where the name is repeated in a single worksheet? It would also be useful if the names of the worksheets could be returned as there are 20 of them so perhaps some VBA.

Thank you in advance.

[Jan Karel Pieterse]

Perhaps you can utilise my flexfind tool for this? www.jkp-ads.com/officemarketplaceff-en.asp

[GarysStudent]

Click on any cell in any sheet and run this small macro:

Sub FindValues()
    Dim v As Variant, sh As Worksheet, msg As String
    v = ActiveCell.Text
    msg = v & vbCrLf & vbCrLf
    If v <> "" Then
        For Each sh In Sheets
            For Each r In sh.UsedRange
                If r.Text = v Then
                    msg = msg & vbCrLf & sh.Name & vbTab & r.Address(0, 0)
                End If
            Next r
        Next sh
        MsgBox msg
        Exit Sub
    End If
    MsgBox "I won't find empties"
End Sub

It will produce a report of all the cells in all the sheets in which the value was found. Included in the report will be the activecell you chose.