Combine multiple columns into two long columns, Repeating rows in first column
 

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

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

Combine multiple columns into two long columns, Repeating rows
 

another way
names:
=OFFSET(A$1,ROUNDUP(CELL("row",A1)/4,0),0)
numbers:
=OFFSET($A$1,ROUNDUP(CELL("row",A1)/4,0),IF(MOD(CELL("row",A1),4)=0,4,MOD(CELL("row",A 1),4)))