I did a search for help, but I haven't found an answer. My question can be excel/access/sql based. Here's the issue. I have two spreadsheets. One is a spreadsheet with 2 columns. Column A is just a bunch of numbers, unique numbers. Column B is a UserName associated with the number. The other spreadsheet also has two columns. One column is a name, in the format Joe A Schmo. The other column has his/her user id, schmoj (typically the first 5 letters of the last name followed by the first name initial). My task is to change all the usernames to userid's, next to the appropriate record.

So, say spreadsheet one has a record with cell A1 as 123456, with the associated name as Joe A Schmo in spreadsheet1columnB. I would need to make his name schmoj(in the other spreadsheet on row 150). Now, the main issue is that although the numbers are unique, one user can be associated to multiple numbers... in different formats! So in the database, I can see Joe Schmo, J Schmo, Schmo J, ; J ; Schmo, Joe ; Schmo, Schmo J A, and so on. I want to be able to use either an Excel Function, Access Function, SQL Query or Macro to be able to sort of match up 80 - 90% of this. That way, I can go in and only have 2 or 3k records to manually retreive instead of 20k records. Does anybody have any suggestions? Maybe something like, make a third spreadsheet, column A is spreadheet 1's first column(the number). column B is spreadsheet 1's name. Then, if there is a match of 5 consecutive letter in Spreadsheet1columnB with spreadsheet2column1(full name), populate spreadsheet3column3 with spreadsheet2column2(userid). Make sense?

I've tried separating the name into to columns, first name and last name, but since they are sometimes reversed, I can only split the 2 names into two columns and name them Name 1 and Name 2 since I can't say for sure one column is all last names. Regardless, any type of solution is much appreciated.