Reputation: 127
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
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
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.
With data in G17:H20
and formula in I10
With data in G18:H21
and formula in I12
Upvotes: 3
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