V Lookup w/ unreliable data

[hshayh0rn]

I have 2 spreadsheets that I need to compare and then grab data from one of
them if I find a match. That's great is the data in each sheet matches but I
have to deal with two different companies inputing data on each sheet so the
data is rarely in the same format. For example:

Sheet #1
Name Title
Bob Smith AVP Marketing
Jane Smith CEO
Tim Thomas VP

Sheet #2
Name
Smith, Bob
Jame Smith
Thomas Tim

If a name from coulmn A on sheet two match the names from column A on sheet
one then I need to populate column B on sheet 2 with the person's title.
There's no way for me to control how the data is input by the companies. I
can not control frist, last or last, first or the use or non-use of a middle
initial.

Can someone please help me with some code to figure this problem out???

 

[Jim Thomlinson]

You need to convert both sheets to a common format. I might suggest first
middle and last. Then the problem becomes relatively easy. You will need to
do text manipulation functions to make it work but assuming each sheet is
reasonably consistent then you should be able to work it out. One thing I do
in these cases is to remove all of the commas, dashes, etc using the find and
replace function to clean things up a bit. If you need help with the text
functions then just reply back...

 

[hshayh0rn]

I can probably get the data so that there isn't anything extra in it but I'm
still going to have the issue where some of the cells in each of the sheets
have the first name first and others will have the last name first. How would
you find a match that way?

 

[Jim Thomlinson]

There is no easy way... How many of these do you need to match up? Are each
of the sheets consistent. That is to say on one sheet are half the records
first then last while the other half are last then first? Finally are there
any repetitions in either list or are the names unique. No matter what we do
you will not have any luck matching up where there are spelling mistakes or
differences such as Dave and David...

 

[hshayh0rn]

The number of records on the 2 sheets may not be equal. Unfortunately sheet 1
which is supposed to be the master record isn't always up to date and vise
versa. So sheet 1 may have 100+ names on it while sheet 2 may only have 95
names. Second issue, generally the data sheet (2) that contains the titles is
either going to be first then last or last then first. I haven't see where
this sheet has a combination. Now sheet 1 may have a combination of first
last and last first names on it. So, can code be written to search sheet 2
for "Jim Smith" and "Smith Jim" and if found then grab the Title from column
B on the data sheet (2)?

 

[Jim Thomlinson]

Here is some code for you to try... It is a function that looks for the name
forwards or backwards... There is more needed than just this but it is a
start...

Public Sub Test()
MsgBox FindName("John Smith", Sheet2.Columns("A"))
End Sub

Public Function FindName(ByVal strName As String, ByVal rngToSearch As
Range) As String
Dim rngFound As Range

Set rngFound = rngToSearch.Find(strName, , , xlPart)
If rngFound Is Nothing Then _
Set rngFound = rngToSearch.Find(ReverseName(strName), , , xlPart)
If rngFound Is Nothing Then
FindName = "Not Found"
Else
FindName = rngFound.Value
End If
End Function

Public Function ReverseName(ByVal strName As String) As String
Dim intReversePoint As Integer

intReversePoint = InStr(strName, " ")

ReverseName = Trim(Right(strName, Len(strName) - intReversePoint)) & " "
& _
Trim(Left(strName, intReversePoint - 1))
End Function