Task description

The Excel file below contains 3 columns. Master10 contains 10-digit numbers. Digit8 contains the first 8 digits of the corresponding Master10 value.

We want to calculate values for Ismax. The computing rule is this: group rows by Digit8. If the current Master10 value is the largest value in the group, make it the Ismax value; otherwise enter Nil as the Ismax 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 with esProc:

To get a clearer view of the script, we have this:

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

A2   Copy A1’s data.

A3   Loop through each row of A1 while getting all rows from A2 whose Digit8 value is equal to the current Digit8 value, finding the largest Master10 value from those rows and comparing the largest value with the Master10 value in the current row. If they are equal, return the Master10 value; otherwise return Nil.

A4   Join A3’s result values by the carriage return \n and write it to the clipboard.

3.  With the script ready, open the Source Excel file, select the data area A1:B8 and press Ctrl+C to copy it to the clipboard.

Then return back to esProc and press F9 to execute the code. After execution you enter the Excel file again, click C2 and press Ctrl+V to copy the final result to in.

【Reference】 ismax.zip