Transpose Function?

[rollinn95z]

I'm working with the transpose function, and I'm not sure it's what
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

 

[Roger Govier]

Hi

For a one off task on a small set of data as shown, then
Mark A1:A6>Copy>move to cell B1>Paste Special>Transpose
Mark A7:A12>Copy>move to cell B2>Paste Special>Transpose
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 B1>paste Special>Values
Delete column A

--
Regards

Roger Govier


"rollinn95z" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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]

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 B1>paste Special>Values
Delete column A

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?

 

[Roger Govier]

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" <[email protected]>
wrote in message

 

[Harlan Grove]

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.