Solved: Is this a Vlookup problem?

[sooty8]

Hi All

Scratching my head for a couple of hours with this.

On Sheet1 - Range B2:B400 are Surnames - Range C2:C400 are Titles
eg. Bloggs J.

On Sheet2 - Range A9:A409 I have J. Bloggs in the same cell

At the moment I am entering the data twice is it possible to just enter the data on Sheet 1 in the format shown above and the data would transfer automatically to Sheet2 in the format required.

Many Thanks

Sooty8

[jigar1276]

Hi Sooty,

on sheet2 - Range A9 write the formula:

=C2 & " " & B2

drag down this formula till all titles are coverd.

this is the simple way. there is other way of doing this is by macro too.

[sooty8]

Hi Jigar

Thanks for the help much appreciated - will have a go recording a macro and try to set it automatically

Regards

Sooty

[sooty8]

Hi Jigar

Just thought I'd let you know Recorded the macro placed it on a commandbutton works perfectly.

Many Thanks

Sooty8

[jigar1276]

Hi sooty,

I written a macro for you:


Option Explicit
Sub CpyData()
Dim i As Long, iLastRow As Long
iLastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To iLastRow
Worksheets("Sheet2").Range("A" & i + 7).Value = Worksheets("Sheet1").Range("C" & i).Value & " " & Worksheets("Sheet1").Range("B" & i).Value
Next i
End Sub