Transposing Rows to Columns 1-12, 13-24,25-36 etc

I have imported a text file into column 1.

How do I transpose rows 1-12 to columns then 13-24 to the same columns then 25-36 etc.

Basically Convert the first 12 rows to columns then the next 12 to the same columns and continue the same down to row 600.

If I use the automatic transpose function for every row then I get a new column for every row.

So your data is all in column A and you want it so that A1:A12 goes into C1:N1, then A13:A24 goes into C2:N2, and so on?

Pete

If that is the case then you can put this formula in C1:

=INDEX($A:$A,(ROWS($1:1)-1)*12+COLUMNS($C:C))

copy it across to N1, then copy C1:N1 down as far as you need to.

Hope this helps.

Pete

Thanks Glenn for the spreadsheet

Its great but not exactly what I need, you have numbers 1-36 in columns a,b,c

I may of not described properly every row is in column A not 1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
.
.
.
35
36

I need to convert to

1 2 3 4 5 6 7 8 9 10 11 12
13 14 15 16 17 18 19 20 21 22 23 24
25 26 27 28 29 30 31 32 33 34 35 36

as per your spreadsheet

@Glenn,

All the date are in column A. Not in column A,B, and C.

Another way ...

=OFFSET($A$1,COLUMNS($A:A)-1+ROWS($1:1)*12-12,INT((ROWS($1:1-1))/12))

Thanks Phuocam It works Pertectly

Did you not try mine in Post #4? It does not use a volatile function.

Pete

Sorry Pete, yours works great as well.
The only thing that could improve both is

If the cell is blank both formulas give a zero instead of a blank.
If its blank and the formula gives a blank it would make it perfect.

Thanks
Very Much

This seems to work

=IF(INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($C:C))=0,"",INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($C:C)))

Thanks, AliGW I will change to solved, sorry new to the Forum today.

Originally Posted by mralanb

This seems to work

=IF(INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($C:C))=0,"",INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($C:C)))

Zero might be a valid number to return, so it would be safer to do this:

=IF(INDEX($A:$A,(ROWS($1:1)-1)*12+COLUMNS($C:C))="","",INDEX($A:$A,(ROWS($1:1)-1)*12+COLUMNS($C:C)))

However, if the values that you are returning are text values, then you can do this:

=INDEX($A:$A,(ROWS($1:1)-1)*12+COLUMNS($C:C))&""

If you are returning numbers then you can use this Custom Format:

General;-General;;

There are many ways to do things in Excel.

Hope this helps.

Pete

Thanks Again

You're welcome - thanks for the rep.

Pete