Merging files with nearly identical values in one column

[Allen]

I've got two files of teacher's class information that I need to merge. For
instance, in Column C includes the teachers' names in both files. however,
in FILE1 the names are listed lastname firstname (with no comma) while in
FILE2, the names are listed lastname, firstname (with comma). Of course, the
last names are not the same length from row to row. I need to get the column
D information from File1 and have it show up in Column D in File2. normally,
this would be a simple process of copying and pasting the information except
that there are some names that appear in one file and not both and some
teachers are listed on multiple rows in one file and a different number of
rows in the other file.

Any assistance would be greatly appreciated.

 

[Sheeloo]

Insert a column AFTER Column C in file1 and enter this in the new D1
=SUBSTITUTE(C1," ",", ")
and copy down...

Now in new E1 of file2 enter this
=VLOOKUP(D1,

switch to file1, select column C and D both together
then type
,2,false)
and press enter...

You can copy it down...

You will get #N/A for names not found in file 1..

You can hide that with
=IF(ISNA(Vlookup(),"",Vlookup())

Once you are happy, you can copy everything, paste special as values and
delete either Col C or Col D, as you want.

replace Vlookup() with the Vlookup you got above

 

[Allen]

Thanks for the quick response. I now have another question related to
VLookup...is it possible to run it, somewhat, in reverse. In other words, in
the same file (file2 in the previous question) I have building level course
numbers (for instance M6151, M7151 and M8151) that I need to lookup. If the
course number (in column D for instance) matches ANY THREE OF THOSE, I need
to return a common number (054800...a state-level number this time) in column
E where the forumula would be. I can setup a separate worksheet where I have
all of the state-level numbers in column A with the various building-level
numbers in the subsequent columns (some of the rows would have up to 5
building-level codes for a specific state-level code).

Thanks again.

 

[Sheeloo]

You are welcome.

It is best to set up 'building level course
numbers' in the left most column in the lookup range and state level to the
right... then it is straightforward lookup...