Hi, I've got a problem.
I was given a data which look like this:(it's a dummy data)

KLM 130205 1.30
KLM 140205 1.29
SDF 130205 0.90
SDF 140205 1.20
SDF 150205 1.29
FGH 130205 2.50
FGH 140205 2.30

and so on.. there will always be 3letter word for the company name. The 2nd column is the date and the 3rd is the share price.

Now, I need a macro to do this:

KLM 130205 1.30 SDF 130205 0.90 FGH 130205 2.50
KLM 140205 1.29 SDF 140205 1.20 FGH 140205 2.30
                          SDF 150205 1.29

Please could anyone help me with this? I'm stuck!
Thanks in advance.

Hi

Try this one

Sub Transpose()
Dim Col As Byte
Dim RowNr As Long, l As Long
Col = 5
'Change l = 2 if you want to start in row 2...
l = 1
Do Until IsEmpty(Cells(l, 1))
    RowNr = 1
    Do
        Cells(RowNr, Col) = Cells(l, 1)
        Cells(RowNr, Col + 1) = Cells(l, 2)
        Cells(RowNr, Col + 2) = Cells(l, 3)
        RowNr = RowNr + 1
        l = l + 1
    Loop While Cells(l, 1) = Cells(l - 1, 1)
    Col = Col + 3
Loop
End Sub

-vemaju

hi, it works great!
But I want to copy it to another workbook, can you tell me how?
thanks in advance

Hi
This one creates a new workbook and transposes table to new one

Sub Transpose()
Dim Col As Byte
Dim RowNr As Long, l As Long
Dim Book As Object
Workbooks.Add
Set Book = ActiveWorkbook.Sheets(1)
ThisWorkbook.Activate
Col = 1
l = 1
Do Until IsEmpty(Cells(l, 1))
    RowNr = 1
    Do
        Book.Cells(RowNr, Col) = Cells(l, 1)
        Book.Cells(RowNr, Col + 1) = Cells(l, 2)
        Book.Cells(RowNr, Col + 2) = Cells(l, 3)
        RowNr = RowNr + 1
        l = l + 1
    Loop While Cells(l, 1) = Cells(l - 1, 1)
    Col = Col + 3
Loop
End Sub

-vemaju

thanks vemaju.