DALambdaWebinar

//DuplicatesListFromColumn---------------------------
// Text Joined list of duplicates in a selected column

ListDuplicatesInColumn
=LAMBDA(ColumnRange,
    TEXTJOIN(
        ", ",
        ,
        SORT(
            UNIQUE(
                FILTER(
                    ColumnRange,
                    COUNTIFS(
                        ColumnRange,
                        ColumnRange
                    ) > 1,
                    0
                )
            )
        )
    )
)
;


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


//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)
        ),
            UNIQUE(
                FILTER(SelectRangeToLookIn,FlagMatch,"Not Found")
            )
    )
)
;

//SUM_Array_Col------------------
//SUM the columns of an ARRAY

SUM_Array_Col = 
LAMBDA(
    SelectArray,
        BYCOL(SelectArray,
            LAMBDA(each,SUM(each)
                    )
            )
        )
;


//SUM_Array_Row------------------
//SUM the rows of an ARRAY

SUM_Array_Row = 
LAMBDA(
    SelectArray,
        BYROW(SelectArray,
            LAMBDA(each,SUM(each)
                    )
            )
        )
;

/*---------------------------------------------------------
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)
        )
    )
);



/*
    FUNCTION NAME: RandomSelection
    DESCRIPTION: Generate a randomly selected subset of a list
    ARGS:
        SelectList: The Original Single Column list
        NumberOfItemsToReturn: a whole number for how many items to return (Defaults to 1 if ommitted)

    EXAMPLE: 
        = RandomSelection(A1:A10,4)   gives four randonly selected items from the original list
        =RandomSelection(A1:10)  gives 1 randomly selected item
*/
RandomSelection=
LAMBDA(SelectList, [NumberOfItemsToReturn],
 
LET(  _ItemsToReturn,IF( ISOMITTED( NumberOfItemsToReturn ), 1,  NumberOfItemsToReturn ),
           TAKE(
               SORTBY(
               SelectList,    RANDARRAY(ROWS(SelectList))
                     ),
                _ItemsToReturn,1
                )
    ) 
);