PDA

View Full Version : SEARCH FOR DUPLICATE VALUES ACROSS NUMEROUS WORKSHEETS



stevembe
02-12-2015, 04:05 AM
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
02-12-2015, 06:59 AM
Perhaps you can utilise my flexfind tool for this? www.jkp-ads.com/officemarketplaceff-en.asp (http://www.jkp-ads.com/officemarketplaceff-en.asp)

GarysStudent
02-12-2015, 07:19 AM
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.