I want to convert Column data into rows in excel. I have data like this-CodePudding

in one column i have data

After 1 want to add new row and add the column data into rows i want the out as

1 2 3
1 2 
1 5  6

CodePudding user response：

If you have data like this:

Just copy the data:

Then click on the Paste above and then select Paste Transpose:

Finally, your output data looks like what you are looking for:

CodePudding user response：

I'm afraid you'll need VBA for this. You can use this code on a module, make sure the active sheet is the one with the values to transpose and then execute it:

Sub custom_transpose()
Dim i As Long
Dim MyValues As Variant
Dim InitialRow As Long
Dim ThisColumn As Long
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

MyValues = Range("A1:A" & LR).Value

InitialRow = 0

For i = 1 To UBound(MyValues) Step 1
    If MyValues(i, 1) = 1 Then
        'because it's a one, we start a new row at column 2 (Column B)
        InitialRow = InitialRow   1
        ThisColumn = 2
        Cells(InitialRow, ThisColumn).Value = 1
    Else
        'we keep same row but increase column number
        ThisColumn = ThisColumn   1
        Cells(InitialRow, ThisColumn).Value = MyValues(i, 1)
    End If
Next i

Erase MyValues 'clear array
End Sub