Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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, |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
transpose data between columns, rows or cells | New Users to Excel | |||
REQ: Columns to rows or Rows to Columns | Excel Discussion (Misc queries) | |||
convert columns to rows & rows to columns | Excel Discussion (Misc queries) | |||
hidden rows & columns slow file open | Excel Discussion (Misc queries) | |||
repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) |