#1
|
|||
|
|||
Match names with different formats
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! |
#2
|
|||
|
|||
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.
|
#3
|
|||
|
|||
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. Code:
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 |
#4
|
|||
|
|||
Thanks guys.
I was sick so I could answer until now. But thanks for your feedback. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel - Index and Match Function ,First Second and Third Match | paulzy95 | Excel | 10 | 09-29-2016 10:46 PM |
Too many different cell formats | nishant | Excel | 1 | 02-05-2014 10:49 AM |
How to enter names in Resource Pool/names | pstein | Project | 1 | 03-26-2012 07:37 AM |
TOC formats | ruggb | Word | 3 | 03-09-2012 02:07 AM |
Template formats | ROTECH | Outlook | 0 | 07-20-2011 09:28 PM |