Find and replace?

[iwilli]

I have a list of employee last names and and employee number both in separate columns. I have a few employees that have the same last name. Is there a way to look up certain employee numbers in one column and then replace the value of a different column. So look up the employee number then replace the last name with the first initial of their first name and last name?

 

[Legacy 221039]

I think this is the simplest approach (i.e. to replace all surnames), although I'm not sure if this is what you are looking for....

Excel 2007

	A	B	C	D	E
Emp no	Surname	Name and initial
Smith	Smith, E
Jones	Smith, S
Smith	Jones, S
Table
Smith, E
Smith, S
Jones, S

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C2[/TH]
[TD="align: left"]=VLOOKUP(A2,$D$11:$E$13,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C3[/TH]
[TD="align: left"]=VLOOKUP(A3,$D$11:$E$13,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C4[/TH]
[TD="align: left"]=VLOOKUP(A4,$D$11:$E$13,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[iwilli]

Mark-
Thanks for the reply. Not quite what I had in mind. I don't have another table with the info on it. I was hoping there was a way to have a find function search for the employee number (ei. 654987) and then replace the info two columns to the left. I appreciate the help.