TRANSPOSE 'group' of columns to rows
 

Excel 2003 SP2
Data is structured like this
Column
A B C D E F
123 456 789 0ab cde fgh
ijh klm nop qrs tuv wxy
I can use the TRANSPOSE copy/paste special w/ transpose but that's manual
for about 25-50 rows. Arg! I prefer not doing the copy/paste special.

When I create a TRANSPOSE function I get things to work for the 1st 6 rows
(in this example "F" is the 6th letter). Then, when I copy/paste (normally)
the TRANSPOSE of the first 6 rows, I don't get the RELATIVE GROUP that I
want. That is, I need to have the TRANSPOSE start at row 2 (A2, B2, C2, D2,
E2, F2) in Sheet1 but the copy of the cell is actually placed into Sheet2 A7
(A1 plus 6 columns). So, I'd like to create an INITIAL group of nn columns
wide (probably about 10-20ish) and then copy/paste this INITIAL group 10-20
times to get my TRANSPOSE function to work. [A1 plus nn columns but still in
Column A from Sheet1 when I paste into Sheet2]
Note: My actual TRANSPOSE function is something like this:
=IF(Sheet1!$A1<"",trim(TRANSPOSE(Sheet1!$A1)),"") for A1
=IF(Sheet1!$B1<"",trim(TRANSPOSE(Sheet1!$B1)),"") for B1 etc.....
The new TRANSPOSE should look something like this:
=IF(Sheet!$A{1+nn}<"",trim(TRANSPOSE(Sheet1!$A{1+ nn}),"") for A{1+nn} etc
TIA,

TRANSPOSE 'group' of columns to rows
 

Hazarding some thoughts.
Perhaps something along these lines ..

In Sheet2,

Place in A7:
=IF(OFFSET(Sheet1!$A$2,COLUMN(A1)-1,ROW(A1)-1)=0,"",OFFSET(Sheet1!$A$2,COLUMN(A1)-1,ROW(A1)-1))
Copy A7 across as far as required, then fill down as far as required to pull
in a dynamic transpose of Sheet1's source region with top left cell at A2.
The expression will return blanks: "" if the source region is either blank or
contain zeros or null strings.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tom" wrote:
Excel 2003 SP2
Data is structured like this
Column
A B C D E F
123 456 789 0ab cde fgh
ijh klm nop qrs tuv wxy
I can use the TRANSPOSE copy/paste special w/ transpose but that's manual
for about 25-50 rows. Arg! I prefer not doing the copy/paste special.

When I create a TRANSPOSE function I get things to work for the 1st 6 rows
(in this example "F" is the 6th letter). Then, when I copy/paste (normally)
the TRANSPOSE of the first 6 rows, I don't get the RELATIVE GROUP that I
want. That is, I need to have the TRANSPOSE start at row 2 (A2, B2, C2, D2,
E2, F2) in Sheet1 but the copy of the cell is actually placed into Sheet2 A7
(A1 plus 6 columns). So, I'd like to create an INITIAL group of nn columns
wide (probably about 10-20ish) and then copy/paste this INITIAL group 10-20
times to get my TRANSPOSE function to work. [A1 plus nn columns but still in
Column A from Sheet1 when I paste into Sheet2]
Note: My actual TRANSPOSE function is something like this:
=IF(Sheet1!$A1<"",trim(TRANSPOSE(Sheet1!$A1)),"") for A1
=IF(Sheet1!$B1<"",trim(TRANSPOSE(Sheet1!$B1)),"") for B1 etc.....
The new TRANSPOSE should look something like this:
=IF(Sheet!$A{1+nn}<"",trim(TRANSPOSE(Sheet1!$A{1+ nn}),"") for A{1+nn} etc
TIA,