The following Excel table records four indexes of certain people:

Task: Beginning from column F, enter names of indexes by index values in descending order.

Two ways of handling indexes of same values:

1. Place them in order in multiple cells, as shown below:

 2. Place them in order in one cell, as shown below:

Order-based calculations are needed. Excel functions can handle certain of them, such as sorting in ascending or descending order. But others, such as getting the sequence number of a value before sorting and getting the sequence numbers of members of a group before they are grouped, are hard to deal with in Excel.

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. Open the above file in Excel, select the area of B2:E3 and press Ctrl+C to paste it to the clipboard.

3. Open esProc, select cell A1, and press Ctrl+V to paste data from the clipboard to A1. Make sure that the caret is placed above A1 in its editing status during the pasting.

4. Write the following esProc script (the first way of handling indexes):

Script function split()splits strings and rearrange them into a table. psort() function gets sequence numbers of members before they are sorted; @z option enables a sorting in descending order; A2.()loops through each members of sequence A2, where ~ represents the current members. A3() gets members of sequence A3 in order.

5. Press F9 to execute the SQL script. Then you can click A5 to view the result in the value viewing section on the right. Click the “Copy data” button on the right-hand, or, press Ctrl+C, to paste A5’s result to clipboard.

6. Select cell F2 in Excel and press Ctrl+V to paste the result in. Then you can enter the column headers manually.

7. Below is the script for handling indexes in the second way, with same steps as in the first way.

Script function group() groups data and return groups of members by default; @p option enables returning sequence numbers of members in each group before they are grouped.