Check name against different spellings list

**dopple** · 11-26-2008, 09:50 AM

This kind of relates to my post at http://www.excelforum.com/excel-prog...ml#post2003473.

I have 2 worksheets.
Column A on both contains a forename
Column B on both contains a surname.

I have a macro which runs through all the rows on Sheet1 and checks the forename and surname against all the rows in Sheet2. If a match is found, that row is copied onto the same row on Sheet3.

The problem we have is that in 1 sheet a name might be
A B
Tommy Jones

and on the other the same person is in under
A B
Tom Jones

What would the best way be to pick out these irregularities, I.e. still have the code pick the above out as a match and copy the row to Sheet3

Sub checkAssystAgainstEdir()
    Dim iAssyst As Long, iEdir As Long, countassyst As Long, countedir As Long
    Dim assystfn As String
    Dim assystsn As String
    Dim edirfn As String
    Dim edirsn As String
    countassyst = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    countedir = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    For iAssyst = 2 To countassyst Step 1
        assystfn = Cells(iAssyst, 1)
        assystsn = Cells(iAssyst, 2)
        For iEdir = 2 To countedir Step 1
            edirfn = Sheets("Sheet2").Cells(iEdir, 3)
            edirsn = Sheets("Sheet2").Cells(iEdir, 2)
            If assystfn = edirfn And assystsn = edirsn Then
                Exit For
            End If
            If iEdir = countedir Then
                Sheets("Sheet1").Rows(iAssyst).Select
                Selection.Copy
                Sheets("Sheet3").Rows(iAssyst).Insert
            End If
        Next
    Next
    'Tidy up sheet3
    DeleteBlankRows ("Sheet3")
End Sub

Any help is greatly appreciated

**Leith Ross** · 11-26-2008, 02:50 PM

Hello dopple,

The problem I see with sorting this out is you really don't have enough information to determine if the names really are the same. You could have a Tom and Tommy who are two different people. How are deciding this or are you certain all short forms of a given name are the same?

**dopple** · 11-28-2008, 05:58 AM

I am also searching against the surname field.
So in pseudo code

If ( ( (forename1 <> forename2 ) AND ( forename1 meets no criteria in the exceptions list ) ) AND ( surname <> surname ) )
    DO whatever
END IF

**dopple** · 11-28-2008, 07:58 AM

Also I should add that these are just to throw up any possible exceptions in order to clean up our data sources so even if a few do ghet through the script, it will only be acted on after a secondary check, thus avoiding any duplication, or accidental de-duplication of two genuine people with the sme name.