A starter:


Sub Transform()

Dim ws1 as worksheet, ws2 as worksheet
Dim r as long, lastrow as long
Dim ID as string

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

r = 2
lastrow = ws1.Cells(Rows.Count, "B").End(xlUp).Row
Do
ID = ws1.Cells(r, "A")
Do
ws2.Cells(r, "A") = ID
ws2.Cells(r, "B") = ws1.Cells(r, "B") & " " & ws1.Cells(r, "C")
r = r + 1
Loop Until ws1.Cells(r, "A") < "" Or r lastrow

Loop Until r lastrow

HTH

End Sub

"Vikram Dhemare" wrote:

Dear Friends,

I have got a table, like
A B C
RC1 2843768 FR LH STD
RC2 FR RH STD
RC3 RR LH STD
RC4 RR RH STD
RC5 2843769 FR LH DLX.
RC6 FR RH DLX.
RC7 RR LH DLX.
RC8 RR RH DLX.
RC9 2843770 FR LH LUX
RC10 FR RH LUX
RC11 RR LH LUX
RC12 RR RH LUX
RC13 2843771 FR LH SDLX
RC14 FR RH SDLX
RC15 RR LH SDLX
RC16 RR RH SDLX
Now in another excel file if I put the 'Col 'A' cell value (number of col
A) as input
then the result in subsequent cell would be the merge of col. B & col. C &
in the next row the answer would be 2nd row index value & so on.
like this
B
RC1 FR LH STD
RC2 FR RH STD
RC3 RR LH STD
RC4 RR RH STD
This all vlues in Pivot table, so can I access that ref.
--
Thanks,
Vikram P. Dhemare