Convert a column to rows

QuestionEdit

I am familiar with the TRANSPOSE function to change a vertical column of data into a single row. I would like to extend this function so I can transpose a long column (10,000+) entries into multiple rows.

For example, A1 through A5 would occupy C1, D1, E1, F1, G1. A6 through A10 would occupy C2, D2, E2, F2, G2. A11 through A15 would occupy C3, D3, E3, F3, G3.

I have set up small conversions using "static" formulas, but this becomes awkward when the number of data elements (5 in my example above) changes and I need to rebuild the formulas manually.

I thought the OFFSET command might be my friend, but I don't understand how to increment the offset values automatically.

Help!

AnswerEdit

Robert,

Put this in C1. Drag fill to G1. Select C1:G1 and drag fill down the columns

=OFFSET($A$1,(ROW()-1)*5+COLUMN()-3,0)

It just calculates where the next value should be copied from. the 5 steps each row in the new location by 5. The use of the column() handles getting the interim values (row 2, 3, 4, 5) for columns D, E, F and G. (Of course column() provides input to row C as well, but just trying to explain how it works).

If you need more of an explanation, just post a followup and I will give a more detailed explanation or contact me at