Problem description & analysis:
An Excel table has three columns:
| A | B | C | |
| 1 | A1 | B1 | 51 |
| 2 | A2 | B2 | 721 |
| 3 | A3 | B3 | 382 |
| 4 | A4 | B4 | 9 |
| 5 | A5 | B5 | 73 |
| 6 | A6 | B6 | 577 |
| 7 | A7 | B7 | 91 |
| 8 | A13 | B13 | 150 |
| 9 | A14 | B14 | 49 |
| 10 | A17 | B17 | 702 |
| 11 | A18 | B18 | 705 |
| 12 | A34 | B34 | 33 |
| 13 | A35 | B35 | 409 |
| 14 | A36 | B36 | 579 |
| 15 | A37 | B37 | 10 |
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:
| E | F | G | |
| 1 | A4 | B4 | 9 |
| 2 | A37 | B37 | 10 |
| 3 | A34 | B34 | 33 |
| 4 | A14 | B14 | 49 |
| 5 | A17 | B17 | 702 |
| 6 | A18 | B18 | 705 |
| 7 | A1 | B1 | 51 |
| 8 | A5 | B5 | 73 |
| 9 | A7 | B7 | 91 |
| 10 | A13 | B13 | 150 |
| 11 | A3 | B3 | 382 |
| 12 | A35 | B35 | 409 |
| 13 | A6 | B6 | 577 |
| 14 | A36 | B36 | 579 |
| 15 | A2 | B2 | 721 |
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.
Please feel free to download esProc Desktop for FREE ⬇️
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso