Problem description & analysis

Below is Excel file book1.xlsx:

We are trying to sort column A (Parent Column) according to the order of values in column B (Modifier (Column)), the list of key words. Below is the desired result:

Solution

We write the following script p1.dfx in esProc:

Explanation:

A1   Copy and paste column A (without headers) from the Excel file.

A2  Copy and paste column B (without headers) from the Excel file.

A3  Import A1’s string as a sequence.

A4  Import A2’s string as a sequence and convert members into lowercase.

A5  Convert each member (a sentence) of A3’s sequence into lowercase, split it into a sequence of words, find the sequence number of each word in A4’s sequence, get value at the minimum sequence number from the sentence (the word with the highest priority), and sort A3 according to the order of values at the smallest sequence numbers.

Concatenate A5’s result into a sequence, where rows are separated by carriage return.

After code is executed, select cell A6 in esProc and click “Copy data” button on the right. Then back in Excel to click cell A2 and press Ctrl+V to paste result in.

Q & A Collection

https://stackoverflow.com/questions/63649754/excel-i-want-to-sort-a-column-based-on-modifier-list-of-words