Comparing names in XL 2010 worksheet

In the attached worksheet I have 2 lists of names, one with last names and another field with the names of people who have been trained. The last name field only gives the last name but the trained field gives first initial and last name. Is there some way I can find out who has been trained by finding which last names listed in the trained column are contained in the name column of the same sheet even though the data does not match exactly? I tried conditional format but each one I tried I got a dialog box telling me I couldn't run it on a group of cells.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Put this code in the worksheet. I didn't know how you wanted to indicate that the person was trained so I just put a checkmark in column C. You probably realize this but you'll get possibly incorrect results if you have both a John Jones and a Jane Jones and only one of them is trained.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngRowB As Long
Dim lngRowD As Long
Dim lngLastRowB As Long
Dim lngLastRowD As Long
Dim bFound As Boolean

Application.EnableEvents = False
lngLastRowB = Range("B1048576").End(xlUp).Row
lngLastRowD = Range("D1048576").End(xlUp).Row

For lngRowB = 4 To lngLastRowB
    bFound = False
    For lngRowD = 4 To lngLastRowD
        If InStr(1, LCase(Cells(lngRowD, "D")), LCase(Cells(lngRowB, "B"))) > 0 Then
            Cells(lngRowB, "C").Font.Name = "Wingdings 2"
            Cells(lngRowB, "C") = "P" ' This is a checkmark in the Wingding 2's font
            bFound = True
            Exit For
        End If
    If Not bFound Then
        Cells(lngRowB, "C").Font.Name = "Calibri"
        Cells(lngRowB, "C") = ""
    End If

Application.EnableEvents = True

End Sub

Open in new window

Rob HensonFinance AnalystCommented:
or with formulae, in column E:

In column C:

Copy down as required.

Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contrainAuthor Commented:
The Expert's solution gave me exactly what I needed, and was easy to follow and emplement.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.