Last active
October 21, 2023 08:24
-
-
Save wynhopkins/02e4d1951e65334db53ce17f5519190f to your computer and use it in GitHub Desktop.
Wyn Trial Lambdas
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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) | |
) | |
) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment