Sort a Certain Column according to the Specific Order of Key Words It Contains
Problem description & analysis
Below is Excel file book1.xlsx:
| A |
B |
|
| 1 |
Parent Column |
Modifier (Column) |
| 2 |
Jack lives in the village |
Rose |
| 3 |
As mentioned by jack |
Village |
| 4 |
Rose already spoke to jack about last night |
Jack |
| 5 |
Rose left the village |
|
| 6 |
rose was their yesterday |
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:
| A |
B |
|
| 1 |
Parent Column |
Modifier (Column) |
| 2 |
Rose already spoke to jack about last night |
Rose |
| 3 |
Rose left the village |
Village |
| 4 |
rose was their yesterday |
Jack |
| 5 |
Jack lives in the village |
|
| 6 |
As mentioned by jack |
Solution
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
Jack lives in the village… |
| 2 |
Rose… |
| 3 |
=A1.import@i() |
| 4 |
=A2.import@i().(lower(~)) |
| 5 |
=A3.sort((X=lower(~).words(),X.min(A4.pselect(~:X.~)))) |
| 6 |
=A5.export() |
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.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
