#47 — Sort An EXCEL Table According To Custom Segments

#productivity #programming #tutorial #beginners

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.