RaqForum 43 No.
228 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


 
 •  228 View  •