Skip to content

Instantly share code, notes, and snippets.

@fstfwd

fstfwd/excel-lambda-sortbymonth.txt

Forked from YeeBePrime/excel-lambda-sortbymonth.txt
Created June 15, 2023 14:58
Show Gist options
  • Save fstfwd/4f41f8fbfe742d3d4146a5f3e688a75e to your computer and use it in GitHub Desktop.
Save fstfwd/4f41f8fbfe742d3d4146a5f3e688a75e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @YeeBePrime YeeBePrime created this gist Sep 20, 2022.
    121 changes: 121 additions & 0 deletions excel-lambda-sortbymonth.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,121 @@
    /*
    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)
    )
    )
    )
    ;