Help with macro to populate blank values

[Henry]

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.

 

[PY & Associates]

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

 

[PY & Associates]

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