Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Sorting Data From One Column into Multiple Columns | Excel Worksheet Functions | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
Convert multiple columns to rows | Excel Worksheet Functions |