How to Locate the Nearest Row of Same Class

Task description

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

The task is to calculate values for column Cost for Sales. The rule is like this: If Action is Bought, there’s no need to get the target value; if it is Sold, find the same type of Stock for 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 script in esProc:

List the code separately for an easy viewing:

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 sequence number of the row with same Stock and that satisfies Action=Bought and, assign the sequence number to variable k. Then the Cost for Sales in the current row is Dynamic Cost in the kth row multiplied by the current Shares. Expression ~[:-1] represents rows from the first one 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 clipboard.

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

【Reference】 stock.zip