5G functions for Excel for Dynamic Ranges

BXL LAMBDA Dynamic Ranges

/*  FUNCTION NAME:  Aboutλ
    DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/*  REVISIONS:      Date        Developer       Description  
                    Mar 02 2023 Craig Hatmaker  Original Development
                    Jun 08 2023 Craig Hatmaker  CBSE Compliant
                    Jan 17 2024 Craig Hatmaker  See DynamicArrayλ
*/

Aboutλ =TEXTSPLIT(
    "About:                         →BXL's Dynamic Array module. Suggested module name: BDR¶" &
    "Version:                       →Mar 02 2023¶" &
    "Gist URL:                      →https://gist.github.com/CHatmaker/d14b6fcba6acf55d30b56e554e7d6c48¶" &
    "Website:                       →https://sites.google.com/site/beyondexcel/home/excel-library/module-dynamic-ranges¶" &
    "→¶" &
    "Function                       →Description¶" &
    "Helpλ                          →Produces this table¶" &
    "DynamicRowsλ                   →Create a Dynamic array of X rows from a contiguous range of non-blank cells in the first column¶" &
    "DynamicColumnsλ                →Create a Dynamic array of X columns from a contiguous range of non-blank cells in the first row¶" &
    "DynamicArrayλ                  →Create a 2D Dynamic array from a contiguous range of non-blank cells in the first row and first column",
    "→",
    "¶"
);




/*  FUNCTION NAME:  DynamicRowsλ
    DESCRIPTION:*/  /**Create a Dynamic array from a contiguous range of non-blank cells in a column */
/*  REVISIONS:      Date            Developer       Description  
                    Mar 02 2023     Craig Hatmaker  Original Development
                    Jun 08 2023     Craig Hatmaker  CBSE Compliant
*/

DynamicRowsλ = LAMBDA(
//  Parameter Declarations
    [TopLeftCell], 
    [Columns], 
    
//  Procedure
    LET(Help,       TRIM(TEXTSPLIT( 
                        "DESCRIPTION:   →Create a Dynamic array from a contiguous range of non-blank cells in a column .¶" & 
                        "VERSION:       →Jun 08 2023¶" &
                        "PARAMETERS:→¶" & 
                        "TopLeftCell    →(Required) Contiguous range's first cell.¶" & 
                        "Columns        →(Optional) Number of adjacent columns to include in the dynamic range.¶" & 
                        "→¶" &
                        "EXAMPLES:→¶" & 
                        "Result         →Formula - Assumes 4 contiguous cells starting in A1¶" &
                        "4              →=COLUMNS(DYN.DynamicColumnsλ(A1,1))" ,
                        "→", "¶" )
                    ),
    //  Check inputs
        TopLeftCell,    IF(OR(ISOMITTED(TopLeftCell), TopLeftCell=""), #Value!, TopLeftCell),    
        Columns,        IF(OR(ISOMITTED(Columns), Columns=""), 1, Columns),    

    //  Procedure
        Rows,           MATCH(TRUE, ISBLANK(OFFSET( TopLeftCell, 0, 0, 999999, 1)), 0) - 1,
        Result,         OFFSET(TopLeftCell, 0, 0, Rows, Columns),

    //  Handle Error
        Error,          MAX(IsError(Result)+1),
        
    //  Return Result
        Choose(Error, Result, Help)
    )         
);



/*  FUNCTION NAME:  DynamicColumnsλ
    DESCRIPTION:*/  /**Create a Dynamic array from a contiguous range of non-blank cells in a row */
/*  REVISIONS:      Date            Developer       Description  
                    Mar 02 2023     Craig Hatmaker  Original Development
                    Jun 08 2023     Craig Hatmaker  CBSE Compliant
*/

DynamicColumnsλ = LAMBDA(
//  Parameter Declarations
    [TopLeftCell], 
    [Rows], 
    
//  Procedure
    LET(Help,       TRIM(TEXTSPLIT( 
                        "DESCRIPTION:   →Create a Dynamic array from a contiguous range of non-blank cells in a row .¶" & 
                        "VERSION:       →Jun 08 2023¶" &
                        "PARAMETERS:→¶" & 
                        "TopLeftCell    →(Required) Contiguous range's first cell.¶" & 
                        "Rows           →(Optional) Number of adjacent rows to include in the dynamic range.¶" & 
                        "→¶" &
                        "EXAMPLES:→¶" & 
                        "Result         →Formula - Assumes 4 contiguous cells starting in A1¶" &
                        "4              →=ROWS(DYN.DynamicColumnsλ(A1,1))" ,
                        "→", "¶" )
                    ),
    //  Check inputs
        TopLeftCell,    IF(OR(ISOMITTED(TopLeftCell), TopLeftCell=""), #Value!, TopLeftCell),    
        Rows,           IF(OR(ISOMITTED(Rows), Rows=""), 1, Rows),    

    //  Procedure     
        Cols,           MATCH(TRUE, ISBLANK(OFFSET( TopLeftCell, 0, 0, 1, 9999)), 0) - 1,
        Result,         OFFSET(TopLeftCell, 0, 0, Rows, Cols),

    //  Handle Error
        Error,          MAX(IsError(Result)+1),
        
    //  Return Result
        Choose(Error, Result, Help)
    )
);



/*  FUNCTION NAME:  DynamicArrayλ
    DESCRIPTION:*/  /**Create a 2D Dynamic array from a contiguous range of non-blank cells*/                   
/*  REVISIONS:      Date            Developer       Description  
                    Mar 02 2023     Craig Hatmaker  Original Development
                    Jun 08 2023     Craig Hatmaker  CBSE Compliant
*/

/*  FUNCTION NAME:  DynamicArrayλ
    DESCRIPTION:*/  /**Create a 2D Dynamic array from a contiguous range of non-blank cells*/                   
/*  REVISIONS:      Date        Developer       Description  
                    Mar 02 2023 Craig Hatmaker  Original Development
                    Jun 08 2023 Craig Hatmaker  CBSE Compliant
                    Jan 17 2024 Craig Hatmaker  Fixed ROW issue
*/

DynamicArrayλ = LAMBDA(
//  Parameter Declarations
    [TopLeftCell], 
    
//  Procedure
    LET(Help,       TRIM(TEXTSPLIT( 
                        "DESCRIPTION:   →Create a two dimensional dynamic array from a contiguous range of non-blank cells.¶" & 
                        "VERSION:       →Jan 17 2024¶" &
                        "PARAMETERS:→¶" & 
                        "TopLeftCell    →(Required) Contiguous range's first cell.¶" & 
                        "→¶" &
                        "EXAMPLES:→¶" & 
                        "Result         →Formula - Assumes 4 contiguous cells starting in A1¶" &
                        "4              →=COLUMNS(DYN.DynamicColumnsλ(A1,1))" ,
                        "→", "¶" )
                    ),
    //  Check inputs
        TopLeftCell,    IF(OR(ISOMITTED(TopLeftCell), TopLeftCell=""), #Value!, TopLeftCell),    

    //  Procedure
        Rows,           MATCH(TRUE, ISBLANK(OFFSET( TopLeftCell, 0, 0, 999999 - COLUMN( TopLeftCell), 1)), 0) - 1,
        Cols,           MATCH(TRUE, ISBLANK(OFFSET( TopLeftCell, 0, 0, 1, 16384 - ROW( TopLeftCell))), 0) - 1,
        Result,         OFFSET(TopLeftCell, 0, 0, Rows, Cols), 

    //  Handle Error
        Error,          MAX(IsError(Result)+1),
        
    //  Return Result
        Choose(Error, Result, Help)      
      )
);