"Task description Excel file book1.xlsx stores the stock information as shown below: [p .."

blackduckie RaqForum 28 No.
1 Reply • 380 View • 3 Years ago

How to Locate the Nearest Row of the Same Catagory

excel(115) clipboard(13) locate calculation(2)

Task description

Excel file book1.xlsx stores the stock information as shown below:

The task is to calculate the values of column Cost for Sales. The rule is: If Action is Bought, there’s no need to get the target value; if it is Sold, find the same type of Stock as the nearest Action whose value is Bought and multiply its Dynamic Cost by the current Shares to get the target value.

Directions:

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Write a script in esProc:

List the code separately for an easy viewing:

	A
1	=clipboard().import@t()
2	=A1.run(if(Action=="Sold",(k=~[:-1].pselect@z(Stock==A1.Stock && Action=="Bought"),'Cost for Sales'=A1(k).'Dynamic Cost'*Shares)))
3	=clipboard(A2.field("Cost for Sales").export())

A1 Import data from clipboard; @t option enables reading the first row as column headers.

A2 Loop through each row of A1. If the current Action is Sold, search backward to get the sequence number of the row with the same Stock that satisfies Action=Bought, and assign the sequence number to variable k. Then the Cost for Sales in the current row is the product of Dynamic Cost in the k^th row and the current Shares. Expression ~[:-1] represents rows from the first one in A1 to the one directly previous to the current row.

A3 Convert Cost for Sales values in A2 into a string and put it on the clipboard.

3. Now switch back to the Excel file to select data area A1:F7 and press Ctrl+C to copy it to the clipboard.

Then back to esProc and press F9 to execute the script. Click F2 in the Excel file after the execution is finished, and press Ctrl+V to paste the result in.