Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-26-2018, 12:20 PM
franz franz is offline Match names with different formats Windows 10 Match names with different formats Office 2013
Novice
Match names with different formats
 
Join Date: Apr 2018
Posts: 5
franz is on a distinguished road
Default 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!
Attached Files
File Type: xlsx Schedule Alerts.xlsx (190.4 KB, 7 views)
Reply With Quote
  #2  
Old 05-27-2018, 10:26 AM
ArviLaanemets ArviLaanemets is offline Match names with different formats Windows 8 Match names with different formats Office 2016
Expert
 
Join Date: May 2017
Posts: 572
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

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.
Reply With Quote
  #3  
Old 05-28-2018, 08:13 AM
NoSparks NoSparks is offline Match names with different formats Windows 7 64bit Match names with different formats Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 779
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

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
Reply With Quote
  #4  
Old 07-21-2018, 11:41 AM
franz franz is offline Match names with different formats Windows 10 Match names with different formats Office 2013
Novice
Match names with different formats
 
Join Date: Apr 2018
Posts: 5
franz is on a distinguished road
Default

Thanks guys.

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

Thread Tools
Display Modes


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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft