Automatic name change

[bugalabuga]

I get data from several sources everyday, and input the data into my workbook. Each data source has a list of names and with this list of names comes a common difference everyday. For example:

Site 1 data: Jim Beam
Site 2 data: Jim Beam Jr.
Site 3 data: Jim Beam Jr

This occurs with several names and the theme is common. What would be the best way to automatically change these names to a common name (such as Jim Beam Jr) so that all the names match up (as I pull data from each source which gets entered into separate sheets within the workbook and each sheet is referenced from the Master sheet)?

It would save me a great deal of time if I had something built in to automatically change these names...since I know which names I have to change everyday.

 

[RatExcel]

Try this:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

This will find seconf space in the string and extract only what's on the left of it - so just firstname & lastname. Is it OK for you?

 

[bugalabuga]

Try this:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

This will find seconf space in the string and extract only what's on the left of it - so just firstname & lastname. Is it OK for you?

That would work for that situation, but the problem is I have other scenarios with names...such as:

Site 1 data: Bradley Smith
Site 2 data: Brad Smith

Site 1 data: John (James) Doe
Site 2 data: John Doe

I am trying to find a way to build in all the corrections I know will need to be made so I don't have to do it every day I load the data. Does that make sense?

Thank you for the suggestion...

 

[RatExcel]

How many of such exceptions do you have?

 

[bugalabuga]

How many of such exceptions do you have?

Probably 7-10 different names appearing differently in the data sets (and they can vary from 1-2 differences)

 

[RatExcel]

The only thing I can think of right now is to apply simple sub with all exceptions hardcoded in the code:

Sub T()

mCol = "A"

For i = 2 To Cells(Rows.Count, mCol).End(xlUp).Row
    If Cells(i, mCol) = "Bradley Smith" Then
        Cells(i, mCol) = "Brad Smith"
    ElseIf Cells(i, mCol) = "John (James) Doe" Then
        Cells(i, mCol) = "John Doe"
    'ElseIf...
    End If
Next i

End Sub

I assumed all your names are in column A and they start in row 2.

 

[bugalabuga]

The only thing I can think of right now is to apply simple sub with all exceptions hardcoded in the code:

Sub T()

mCol = "A"

For i = 2 To Cells(Rows.Count, mCol).End(xlUp).Row
    If Cells(i, mCol) = "Bradley Smith" Then
        Cells(i, mCol) = "Brad Smith"
    ElseIf Cells(i, mCol) = "John (James) Doe" Then
        Cells(i, mCol) = "John Doe"
    'ElseIf...
    End If
Next i

End Sub

I assumed all your names are in column A and they start in row 2.

It's more than just Column A, but I will give this a shot. This looks like a good long term solution.