Task description

The following Excel file (book1.xlsx) records proportions of materials:

Every two columns make a pair (a group). The odd-numbered column contains material names and the even-numbered column has the percentages of materials. Our goal is to find the material having the most percentage and enter it in Max column. 

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:

Let’s write the code separately for easy viewing:

A1   Read in data from clipboard. @n option enables splitting each row by carriage return \n and then dividing each split item by \t and returning a sequence of sequences.

A2   Loop through each row of data in A1 to process them. ~.step(2,2).pmax()gets data every two columns beginning from the second column to generate a sequence consisting of values of column 2, column 4, column 6…, and locates the sequence number of the maximum member value. Likewise, ~.step(2,1)gets data every two columns beginning from the first column to generate a sequence consisting of values of column 1, column 3, column 5…, and locates the sequence number of the maximum member value.

A3   Concatenate values of A2’s sequence by carriage return \n into a string and put it on clipboard.

3. Select data area A2:F6in Excel file and press Ctrl+C to copy it onto the clipboard. 

Then switch back to esProc, press F9 to execute the program. After the execution is over, switch back to Excel to click G2 and press Ctrl+V to paste the result in. 

【Reference】 material.zip