Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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??? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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??? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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??? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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??? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
V Lookup w/ unreliable data
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 -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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" wrote: 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... -- HTH... Jim Thomlinson "hshayh0rn" wrote: 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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
Is Excel unreliable ? | New Users to Excel | |||
Scrolling in Excel unreliable | Excel Discussion (Misc queries) | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |