Problem description & analysis:
An Excel table has three columns:
We want to sort rows of the table according to different segments of the 3rd column in ascending order. The 1st segment: the 3rd column value≤50; the 2nd segment: 700 < the 3rd column value < 720; the 3rd segment: the other cases (50 < the 3rd column value < 700 or the 3rd column value > 720). Below is the expected result:
Solution:
Use SPL XLL to do this:
=spl("=?.enum@n([$[?<=50],$[?>=701 && ?<=720]],~3).conj(~.sort(~3))",A1:C15)
As shown in the picture below:
Explanation:
enum()function performs enumerated grouping according to the value of the specified string expression; $[] represents a string; @ n option enables putting members that do not meet the enumerated conditions in one and separate group. ~ is the current member of a sequence, and ~3 is the 3rd member of a sequence.