Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 31st 06, 05:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2
Default 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   Report Post  
Old July 31st 06, 06:21 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default 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   Report Post  
Old July 31st 06, 09:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 695
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine multiple columns into two long columns, Repeating rows in first column [email protected] Excel Discussion (Misc queries) 0 July 31st 06 05:07 PM
Sorting Data From One Column into Multiple Columns Justin Hoffmann Excel Worksheet Functions 2 July 12th 06 04:15 PM
how to combine several columns into a single column jims Excel Discussion (Misc queries) 9 August 15th 05 12:00 PM
generate multiple rows based on cell value Theresa Excel Worksheet Functions 0 May 25th 05 11:18 PM
Convert multiple columns to rows Lois Lane Excel Worksheet Functions 8 January 10th 05 12:47 AM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017