I am hoping that someone can help me with this excel issue. I have two sheets in a excel workbook, one with a list of name of individuals participating in one program, the other with a list of names of individuals participating in a different program. The challenge is that each program is recording names differently. For example, the first program records names as: Doe, John A and the other Doe, John Anthony. In both lists they are recording date of birth in the same fashion, i.e. However, I do have concerns that it is possible that a clerical error could have occurred at the time of the data entry and that this could be incorrect and since there are hundreds of entries, it is likely that multiple people have the same birth date.
Microsoft.com The Fuzzy Lookup Add-In for Excel was developed by Microsoft Research and performs fuzzy matching of textual data in Microsoft Excel. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables.
Both list use an ID number but the format of these have nothing in common with one another. I am hoping to combine these two lists as the person the individual is working with in each program is listed in each list and would like to create a master list of those who are in both programs and who they are working with.
I have created samples of each list below. To this point, I have tried using vlookup to accomplish this as well as the fuzzy lookup add-in, but when I review the information, there are clearly individuals that are being missed that I would think would be found. Any suggests you might have would be greatly appreciated. SheetA Name ID# DOB Agent Doe, John A X47875 Todd Smith Doe, Jane B. C25369 Tom Hass Smith, Donna J F78965 5/11/1965 Todd Smith SheetB Name ID# DOB Officer Doe, John Anthony 34567 Chad Towns Doe, Jane Betrice 98765 Steven Young Smith, Donna Jane 23678 5/11/1965 Tom Doe Thank you in advance for assistance! You didn't provide any column letters or row numbers, so I am going to assume your data looks like this: A B C D 1 Name ID# DOB Agent 2 Doe, John A X47875 Todd Smith 3 Doe, Jane B. C25369 Tom Hass 4 Smith, Donna J F78965 5/11/1965 Todd Smith Based on that assumption.
1 - Insert a Helper Column at Column A. 2 - Enter this formula in the new A2: =IFERROR(LEFT(B2,FIND(' ',B2,FIND(' ',B2)+1)+1),B2) 3 - Drag this down as far as you need. For names with a middle initial or middle name, you should get the name but only the middle initial. For Doe, John Anthony or Doe, John A the formula will return Doe, John A For names that have no middle initial or middle name, you should get the existing name. For Doe, John the formula will return Doe, John Now you should have matching names in Column A and you can apply a standard VLOOKUP against that column. I.e both lists will contain Doe, John A Granted, there are probably formats of names for which this formula may not work. Message edited by DerbyDad03.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |