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.

On Aug 12, 9:32*am, Henry wrote:
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.

Not sure if "starting condition" and "desired output" have been
altered during transmission.
I trust line numbers are keyed in, not from original text. Puzzled why
there are different headers(LastName ~ Last Name)

If we "text to columns" on lines 2 to 11, delimited by space, there
are too many blank cells introduced even "treat consecutive delimiters
as one" is set.

Being so, I would try check each line across for blank cells BEFORE
the last cell, remove it thus packing each line as 2 names or 3. An
error will occur in line 5.

When this is done, set D2=A2, E2=B2, F2=C2;
highlight D2:F2, drag down

On Aug 12, 2:49*pm, "PY & Associates" wrote:
On Aug 12, 9:32*am, Henry wrote:





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.

Not sure if "starting condition" and "desired output" have been
altered during transmission.
I trust line numbers are keyed in, not from original text. Puzzled why
there are different headers(LastName ~ Last Name)

If we "text to columns" on lines 2 to 11, delimited by space, there
are too many blank cells introduced even "treat consecutive delimiters
as one" is set.

Being so, I would try check each line across for blank cells BEFORE
the last cell, remove it thus packing each line as 2 names or 3. An
error will occur in line 5.

When this is done, set D2=A2, E2=B2, F2=C2;
highlight D2:F2, drag down- Hide quoted text -

- Show quoted text -

OK, replace all char160 with char32 first