Match names with different formats

[franz]

Hi all,

I need to match names in one list with names I another list (in separate tabs), the main problem is that those names have different formats, for example:
List 1 > Jimenez, Veronica
List 2 > Veronica Jimenez Rodriguez



Thanks in advance!

[ArviLaanemets]

The only foolproof way is add another column to List1 (the list of all name variants), where you enter/select the according name from List 2 (the list of standardized names). It is impossible to design a formula for this which is 100% right, even when there aren't similar or partly similar full names for different people. And even a single not properly determined link makes all your work wasted.

[NoSparks]

Using VBA with data as-is I find 320 name matches.
Cleaning up different space characters and double spaces in column A "Full List" gets 573 matches.

Option Explicit


Sub get_ID()

    Dim rng As Range, cel As Range, fndRng As Range
    Dim strName As String, arrName As Variant
    
    'Call CleanUpNames

Application.ScreenUpdating = False
    
With Sheets("Schedule Alerts")
    Set rng = .Range(.Cells(3, 2), .Cells(Rows.Count, 2).End(xlUp))
End With

For Each cel In rng
    arrName = Split(cel, ",")
    strName = Trim(arrName(1)) & " " & Trim(arrName(0))
    
    With Sheets("Full List").Range("A:A")
            Set fndRng = .Find(What:=strName & "*", _
                               LookIn:=xlValues, _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
            If Not fndRng Is Nothing Then
                cel.Offset(, 9).Value = fndRng.Offset(, 1).Value
            End If
    End With
Next cel

'Range("N3").Value = WorksheetFunction.CountA(Range("K3:K1820"))

Application.ScreenUpdating = True

End Sub


Private Sub CleanUpNames()
    
    Dim lr As Long, rng As Range, cel As Range
    
Application.ScreenUpdating = False

With Sheets("Full List")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = .Range("A2:A" & lr)
End With

For Each cel In rng
    cel.Value = WorksheetFunction.Trim(Replace(cel.Value, Chr(160), Chr(32)))
Next cel

Application.ScreenUpdating = True

End Sub

[franz]

Thanks guys.

I was sick so I could answer until now. But thanks for your feedback.