The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
 Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum
 Origin Forum
 Apply formula to multiple columns		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

leonardo.a027

Argentina
9 Posts
Posted - 09/09/2016 :  02:12:35 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Hi!
I have been trying this for a long time now.
I have a lot of data in diferent workbooks in a folder. I have to multiply one column of each workbook by another column (reference column) then the result to be saved in a new column at each worksheet.
I need to make automatic for the workbooks because I have a lot of data, so doing it one a time is not an option.
I will really apreciate the help.
Thank!
P.D: I am using Origin 2016.

yuki_wu

896 Posts
Posted - 09/09/2016 :  04:48:23 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

Not sure if I understand your question fully.

Let me take a simple example to illustrate:
There are some workbooks in a folder and some worksheets in every workbook. In each worksheet, there are three columns: col(A), col(B) and col(C).

Now we want to calculate col(A)*col(C) for every worksheets and put the result in a new created column col(D), we can run the scripts below in Script Window:

// Loop over all Workbooks ...
// Restricted to the current Project Explorer Folder View
doc -ef W 
{
   // Loop over all worksheets in each workbook
   loop(ii,1,page.nlayers) 
   {
      range rW = [%H]$(ii)!;
      rW.addCol(Result);
      newColIndex = rw.nCols;
      range rA = [%H]$(ii)!col(1);
      range rB = [%H]$(ii)!col(3);
      range rR = [%H]$(ii)!col($(newColIndex));
      rR = rA * rB;
   } 
}

If you want to know more about how to loop Origin object, you can refer to:
http://www.originlab.com/doc/LabTalk/guide/Looping-Over-objs

Or you can try X-function in this case:

doc -ef W
{
	doc -e LW
	{
		vmathtool -r 2 ix1:=1 operator:=mul operand:=data ix2:=3 ox:=<new>;
	}
}


More details about vmathtool can be found here:
http://www.originlab.com/doc/X-Function/ref/vmathtool

Hope it helps!

Regard,
Yuki
OriginLab
Edited by - yuki_wu on 09/09/2016 04:53:01 AM
Go to Top of Page

leonardo.a027

Argentina
9 Posts
Posted - 09/09/2016 :  1:46:39 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi yuki.
Thanks a lot for the help. You almost got the idea. The only diference is that all comun(B) of the worksheets in the folder have to be multiplied by only one column of only one worksheet (the reference data). I can put the reference data in another folder if it helps somehow.
Go to Top of Page

snowli

USA
1412 Posts
Posted - 09/09/2016 :  2:43:40 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello,

You can run this

range reff=[book1]sheet1!A; //suppose this is where reference column is
doc -ef W
{
loop(ii,1,page.nlayers)
{
col(B)=col(B)*reff;
}
}


If you want to exclude calculation in reference book, you can lock the workbook, or simple highlight all data there and choose Format Cells. Check Disable Editing.

BTW, if the data are not imported into each sheet yet, you can choose File: Import: Import Multiple ASCII
In the dialog that opens, there is a Script section to choose what script to run after each import

You can add code such as
range reff=[book1]sheet1!A;
col(B)=col(B)*reff;

So after each import, the calculation is done on col(B)

Thanks, Snow
Go to Top of Page

leonardo.a027

Argentina
9 Posts
Posted - 09/09/2016 :  3:25:42 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Thanks a lot for the help!!
It works perfectly!
Problem solved!!
Go to Top of Page

jmenges

Germany
1 Posts
Posted - 09/11/2018 :  2:12:39 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello Pros,

I have a similar problem as leonardo. I have one sheet in my project with 400 xyxy columns and i want to do a two-dimensional frequency count for a single range of intensity values ( like 10000 to 20000 on x and 0 to 10000 on y) and only need the total count of data pairs in this regime. I tried these two variances in Origin 2018 Pro:
// Loop over all columns ...
// Restricted to the current Project Explorer Folder View
doc -ef W
{
loop(ii,1,wks.ncols)
{
range rA = [%H]sheet1!col(1);
range rB = [%H]sheet1!col(2);
range rR = [%H]sheet2!col($(newColIndex));
rR = (rA>10000)*(rA<20000) * (rB>0)*(rB<10000);
}
}
// Loop over all columns ...
// Restricted to the current Project Explorer Folder View
doc -ef W
{
int ncols = wks.ncols - 1;
loop(icol,1,wks.ncols)
for(int icol = 1; icol <= ncol; icol++)
{

range rA = [%H]sheet1!col($(icol));
range rB = [%H]sheet1!col($(icol+1));
range rR = [%H]sheet2!col($(icol));
rR = (rA>10000)*(rA<20000) * (rB>0)*(rB<10000);
}
}
but it´s not doing what i want yet only one column at a time and i would still need only half of the values bacause i need only the xy pair count... and further the total amount of every count.
As I am literally a newby in this field, I really would appreciate your help!!!
Thanks in advance,
jmenges

SingleMolecule

Edit: I found the first answer myself, thanks anyway
// Loop over all columns ...
// Restricted to the current Project Explorer Folder View
doc -ef W
{
for(icol = 2; icol <= wks.ncols; icol = icol + 2)
{
range rB = [Book1]sheet1!col($(icol));
range rA = [Book1]sheet1!col($(icol-1));
range rR = [Book1]sheet2!col($(icol / 2));
rR = (rA>10000)*(rA<20000) * (rB>0)*(rB<10000);
}
}
Edited by - jmenges on 09/13/2018 04:58:14 AM
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000