I'm working with the transpose function, and I'm not sure it's what I
really need. I have a column of data like:

A1 1
A2 2
A3 3
A4 4
A5 5
A6 6
A7 7
A8 8
A9 9
A10 10
A11 11
A12 12

Without manually coding, I need the information to transpose into:

a b c d e f
1 1 2 3 4 5 6
2 7 8 9 10 11 12

Any suggestions?

Thanks.


--
rollinn95z
------------------------------------------------------------------------
rollinn95z's Profile: http://www.excelforum.com/member.php...o&userid=33571
View this thread: http://www.excelforum.com/showthread...hreadid=545162

Hi

For a one off task on a small set of data as shown, then
Mark A1:A6Copymove to cell B1Paste SpecialTranspose
Mark A7:A12Copymove to cell B2Paste SpecialTranspose
Delete column A

For a formula solution dealing with a larger set of data, enter the
following in cell B1
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
copy across through cells C1:G1
copy B1:G1 down through cells B2 for as many rows as you wish (or until
you see a row of zero's appearing)
To "fix" the transformation, copy B1:Gxxx where xxx is the row number
you have gone down to.
Move cursor to cell B1paste SpecialValues
Delete column A

--
Regards

Roger Govier


"rollinn95z"
wrote in message
...

I'm working with the transpose function, and I'm not sure it's what I
really need. I have a column of data like:

A1 1
A2 2
A3 3
A4 4
A5 5
A6 6
A7 7
A8 8
A9 9
A10 10
A11 11
A12 12

Without manually coding, I need the information to transpose into:

a b c d e f
1 1 2 3 4 5 6
2 7 8 9 10 11 12

Any suggestions?

Thanks.


--
rollinn95z
------------------------------------------------------------------------
rollinn95z's Profile:
http://www.excelforum.com/member.php...o&userid=33571
View this thread:
http://www.excelforum.com/showthread...hreadid=545162

You may use the TRANSPOSE function, inserting it in all the cells as the same
array formula.
To do so, select the range A1:F1, write the formula
=TRANSPOSE(Sheet1!A1:A6)
And enter it with CRTL+SHIFT+ENTER. On the A2:F2 the formula should be
=TRANSPOSE(Sheet1!A7:A12)
I have used Sheet1 here as the ranges are overlaping.
In order to get all the different results, all the cells must be highlighted
when entering the array formula.

Hope this helps,
Miguel.

"rollinn95z" wrote:


I'm working with the transpose function, and I'm not sure it's what I
really need. I have a column of data like:

A1 1
A2 2
A3 3
A4 4
A5 5
A6 6
A7 7
A8 8
A9 9
A10 10
A11 11
A12 12

Without manually coding, I need the information to transpose into:

a b c d e f
1 1 2 3 4 5 6
2 7 8 9 10 11 12

Any suggestions?

Thanks.


--
rollinn95z
------------------------------------------------------------------------
rollinn95z's Profile: http://www.excelforum.com/member.php...o&userid=33571
View this thread: http://www.excelforum.com/showthread...hreadid=545162

Roger Govier Wrote:
Hi
For a formula solution dealing with a larger set of data, enter the
following in cell B1
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
copy across through cells C1:G1
copy B1:G1 down through cells B2 for as many rows as you wish (or
until
you see a row of zero's appearing)
To "fix" the transformation, copy B1:Gxxx where xxx is the row number
you have gone down to.
Move cursor to cell B1paste SpecialValues
Delete column A

--
Regards

Roger Govier



That formula seemed to do the trick. However, if I use data that
doesn't start in cell A1, the coding doesn't seem to work properly.
Comments?


--
rollinn95z
------------------------------------------------------------------------
rollinn95z's Profile: http://www.excelforum.com/member.php...o&userid=33571
View this thread: http://www.excelforum.com/showthread...hreadid=545162

Hi

=OFFSET($A1,row,column)
In all cases the column offset is 0 from columns A, as all values
required are in column A.

But, as we drag the formula across the page, we want to take each
successive row value down column A so the formula posted was
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
The $ before the A fixes it to be column A, the final 0 in the formula
ensuring that we do not offset by any columns from the starting column.

This formula is entered in B1 so COLUMN() = 2 and ROW() = 1
So the first offset from $A1 is (2-2)+(1-1)*5 which = 0 rows from A1 and
0 columns from A1 so it is the value in A1

As the column number goes up, we get (3-2)+(1-1)*5 which equals 1 then
2, then 3 reaching a value of 5 when you get to column G.
As the formula is then copied down to the next row, row 2, the formula
in cell B2 evaluates to an offset of
(2-2)+(2-1)*5 which equals 5, but now the offset is from cell $A2, as
the row number was left relative in the formula, and not absolute as the
column reference was made.
So it picks up the value that is 5 rows below A2, which is the value in
A7.

Hopefully, from the above, you can work out what you need the values to
be within the formula, if you are starting from a location other than
A1.

--
Regards

Roger Govier


"rollinn95z"
wrote in message
...

Roger Govier Wrote:
Hi
For a formula solution dealing with a larger set of data, enter the
following in cell B1
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
copy across through cells C1:G1
copy B1:G1 down through cells B2 for as many rows as you wish (or
until
you see a row of zero's appearing)
To "fix" the transformation, copy B1:Gxxx where xxx is the row
number
you have gone down to.
Move cursor to cell B1paste SpecialValues
Delete column A

--
Regards

Roger Govier



That formula seemed to do the trick. However, if I use data that
doesn't start in cell A1, the coding doesn't seem to work properly.
Comments?


--
rollinn95z
------------------------------------------------------------------------
rollinn95z's Profile:
http://www.excelforum.com/member.php...o&userid=33571
View this thread:
http://www.excelforum.com/showthread...hreadid=545162

Roger Govier wrote...
....
For a formula solution dealing with a larger set of data, enter the
following in cell B1
=OFFSET($A1,COLUMN()-2+(ROW()-1)*5,0)
....

OFFSET is volatile, and it doesn't provide clear advantages over INDEX
for this. Also, problems can occur (not in this case, but more
generally) using COLUMN or ROW inside OFFSET.

Here's a nonvolatile alternative that's also avoids hardcoding the
source location. If the top-left result cell were E3,

E3:
=INDEX(Source,(ROWS(E$3:E3)-1)*6+COLUMNS($E3:E3))

Fill E3 down into E4, then select E3:E4 and fill right into F3:J4.