Excel Lambda Imports

cwasGist.txt

//All in 1 change formulas, EXCEPT EARNINGS RATIO------------------
//New is the column with the most recent values
//Old is the column with the older values
Change=
LAMBDA(
    NEW, OLD,
   IFERROR(IF(AND(LEFT(CELL("format",NEW),1)="P", LEFT(CELL("format",OLD),1)="P"),
NEW-OLD,
(NEW-OLD)/ABS(OLD)),"")
)
;


//ListOfTextMatches------------------
// Select a word to look for and a column to look in
//outputs filtered list containing that text
ListOfTextMatches=
LAMBDA(
  SelectTextToLookFor,
  SelectRangeToLookIn,
    LET(
      FlagMatch, 
      ISNUMBER(
        SEARCH( SelectTextToLookFor,SelectRangeToLookIn)
        ),
    FILTER(SelectRangeToLookIn,FlagMatch,"Not Found")
    )
)
;




// KeepOddItems----------------------------------
// Highlight a range of numbers and it FILTERS for the ODD numbers
//note the +0 forces the range to spill

KeepOddItems = 
LAMBDA(SelectList,
LET(
FlagOdd, ISODD(SelectList+0),
_Result,FILTER(SelectList,FlagOdd,"No Odd Numbers"),
_Result)
)
;


// ErrorRowNumbers -----------------------------------------
// Return the row references of a column of values that contain errors
// Result is presented as commma separated list

ErrorRowNumbers = 
LAMBDA( CellRange, 
LET(
_RowNumbers, ROW(CellRange ),
_ERRORFlags, ISERROR(CellRange),
_FilteredList, FILTER( _RowNumbers, _ERRORFlags,"No Errors"),
TEXTJOIN(", ",,_FilteredList)
));


//DuplicatesCheck--------------------------
// Highlight a range of cells to get a statement of whether duplicates are present
DuplicatesCheck
=LAMBDA( RangeOfCells,  
   IF(
     MAX( COUNTIFS( RangeOfCells, RangeOfCells  ) )-1 
         <>0,
     "Duplicates Exist",
     "No Duplicates"
    )
  )
;
//DuplicatesListFromColumn---------------------------
// Text Joined list of duplicates in a selected column
  DuplicatesListFromColumn
  =LAMBDA(ColumnRange, 
  TEXTJOIN(
       ", ",,
            SORT(
           UNIQUE(
               FILTER( ColumnRange,COUNTIFS( ColumnRange, ColumnRange  ) > 1,0)
                )
                )
            )
          )
          ;

//Compare2Ranges--------------------------
//Select 2 ranges and get a cell reference output
Compare2Ranges=
LAMBDA(Range1, Range2, 
        TEXTJOIN(", ",TRUE,   
            IF( (Range1<>Range2) = TRUE,     
                 ADDRESS(    ROW(Range2),
                    COLUMN(Range2)    
                        ),
                    ""
                )
                )
        )
;

//AggregateRowOfArray --------------------------------
//DemonstratingOptionalParameters and ISOMITTED function
//Utilises 2 Paramaters of AGGREGATE 
// defaults to SUM (9) and Ignore nothing (4) if optional paramaters are ommitted
// Calc types:  1 AVG, 2 COUNT,  3 COUNTA,  4 MAX, 5 MIN,  9 SUM etc...
// Rule types: 4 Ignore Nothing, 5 Ignore Hidden Rows, 6 Ignore Errors etc...

AggregateRowOfArray  =
LAMBDA(
       _SelectArray,
    [CalcType_1to12], 
    [RuleType_1to9], 
    LET(
    SelectedOperator, 
        IF( ISOMITTED(CalcType_1to12) ,9,CalcType_1to12),
    SelectedRule,
      IF( ISOMITTED(RuleType_1to9) ,4,RuleType_1to9),
    _RESULT,
        BYROW( 
            _SelectArray ,
            LAMBDA(row,AGGREGATE(SelectedOperator,SelectedRule,(row))
                    )
                ),
        _RESULT
    )
)

;


//AggregateColOfArray --------------------------------
//DemonstratingOptionalParameters and ISOMITTED function
//Utilises 2 Paramaters of AGGREGATE 
// defaults to SUM (9) and Ignore nothing (4) if optional paramaters are ommitted
// Calc types:  1 AVG, 2 COUNT,  3 COUNTA,  4 MAX, 5 MIN,  9 SUM etc...
// Rule types: 4 Ignore Nothing, 5 Ignore Hidden Rows, 6 Ignore Errors etc...

AggregateColOfArray  =
LAMBDA(
       _SelectArray,
    [CalcType_1to12], 
    [RuleType_1to9], 
    LET(
    SelectedOperator, 
        IF( ISOMITTED(CalcType_1to12) ,9,CalcType_1to12),
    SelectedRule,
      IF( ISOMITTED(RuleType_1to9) ,4,RuleType_1to9),
    _RESULT,
        BYCOL( 
            _SelectArray ,
            LAMBDA(col,AGGREGATE(SelectedOperator,SelectedRule,(col))
                    )
                ),
        _RESULT
    )
)

;

/*
CHRIS GROSS: MICROSOFT EXCEL TEAM    
FUNCTION NAME: APPENDCOLS
    DESCRIPTION: Appends two arrays column wise
    ARGS:
        array1: The array to append columns from array2 to
        array2: The array to append to array1

    EXAMPLE: 
        =APPENDCOLS(SEQUENCE(10,2),SEQUENCE(10,2,11))
*/

APPENDCOLS =LAMBDA(array1, array2,
    LET(
        //name definitions
        array1Rows, ROWS(array1),
        array1Cols, COLUMNS(array1),
        array2Rows, ROWS(array2),
        array2Cols, COLUMNS(array2),
        rowLen, MAX(array1Rows, array2Rows),        
        colLen, array1Cols + array2Cols,
        newArray, SEQUENCE(rowLen, colLen),
        colIndex, MOD(newArray - 1, colLen) + 1,
        rowIndex, 1 + ((newArray - colIndex) / colLen),
        
        //create the combined array
        resultArray, IF(
            colIndex > array1Cols,
            INDEX(array2, rowIndex, colIndex - array1Cols),
            INDEX(array1, rowIndex, colIndex)
        ),
        
        //return the resultArray
        resultArray
    )
);

/*
    FUNCTION NAME: APPENDROWS
    DESCRIPTION: Appends two arrays row-wise
    ARGS:
        array1: The array to append rows from array2 to
        array2: The array to append to array1

    EXAMPLE: 
        =APPENDROWS(SEQUENCE(10), SEQUENCE(10, 1, 11))
*/
APPENDROWS =LAMBDA(array1, array2,
    LET(
        array1Rows, ROWS(array1),
        colIndex, SEQUENCE(, MAX(COLUMNS(array1), COLUMNS(array2))),
        rowIndex1, SEQUENCE(array1Rows + ROWS(array2)),
        rowIndex2, rowIndex1 - array1Rows,
        IF(
            rowIndex2 >= 1,
            INDEX(array2, rowIndex2, colIndex),
            INDEX(array1, rowIndex1, colIndex)
        )
    )
);

ok