RaqForum 43 No.
300 View  • 
     ...

Excel Sort a Certain Column according to the Specific Order of Key Words It Contains

 

Problem description & analysis

Below is Excel data:


 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

Use the SPL XLL plug-in

Write the following formula in a blank cell

=spl("=(?1.conj().sort((X=lower(~).words(),b=?2.conj().(lower(~)),X.min(b.pselect(~:X.~))))).concat@n()",A2:A6,B2:B4)

As shown:..

Return：

..

Explanation:

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

Q & A Collection

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


 
 •  300 View  •