| Author |
 Topic  |
|
|
juannim
USA
Posts |
 Posted - 05/13/2007 : 9:18:24 PM
|
Origin Version (Select Help-->About Origin): 7.5
Operating System:xp
I'm still slogging my way through scripts 101- trying to set up some data for a pivot table in excel. I have 22 columns. All are the same length, except col 22, which is only 16 rows long.
I want to take each row and expand it (in a new worksheet) so that I replicate each of the first 5 cells in the row 16 times. Then, I want to copy reach of the remaining 16 values to the right in the original column into the new spaces so that I put only one value per row with the appropriate label from column 22. I know this is convoluted. It is a mixture of numbers and text, unfortunately. Can someone have a peek at my attempt? Thanks:
[pivot]
%W=%H; // save name of active wks
win -t D; //create new wks
%Z=%H; // save its name
work -a 5;
work -f 1 2;
work -f 2 2;
work -f 3 2;
work -f 4 2;
work -f 5 2;
work -f 6 2;
work -f 7 2;//unelegant, I know
loop(ii,1,%W!wks.nrows) { //ii is initial row number
for(i=6, i<22, i++) {// i is initial column number
for(aa=1, aa<6, aa++) {
(%Z,aa,ii) = (%W,aa,ii); //copy sorting rows (ws, col, row)
};
(%Z,(5+(i-1)*21+ii),6)= (%W,22,i); //copy value label from list in col 22
(%Z,(5+(i-1)*21+ii),7)= (%W,i,ii); //copy value from col i, row ii of original ws
};
};
Edited by - juannim on 05/14/2007 11:55:24 AM |
|
|
juannim
USA
Posts |
Posted - 05/14/2007 : 11:46:35 AM
|
I found several errors with my script, but still can't make my corrected version work. I had column and row indices confused, but still can't make it work. Help?
[pivot]
%W=%H; // save name of active wks
win -t D; //create new wks
%Z=%H; // save its name
Win -a %Z;
work -a 5;
work -f 1 2;
set col(1) -dc 1;
work -f 2 2;
work -f 3 2;
set col(3) -dc 1;
work -f 4 2;
work -f 5 2;
work -f 6 2;
work -f 7 2;
set col(6) -dc 1;//unelegant, I know
loop(ii,1,%W!wks.nrows) { //ii is initial row number
for(i=6, i<22, i++) {// i is initial column number
for(aa=1, aa<6, aa++) {
%(%Z,aa, ii) = %(%W,aa,ii); //copy sorting rows (ws, col, row)
};
%(%Z,6,(5+(ii-1)*21+i))= %(%W,22,i); //copy value label from list in col 22
%(%Z,7,(5+(ii-1)*21+i))= %(%W,i,ii); //copy value from col i, row ii of original ws
};
}; |
 |
|
|
Mike Buess
USA
3037 Posts |
Posted - 05/14/2007 : 12:40:27 PM
|
Sorry, but I still don't understand what you are trying to do. Perhaps if you provided some "before" and "after" data?
I can comment on the work -f commands...
for(i=i1; ii<8; ii++) work -f ii 2; // is no faster but easier to write
Also, in the context of your script setting a column as categorical merely changes its format to text...
set col(1,3,6) -dc 1; // are redundant because cols 1,3&6 are already text
...The arguments of your FOR loops should be separated by semicolons rather than commas.
for(i=6; i<22; i++) {
for(aa=1; aa<6; aa++) {
Mike Buess
Origin WebRing Member
Edited by - Mike Buess on 05/14/2007 12:45:32 PM |
|
|
|
juannim
USA
Posts |
Posted - 05/14/2007 : 1:12:21 PM
|
| Thanks, Mike. It was the stupid semicolons. What a dingbat. Jonathan |
|
|
| |
Topic |
|
|
|
copying cells between worksheets w/loop