data in rows to a single column

[uberathlete]

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!

[Ardus Petus]

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" <[email protected]> a
écrit dans le message de
news:[email protected]...
>
> 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
>

[uberathlete]

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" <[email protected]> a
écrit dans le message de
news:[email protected]...
>
> 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?

[Carim]

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

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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"
> > <[email protected]> a
> > écrit dans le message de
> > news:[email protected]...
> > >
> > > 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
>

[Carim]

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