Nic
Nic

Reputation: 127

Transpose multiple rows into one column

I have the following table:

ID           Description
1              name1
2              name2
3              name3
4              name4

I would like to have the following

ID+name
 1
 name1
 2
 name2
 3
 name3
 4
 name4

Do you advise something in particular? Already tried some other transpose methods but I am not able to manage two rows in a time.

Thank you for your help.

Upvotes: 0

Views: 1656

Answers (3)

M_Idrees
M_Idrees

Reputation: 2172

Using VBA Macros, you can do this way:

Sub Transpose1()
    Worksheets("Sheet1").Select

    Range("C1").Value = "ID+Name"

    'Get Worksheet
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    'Find last row
    Dim LastRow As Long: LastRow = ws.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Dim i As Long, j As Long, RowCounter As Long: RowCounter = 2
    Dim DestCol As Long
    DestCol = 3

    With ws
        'loop through all rows
        For i = 2 To LastRow
            'set value in row=1,col=3, with value from row=i,col=1
            .Cells(RowCounter, DestCol) = ws.Cells(i, 1)
            RowCounter = RowCounter + 1

            'set value in row=2,col=3, with value from row=i,col=2
            .Cells(RowCounter, DestCol) = ws.Cells(i, 2)
            RowCounter = RowCounter + 1
        Next i
    End With
End Sub

Assuming that you have 2 columns ID, Description on Sheet1 on sheet's columns A and B respectively. This macro will output the desired format in C column. Please review comments in code for better understanding.

Upvotes: 1

Mrig
Mrig

Reputation: 11702

If you are willing to use formula then following might be helpful.

Enter following formula in Cell D2

=INDEX($A$2:$B$5,1+INT((ROW(A1)-1)/COLUMNS($A$2:$B$5)),MOD(ROW(A1)-1+COLUMNS($A$2:$B$5),COLUMNS($A$2:$B$5))+1)

Drag/Copy down as required. See image for reference.

enter image description here

For @Vityata :

With data in G17:H20 and formula in I10

enter image description here

With data in G18:H21 and formula in I12

enter image description here

Upvotes: 3

Rob Anthony
Rob Anthony

Reputation: 1813

You can use a formula based on OFFSET

=OFFSET($A$1,INT((ROW(A1)-1)/2),MOD(ROW(A1)-1,2))

Upvotes: 1

Related Questions