Combine multiple columns into two long columns, Repeating rows in
Try this - copy code into a general module (VBE) and run the macro:
It will copy data from Sheet1 to Sheet2 starting row 1 with data assumed to
be in columns A & B.
Sub Column2Row()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long, rr As Long, c As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
rr = 0
' data starting in row 1
For r = 1 To lastrow
ncol = .Cells(r, Columns.Count).End(xlToLeft).Column
For c = 2 To ncol
rr = rr + 1
ws2.Cells(rr, "A") = .Cells(r, "A")
ws2.Cells(rr, "B") = .Cells(r, c)
Next c
Next r
End With
End Sub
HTH
" wrote:
I need help combining data in multiple columns into two long columns
with the rows in the first column repeating:
Example
Start
Column1, Column2, column3, Column4, Column5
Joe, 1000, 15000, 300, 20
Mike, 400, 5000, 37, 500
Gary, 90, 3000, 100, 66
End
Column1, Column2
Joe, 1000
Joe, 15000
Joe, 300
Joe, 20
Mike, 400
Mike, 5000
Mike, 37
...................
Thanks
|