-
-
Save fstfwd/4f41f8fbfe742d3d4146a5f3e688a75e to your computer and use it in GitHub Desktop.
Sort by month eg. Jan,22 Apr,22 Aug,22 jan23 using Excel lambda function
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| sortByMonth | |
| Description | |
| Sort month range eg. Jan,22 Apr,22 Aug,22 jan23 | |
| Syntax | |
| sortByMonth(monthRange) | |
| The sortByMonth function syntax has the following arguments: | |
| monthRange Required. The text in month format eg. Dec Aug,22 Jan,2022 | |
| Example | |
| A2=Feb,23 | |
| A3=Dec,22 | |
| A4=Jan,22 | |
| A5=Feb,22 | |
| B2=sortByMonth(A2:A5) | |
| Return | |
| B2=Jan,22 | |
| B3=Feb,22 | |
| B4=Dec,22 | |
| B5=Feb,23 | |
| */ | |
| =LAMBDA(monthRange, | |
| LET( | |
| _rng, monthRange, | |
| _y, IF( | |
| ISERROR( | |
| RIGHT( | |
| _rng, | |
| 4 | |
| ) + 1 | |
| ), | |
| IF( | |
| ISERROR( | |
| RIGHT( | |
| _rng, | |
| 2 | |
| ) + 1 | |
| ), | |
| "", | |
| RIGHT(_rng, 2) | |
| ), | |
| RIGHT(_rng, 4) | |
| ), | |
| _n, { | |
| "jan", | |
| "feb", | |
| "mar", | |
| "apr", | |
| "may", | |
| "jun", | |
| "jul", | |
| "aug", | |
| "sep", | |
| "oct", | |
| "nov", | |
| "dec" | |
| }, | |
| isM, IF( | |
| IFERROR( | |
| MATCH( | |
| "*" & | |
| LEFT( | |
| INDEX( | |
| _rng, | |
| 1, | |
| 1 | |
| ), | |
| 3 | |
| ), | |
| _n, | |
| 0 | |
| ), | |
| 0 | |
| ) > 0, | |
| TRUE, | |
| FALSE | |
| ), | |
| _s, MAP( | |
| _rng, | |
| LAMBDA(c, | |
| LET( | |
| _m, TEXT( | |
| MATCH( | |
| TRUE, | |
| INDEX( | |
| LEFT( | |
| c, | |
| 3 | |
| ) = | |
| _n, | |
| 0 | |
| ), | |
| 0 | |
| ), | |
| "00" | |
| ), | |
| _m | |
| ) | |
| ) | |
| ), | |
| IF( | |
| isM, | |
| SORTBY( | |
| _rng, | |
| _y & _s | |
| ), | |
| SORT(_rng) | |
| ) | |
| ) | |
| ) | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment