A question about Microsoft Excel?

Question

I've got a column with thousands of entries in that I want to transpose into rows with 12 entries per row so that row 1 has the values from A1:A12, row 2 has the values from A13:A24, row 3 has A25:A36 and so on. It will take a long time if I Copy > Paste Special > Paste Value & Transpose in blocks of 12.

Is there a quicker way of achieving this such as a formula to use? I'm using Excel 2003 but could use 2007 if needs be.

Answer 1

Here's an easy way to do it.
Assume that your column of data starts in A1.
In B1, paste the formula :
=INDIRECT(ADDRESS(ROW(A1) + (COLUMN() - COLUMN($A1) - 1) * 12,COLUMN($A1)))

Copy this formula into B2:B12.

Finally, copy the set of formulas in B1:B12 into as many adjacent columns as you need (e.g. C1:C12, D1:D12, etc)

Answer 2

OK. Here is a way of doing it. Assume your data is in column A. First insert a column infront of the column with entries in it (moving the data to column B).

In this new column number it 1,2,3.... with 1 being the entry next to the first entry, 2 next to the second entry and so on.

Now go to column D. In cell D2 type in 0, D3 type in =D2+12 and copy this down column D for the amount of rows you want

In Cell E1 key in 0. In cell F1 key in =E1+1. Copy cell F1 to cells G1 to P1. This should give you a range of 0 to 11 in cells E1 to P1

In cell E2 type in this formula: =vlookup($d2+e$1,$A:$B,2,false)

Copy this formula to cells E2 to Pxxx where xxx is the amount of rows you copied the formula in cell D2 down

Answer 3

Here's one I use for putting three values in a column into a row. Values in column a, and an index number in column B:
=concatenate
(INDIRECT(CONCATENATE("a",B1))," ",
INDIRECT(CONCATENATE("a",B1+1))," ",
INDIRECT(CONCATENATE("a",B1+2)))

By creating a column B that advances 3 per row, this works. Just increase the number of concatenations to 12 (huge, but should work), and change the index column to advance 12 per row instead of three.