Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
Hi everyone! Does anyone know an excel macro that would enable me to put row data into a single column? I basically have quarterly data for 5 years with each row containing the quarterly data for each year (row). I just want to make the data into a time series. So it's something like: (ignore the dots) Year ...... Q1 ....... Q2 ....... Q3 ....... Q4 1990 ...... 2 ......... 5 ......... 3 ......... 9 1991 ...... 8 ......... 1 ......... 4 ......... 6 and I want a macro that would put the row data into a single column so that it would look like: 2 5 3 9 8 1 4 6 Any assistance would be greatly appreciated. Thanks! -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
Hi,
Assuming your data ranges from B2 to E6, and that column G is empty : Sub Macro1() Dim i As Integer Dim j As Integer j = 2 For i = 2 To 6 Range("B" & i, "E" & i).Copy Range("G" & j).Select Selection.PasteSpecial Paste:=xlAll, Transpose:=True j = j + 4 Next i Application.CutCopyMode = False Range("A1").Select End Sub HTH Cheers Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
You can do it with a formula.
In new sheet cell A1, enter: =INDEX(Sheet1!$B$2:$E$3,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1) then drag down Adjust table reference (Sheet1!$B$2:$E$3) to your needs HTH -- AP "uberathlete" a écrit dans le message de ... Hi everyone! Does anyone know an excel macro that would enable me to put row data into a single column? I basically have quarterly data for 5 years with each row containing the quarterly data for each year (row). I just want to make the data into a time series. So it's something like: (ignore the dots) Year ...... Q1 ....... Q2 ....... Q3 ....... Q4 1990 ...... 2 ......... 5 ......... 3 ......... 9 1991 ...... 8 ......... 1 ......... 4 ......... 6 and I want a macro that would put the row data into a single column so that it would look like: 2 5 3 9 8 1 4 6 Any assistance would be greatly appreciated. Thanks! -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
Ardus Petus Wrote: You can do it with a formula. In new sheet cell A1, enter: =INDEX(Sheet1!$B$2:$E$3,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1) then drag down Adjust table reference (Sheet1!$B$2:$E$3) to your needs HTH -- AP "uberathlete" a écrit dans le message de ... Hi everyone! Does anyone know an excel macro that would enable me to put row data into a single column? I basically have quarterly data for 5 years with each row containing the quarterly data for each year (row). I just want to make the data into a time series. So it's something like: (ignore the dots) Year ...... Q1 ....... Q2 ....... Q3 ....... Q4 1990 ...... 2 ......... 5 ......... 3 ......... 9 1991 ...... 8 ......... 1 ......... 4 ......... 6 and I want a macro that would put the row data into a single column so that it would look like: 2 5 3 9 8 1 4 6 Any assistance would be greatly appreciated. Thanks! -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 Thanks for replying Ardus! I tried it but it doesn't seem to work. It basically lists diagonal values in the column. So, when I drag down, the column looks like: 2 1 and so on and so forth. any suggestions? -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
Hi, Assuming your data ranges from B2 to E6, and that column G is empty : Sub Macro1() Dim i As Integer Dim j As Integer j = 2 For i = 2 To 6 Range("B" & i, "E" & i).Copy Range("G" & j).Select Selection.PasteSpecial Paste:=xlAll, Transpose:=True j = j + 4 Next i Application.CutCopyMode = False Range("A1").Select End Sub HTH -- Carim ------------------------------------------------------------------------ Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259 View this thread: http://www.excelforum.com/showthread...hreadid=531174 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
data in rows to a single column
I entered the formula in A1 of another sheet and dragged down for 8 rows and
it worked fine for me for the values in B2:E2 on Sheet1 -- Regards, Tom Ogilvy "uberathlete" wrote in message ... Ardus Petus Wrote: You can do it with a formula. In new sheet cell A1, enter: =INDEX(Sheet1!$B$2:$E$3,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1) then drag down Adjust table reference (Sheet1!$B$2:$E$3) to your needs HTH -- AP "uberathlete" a écrit dans le message de ... Hi everyone! Does anyone know an excel macro that would enable me to put row data into a single column? I basically have quarterly data for 5 years with each row containing the quarterly data for each year (row). I just want to make the data into a time series. So it's something like: (ignore the dots) Year ...... Q1 ....... Q2 ....... Q3 ....... Q4 1990 ...... 2 ......... 5 ......... 3 ......... 9 1991 ...... 8 ......... 1 ......... 4 ......... 6 and I want a macro that would put the row data into a single column so that it would look like: 2 5 3 9 8 1 4 6 Any assistance would be greatly appreciated. Thanks! -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 Thanks for replying Ardus! I tried it but it doesn't seem to work. It basically lists diagonal values in the column. So, when I drag down, the column looks like: 2 1 and so on and so forth. any suggestions? -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php...o&userid=28388 View this thread: http://www.excelforum.com/showthread...hreadid=531174 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colating multi rows of data into single rows - no to pivot tables! | Excel Worksheet Functions | |||
how to display rows of data in a single column | Excel Discussion (Misc queries) | |||
Gathering rows of data from multiple wrkbks to single column | Excel Worksheet Functions | |||
Transpose data from many horizontal rows into a single column | Excel Discussion (Misc queries) | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions |