help to pickout first lettet in one column and replace contents in other column with esult

I need to create a mcaro that will search through a large column of surnames(Col2) that already sorted A-Z then every time it finds the first letter of the alpha it'll replace the adjacent col1 with the first letter like if the col2,
I know the simple part which is =LEFT(B3,1) but the search part using if and isnot I am not really sure about how to start get something similar to the following resultsorry I don't know how to enter columns in this post)

Col1, . ,Col2
ID, , Name

12220001 , ,adamsS
44522522 , , jones
32511321 , ,macabe
645464 , , macdemo
2654654, , macmonagle
15651 , ,mcginty
65464 , ,smith
54566 , ,Willison
654654 , ,willlaimson

A, , adamsS
J, , jones
M, , macabe
645464, , macdemo
2654654, , macmonagle
15651, , mcginty
S, , smith
W, , Willison
654654, , willlaimson



any help would be helpful
thanks in advance

Different method...
No need to be sorted, no loop

Please Login or Register  to view this content.

many thanks

loved the code, changed a little to add right side cell:wish i new what was going on though so i could add bold to this as well just for the changed cells only:

Sub test()
With Range("b1", Range("b" & Rows.Count).End(xlUp))

.Offset(, -1).Value = Evaluate("if(countif(offset(" & .Address & ",,,row(1:" & .Rows.Count & _
")),left(" & .Address & ",1)&""*"")=1,upper(left(" & .Address & ",1))," & .Offset(, -1).Address & ")")
Application.CutCopyMode = False
End With
Columns("A:A").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlRight
End With
End Sub

any ideas on how the above works or how to bold would be good ...thanks in advance

Try this for you data.

Please Login or Register  to view this content.

Thanks, the code bolds just fine but it does the whole column, not just the changed cells like I would like.
I guess .Font.Bold = True would need ot be inserted in the code somewhere else as the changes were being made, I ttried a few different places, but had no luck as I don't know how the code works to well, many thanks for your effort though

anybody have any suggestions on the bolding issue as above

Originally Posted by gint32

Thanks, the code bolds just fine but it does the whole column, not just the changed cells like I would like.
I guess .Font.Bold = True would need ot be inserted in the code somewhere else as the changes were being made, I ttried a few different places, but had no luck as I don't know how the code works to well, many thanks for your effort though

Thanks, the code above does boldout just fine but it does the whole column, not just the changed cells like I would like I had asked.
I guess .Font.Bold = True would need to be possibly inserted in the actual code part somewhere else right as the changes were being made, I tried to insert the command in a few different places, but I had no luck with this as I don't know how the VBA example above works well enough to ammend, many thanks for your effort though