Excel 2007 : Columns to rows

[freehawk]

Hello. Is it possible to sort numbers from columns to single column? For example:

A1 = 3
B1 = 4
C1 = 5

A2 = 5
B2 = 6
C2 = 8

and so on.

Is it possible to do it one row so it would look like this:

A1 3
A2 4
A3 5
A4 5
A5 6
A6 8

Thanks for responses.

[Paul]

Hi Freehawk,

In a free column, let's say F1, put the following formula:

=OFFSET($A$1,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))

Copy that down as many rows as needed. (For example, if you have 20 rows of data, A1:C1, A2:C2,.. A20:C20, then you'll need to copy this formula down 60 rows to F60.)

You can then copy that range and use PasteSpecial -> Values on top of itself. Then delete columns A:C.

[darkyam]

I would suggest instead the non-volatile Index formula: =INDEX(A:C,(ROW()-1)/3+1,MOD((ROW()-1),3)+1), but if you're only doing this once, it shouldn't really matter.

[freehawk]

Thanks Guys, it works.

I will mark it as solved.

Thanks