I have data that looks something like this, in Excel 2003 spreadsheets:

Starting condition:

1 LastName FirstName MiddleName Last Name First
Name Middle Name
2 Abell Robert L.
3 Abraham Andrew E. Abraham Andrew E
4 Adelman Peter
5 Adl er David B. Adler David B
6 Ain Judy
7 Aldridge Gerald W. Aldridge Gerald W
8 Alfano Reginald Alfano Reginald
9 Allen Susan
10 Altern Richard N.
11 Altona James H.


Desired output:

1 LastName FirstName MiddleName Last Name First
Name Middle Name
2 Abell Robert L. Abell Robert
L.
3 Abraham Andrew E. Abraham Andrew E
4 Adelman Peter Adelman Peter
5 Adl er David B. Adler David B
6 Ain Judy Ain Judy
7 Aldridge Gerald W. Aldridge Gerald W
8 Alfano Reginald Alfano Reginald
9 Allen Susan Allen Susan
10 Altern Richard N. Altern
Richard N.
11 Altona James H. Altona
James H.


There are many more rows. This is data that will be sent to an outside
processing firm for further processing.
Our system is being consolidated from many older systems that were not
connected and had different data formats.
The requirement is that every row be filled so that when ultimately
processed, all occurrences of a particular name
will map to a current, active person.

Since there can be thousands of rows, it is very time-consuming to have
to cut and paste values from the columns
on the left to fill in the appropriate cells on the right. I want to
write a macro that would run through the entire range
and automate the process. For example, in the first row above, "Abell"
would be placed in the "last name" cell,
"Robert" would go in the first name" column and "L." would go in the
"Middle name" column. If there is a last name
in the "last name" column on the right, don't do any overwriting.


I'd be grateful if anyone could point me in the right direction.