I hope the following macro may help

Because of the logic in your question, your list should contain at least 3 rows of names

To use the macro, you just have to have any one of the cells in your list selected

'--------------------------------------------
Sub move_names(
Dim tmp As Singl
With Selection.CurrentRegion.Columns(1
If .Cells.Count = 3 The
For tmp = .Cells.Count To 3 Step -
If Asc(Left(.Cells(tmp - 1).Value, 1)) Asc(Left(.Cells(tmp).Value, 1)) The
.Cells(tmp - 2).Value = .Cells(tmp - 2).Value & " " & .Cells(tmp - 1).Valu
.Cells(tmp - 1).EntireRow.Delet
End I
Nex
End I
End Wit
End Su
'--------------------------------------------

Regards
Edwin Ta

http://www.vonixx.com

----- drjohnwilliams wrote: ----

I have a spreadsheet of surnames in alphabetical order. As these wer
OCR'd some of the entries got split into two cells e.g. This would be
typical list

Adams John Jone
Rober
Adams Michae
Adams Albert

The split is random so one can't select every nth cel

As this is over 35K rows long. I am trying to move those split name
such as Robert to an adjacent cell to the right of the correct name. S
after processing it would look like
Adams John Jones Rober
Adams Michael

I thought the best way to do this would be to start at the top of th
column, read the first character in the cell e.g. A, (Adams John Jones
go down a line and read the next first character R (Robert)and compare
If the two characters are the same then I would compare the second on
R (Robert) with the next one down; A (Adams Michael). If they wer
different then I would move the second entry Robert up and across on
row. Go back to the column and down a row to avoid the now blank cel
and compare two cells again until the end is reached.

I am having real problems in figusring out how to select just the firs
character in a cell and then compare it with another.
Thank

joh


--
Message posted from http://www.ExcelForum.com