Skip to content

Instantly share code, notes, and snippets.

@YeeBePrime

YeeBePrime/excel-lambda-sortbymonth.txt

Created September 20, 2022 23:41
Show Gist options
  • Save YeeBePrime/fd638e4f05b4a9f13a00b901313f2ef3 to your computer and use it in GitHub Desktop.
Save YeeBePrime/fd638e4f05b4a9f13a00b901313f2ef3 to your computer and use it in GitHub Desktop.
Download ZIP
Sort by month eg. Jan,22 Apr,22 Aug,22 jan23 using Excel lambda function
Raw
excel-lambda-sortbymonth.txt
/*
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