asked on

Excel 2010: finding duplicates in child sheets and highlighting them in master sheet

I have an excel 2010 workbook (FMG All Good Emails_250K_Apportioned _MB-July16-14.xlsm) comprised of tens of thousands of records and about 20 sheets.

On the master sheet (FMG All Good Emails_July_Apport) there are approx 200,000 records (rows). All the other sheets are approximately 10,000 each and each one of them contain copies of records from the master sheet.

I need a function that highlights the entire row in the master sheet when the email address from that master sheet is found in one of the 'children sheets' (sheets other than the master) and I need the name of the tab inserted in every row in Col O of the master sheet.

For example. I have a sheet named Trevor Turner. The function would find all email addresses in sheet Trevor Turner and locate them in the master sheet (FMG All Good Emails_July_Apport). When a match is found in the master sheet the function would highlight the entire row with a unique color AND put the name Trevor Turner in every row in Col O.

I have the following children sheet:
Claudia Lynch
Darin Swall
David Fields
Dino Restrepo
Fred Schwarz
John Winans
Josh Landis
Larry Demaree
Leandra Hill
Marc Rogers
Mike Fiore
Mike Gordon
Michael Strayhorn
Paul Heffernan
Scott E. Naughton
Steve Katcher
Brooks Boucher
Dan Pederson
Chad Dokken
Trevor Turner
Drew Dokken

Having this would be a great, great help to find who has what in the master list.
If there are duplications (an email found in more than one child list) then that would
be good to note somewhere as well if no match can be found.

Ejgil Hedegaard

Try this.
Insert in a module, change the column number in the line
Const colMail As Integer = 3
and run.

Option Explicit

Sub FindEmailMatch()
Dim ws As Worksheet, wsMaster As Worksheet, rw As Long
Dim MailSearch As Variant, MailSheetsMatch As String

'Change below to the column number with the e-mails on the master sheet
Const colMail As Integer = 3
    
    Set wsMaster = ThisWorkbook.Worksheets("FMG All Good Emails_July_Apport")
    wsMaster.Cells.Interior.Color = xlNone
    For rw = 2 To wsMaster.UsedRange.Rows.Count
        MailSheetsMatch = ""
        For Each ws In ThisWorkbook.Worksheets
            If Len(wsMaster.Cells(rw, colMail)) > 0 Then
                If ws.Name <> wsMaster.Name Then
                    Set MailSearch = ws.Cells.Find(What:=wsMaster.Cells(rw, colMail))
                    If Not MailSearch Is Nothing Then
                        If Len(MailSheetsMatch) = 0 Then
                            MailSheetsMatch = ws.Name
                        Else
                            MailSheetsMatch = MailSheetsMatch + ", " + ws.Name
                        End If
                    End If
                End If
            End If
        Next ws
        If MailSheetsMatch = "" Then
            wsMaster.Range(("O" & rw)) = "No Match"
        Else
            wsMaster.Range(("O" & rw)) = MailSheetsMatch
            wsMaster.Rows(rw).EntireRow.Interior.Color = 65535
        End If
    Next rw
End Sub