Transposing Array
 

Hi,

I have approx 6 years worth of data I would like to transpose.
Currently data is listed as:
A1: Jan-07 B1: Feb-07 C1: Mar-07
A2: 25-Dec-06 B2: 20-Jan-07 C2: 17-Feb-07
A3: 26-Dec-06 B3: 21-Jan-07 C3: 18-Feb-07
etc

Result I'm after is:
A1: Jan B1: 25-Dec-06 C1: 26-Dec-06
B2: Feb B2: 20-Jan-07 C2: 21-Jan-07
etc

I understand how to transpose one column but am struggling to understand how
to copy an array. Any help will be greatly appreciated.

Transposing Array
 

One easy way to get it done ..
Assuming your source data as posted is in Sheet1, with top left cell in A1
Then in any other sheet,
Put this in any startcell, say in B2:
=OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,ROWS($1:1)-1)
Copy B2 across/fill down as far as required to achieve the dynamic transpose
sought

Adapt this part (the anchor) in the OFFSET: Sheet1!$A$1
to suit where the top left cell of the source data is

Celebrate success, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rothenburg" wrote:
I have approx 6 years worth of data I would like to transpose.
Currently data is listed as:
A1: Jan-07 B1: Feb-07 C1: Mar-07
A2: 25-Dec-06 B2: 20-Jan-07 C2: 17-Feb-07
A3: 26-Dec-06 B3: 21-Jan-07 C3: 18-Feb-07
etc

Result I'm after is:
A1: Jan B1: 25-Dec-06 C1: 26-Dec-06
B2: Feb B2: 20-Jan-07 C2: 21-Jan-07
etc

I understand how to transpose one column but am struggling to understand how
to copy an array. Any help will be greatly appreciated.

Transposing Array
 

Fantastic, works perfectly. Thanks for your help and quick response.

"Max" wrote:

One easy way to get it done ..
Assuming your source data as posted is in Sheet1, with top left cell in A1
Then in any other sheet,
Put this in any startcell, say in B2:
=OFFSET(Sheet1!$A$1,COLUMNS($A:A)-1,ROWS($1:1)-1)
Copy B2 across/fill down as far as required to achieve the dynamic transpose
sought

Adapt this part (the anchor) in the OFFSET: Sheet1!$A$1
to suit where the top left cell of the source data is

Celebrate success, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rothenburg" wrote:
I have approx 6 years worth of data I would like to transpose.
Currently data is listed as:
A1: Jan-07 B1: Feb-07 C1: Mar-07
A2: 25-Dec-06 B2: 20-Jan-07 C2: 17-Feb-07
A3: 26-Dec-06 B3: 21-Jan-07 C3: 18-Feb-07
etc

Result I'm after is:
A1: Jan B1: 25-Dec-06 C1: 26-Dec-06
B2: Feb B2: 20-Jan-07 C2: 21-Jan-07
etc

I understand how to transpose one column but am struggling to understand how
to copy an array. Any help will be greatly appreciated.

Transposing Array
 

Hi,

You can't do this as stated with a formula because your output range
overlaps the input range. You can do it with copy, paste special by first
transposing it and then replacing the original data area with the transposed
data.

1. this works by simply copying the data set and using Edit, Paste Special,
Transpose.

2. Although it can't be done as stated, you can do something similar. By
using the TRANSPOSE function: suppose your original range runs from A1:C31
Select a range whose width is equal to the height of the original range and
whose height is equal to the width of the original range, in this case for
example select G1:AK3 and array enter the following formula:
=TRANSPOSE(A1:C31)

array enter means press Shift+Ctrl+Enter to enter the formula rather than
pressing Enter.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rothenburg" wrote:

Hi,

I have approx 6 years worth of data I would like to transpose.
Currently data is listed as:
A1: Jan-07 B1: Feb-07 C1: Mar-07
A2: 25-Dec-06 B2: 20-Jan-07 C2: 17-Feb-07
A3: 26-Dec-06 B3: 21-Jan-07 C3: 18-Feb-07
etc

Result I'm after is:
A1: Jan B1: 25-Dec-06 C1: 26-Dec-06
B2: Feb B2: 20-Jan-07 C2: 21-Jan-07
etc

I understand how to transpose one column but am struggling to understand how
to copy an array. Any help will be greatly appreciated.

Transposing Array
 

Good to hear. Could you spare a moment to hit the YES button in that
response? Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rothenburg" wrote in message
...
Fantastic, works perfectly. Thanks for your help and quick response.