Convert rows to one single long column?

If I have a bunch of rows with say 5 columns each, is there an easy way to convert it to one big long column, just adding each row below the information from the previous row after being converted to a column? Copy and then Paste Special Transpose doesn't quite do it since it makes multiple columns.

You can use a formula, assuming that the data starts in A1 you can use this
for a 5 column

=OFFSET($A$1,CEILING(ROW(1:1)/5,1)-1,MOD(ROW(5:5),5))

copy down as long as needed will return

A1
B1
C1
D1
E1
A2
B2
and so on

replace A1 with the upper left most cell, when done copy and paste special
as values in place

--
Regards,

Peo Sjoblom

(No private emails please)


"Spalding" <[email protected]> wrote in
message news:[email protected]...
>
> If I have a bunch of rows with say 5 columns each, is there an easy way
> to convert it to one big long column, just adding each row below the
> information from the previous row after being converted to a column?
> Copy and then Paste Special Transpose doesn't quite do it since it
> makes multiple columns.
>
>
> --
> Spalding
> ------------------------------------------------------------------------
> Spalding's Profile:
> http://www.excelforum.com/member.php...fo&userid=8634
> View this thread: http://www.excelforum.com/showthread...hreadid=486720
>

Thank you very much Peo. It is amazing what people can do with these nested formulas.