Transposing Array




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

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

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


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:
Copy B2 across/fill down as far as required to achieve the dynamic transpose

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
Downloads:27,000 Files:200 Subscribers:70

Shane Devenshire


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

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

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:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question