Skip to content

Instantly share code, notes, and snippets.

@teleksterling

teleksterling/Utils.txt

Last active May 4, 2024 07:38
Show Gist options
  • Save teleksterling/b1e7e130d0cfa5d110fbf1f9c959eb5c to your computer and use it in GitHub Desktop.
Save teleksterling/b1e7e130d0cfa5d110fbf1f9c959eb5c to your computer and use it in GitHub Desktop.
Download ZIP
Excel Lambda Utility Functions (Excel Labs / Advanced Formula Environment)
Raw
Utils.txt
Header = LAMBDA(Table, TAKE(Table, 1));
Data = LAMBDA(Table, DROP(Table, 1));
ConstArray = LAMBDA(value, rows, columns,
EXPAND(value, rows, columns, value)
);
Reverse = LAMBDA(array,
LET(seq, SEQUENCE(ROWS(array)), SORTBY(array, seq, -1))
);
Between = LAMBDA(value, limit1, limit2, [inclusive],
LET(
lower, min(limit1,limit2),
upper, max(limit1,limit2),
if(inclusive,
and(value>=lower,value<=upper),
and(value>lower,value<upper)
)
)
);
IfOmitted = LAMBDA(value, value_if_omitted,
IF(ISOMITTED(value),
value_if_omitted, value
));
FindInDelimitedLists = LAMBDA(find, lookIn, delimiter,
MAP(
lookIn,
LAMBDA(delimitedList,
OR(TEXTSPLIT(delimitedList, delimiter) = find)
)
)
);
BMAPλ =
/* Recursively bisects an array and applies a function to the leaf nodes.
Useful for when the result of the Fnλ(leaf) calls are not of equal length.
As such, BMAPλ can produce ragged arrays.
Credit: Peter Bartholemew
https://www.linkedin.com/in/peterbartholomew/
https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174#profile
---
e.g. For a unique value of ID with n rows in X, create a single row with {date1, value1, date2, value2, ..., date_n, value_n}
Repeat for each unique value of ID.
X = {
"ID07", 44289, 3;
"ID07", 44322, 4;
"ID08", 45251, 7;
"ID22", 44511, 12;
"ID22", 45159, 2;
"ID22", 45038, 5;
"ID45", 45108, 16;
"ID45", 45116, 8;
"ID61", 45116, 32
};
Fnλ = LAMBDA(v, TOROW(FILTER(TAKE(X,,-2), TAKE(X,,1) = v)))
BMAPλ = {
"ID07", 44289, 3, 44322, 4, "", "";
"ID08", 45251, 7, "", "", "", "";
"ID22", 44511, 12, 45159, 2, 45038, 5;
"ID45", 45108, 16, 45116, 8, "", "";
"ID61", 45116, 32, "", "", "", ""
};
*/
LAMBDA(X, Fnλ,
LET(
n, ROWS(X),
Y, IF(
n > 1,
LET(
ℓ, n - QUOTIENT(n, 2),
X₁, TAKE(X, ℓ),
X₂, DROP(X, ℓ),
Y₁, BMAPλ(X₁, Fnλ),
Y₂, BMAPλ(X₂, Fnλ),
IFERROR(VSTACK(Y₁, Y₂), "")
),
Fnλ(X)
),
Y
)
);
PERMUTATEARRAY =
/* This function recursively generates all unique permutations of an ordered array of symbols.
It is efficient - i.e., it does not generate duplicate rows that require filtering.
The arguments are:
symbolArray - ia a required array input (unless the recursion is done).
Is an array of ordered symbols where the left most column contains symbols that will be permutated
by the permutation of the symbols in the next columns to the right. e.g.,
with symbolArray of:
A 1
B 2
A and B will be permutated by 1 and 2:
A 1
A 2
B 1
B 2
byArray - optional array that will be used to permutate the next column of the symbolArray.
This is passed between recursions and it not intended for use by the user but it can be used.
cleaned - optional argument to indicate that the symbolArray has already been cleaned.
This prevents the function from repeatedly cleaning the symbolArray that would otherwise require
a repetition for each column of the symbolArray. It is passed between recursions and it not
intended for use by the user but can be used.
Example - With a symbolArray of:
A C 1
B D 2
The output would be the following array:
A C 1
A C 2
A D 1
A D 2
B C 1
B C 2
B D 1
B D 2
NOTES:
- Blanks will be ignored.
- errors will be ignored. (see comments below to change this)
- all rows of the resulting array will be unique
- blank columns in the symbol array are removed
- this function has no dependencies on external LAMBDA functions even though that would make it more
readable.
------------------------------------ */
LAMBDA(symbolArray, [byArray], [cleaned],
IF(
AND(ISOMITTED(symbolArray), ISOMITTED(byArray)),
ERROR.TYPE(7), // DONE
IF(
ISOMITTED(symbolArray),
byArray, // if there is no symbolArray, the function is DONE.
LET(
clnSymArray, IF(
ISOMITTED(cleaned), //If the symbol array has not been cleaned, then clean it.
/* Only clean arrays can be permuated. They cannot contain blanks because those are interpreted as 0's.
The input also cannot contain entirely blank columns, so these are removed.
The input cells also cannot contain errors as this will cause the whole function to error. That,
however, is a design choice. They are filtered out inside of this function because the user cannot
easily filter them out before passing them as arguments - IFERROR(x,"") causes all blanks to become 0's.
*/
LET(
COMPRESSC, LAMBDA(a,
FILTER(
a,
BYCOL(
a,
LAMBDA(a, SUM(--(a <> "")) > 0)
)
)
),
REPLBLANKS, LAMBDA(array, [with],
LET(
w, IF(ISOMITTED(with), "", with),
IF(array = "", w, array)
)
),
REPLERRORS, LAMBDA(array, [with],
LET(
w, IF(ISOMITTED(with), "", with),
IFERROR(array, w)
)
),
COMPRESSC(
REPLERRORS(REPLBLANKS(symbolArray))
)
// COMPRESSC( REPLBLANKS(symbolArray) ) //removes the REPLERRORS if the user wants errors to result in erroring the function.
),
symbolArray
), //otherwise, pass the symbolArray
//Once cleaned, effectively execute PERMUTATEARRAY( clnSymArray, byArray, 1 )
IF(
AND(
COLUMNS(clnSymArray) = 1,
ISOMITTED(byArray)
),
UNIQUE(FILTER(clnSymArray, clnSymArray <> "")),
/* if the user gives a single column, give it back clean even if it was already cleaned.
there is no point in testing again whether Clean has been set. DONE */
/* Otherwise, we can recursively process the inputs in the following LET. */
LET(
// MUX is an internal LAMBDA function that permutates the left most column of the p array by the b (by) array.
MUX, LAMBDA(p, b,
LET(
pR, ROWS(p),
byR, ROWS(b),
byC, COLUMNS(b),
byCseq, SEQUENCE(, byC + 1), // forces this to look at only one column of p
oRSeq, SEQUENCE(byR * pR, , 0),
IFERROR(
INDEX(
b,
oRSeq / pR + 1,
byCseq
),
INDEX(
p,
MOD(oRSeq, pR) + 1,
byCseq - byC
)
)
)
),
pRSeq, SEQUENCE(ROWS(clnSymArray)),
// Decide when to apply MUX versus when to recurse. MUX is always the final output.
// if there are only two symbol columns with no byArray, filter & MUX the two columns - DONE
IF(
AND(
COLUMNS(clnSymArray) = 2,
ISOMITTED(byArray)
),
LET(
pFin, INDEX(clnSymArray, pRSeq, 2),
fpFin, UNIQUE(
FILTER(pFin, pFin <> "")
),
bFin, INDEX(clnSymArray, pRSeq, 1),
fbFin, UNIQUE(
FILTER(bFin, bFin <> "")
),
MUX(fpFin, fbFin)
),
// if there are more than two symbol columns with no byArray, repartition the symbol and byArray and recurse
IF(
AND(
COLUMNS(clnSymArray) > 2,
ISOMITTED(byArray)
),
LET(
pC, COLUMNS(clnSymArray),
pCSeq, SEQUENCE(, pC - 2, 3),
pNext, INDEX(
clnSymArray,
pRSeq,
pCSeq
),
pFin, INDEX(
clnSymArray,
pRSeq,
2
),
fpFin, UNIQUE(
FILTER(pFin, pFin <> "")
),
bFin, INDEX(
clnSymArray,
pRSeq,
1
),
fbFin, UNIQUE(
FILTER(bFin, bFin <> "")
),
bNext, MUX(fpFin, fbFin),
PERMUTATEARRAY(pNext, bNext, 1)
),
// if there is more than one symbol column and a byArray, repartition the symbol and byArray and recurse
IF(
AND(
COLUMNS(clnSymArray) > 1,
NOT(ISOMITTED(byArray))
),
LET(
pC, COLUMNS(clnSymArray),
pCSeq, SEQUENCE(
,
pC - 1,
2
),
pNext, INDEX(
clnSymArray,
pRSeq,
pCSeq
),
pFin, INDEX(
clnSymArray,
pRSeq,
1
),
fpFin, UNIQUE(
FILTER(
pFin,
pFin <> ""
)
),
bNext, MUX(fpFin, byArray),
PERMUTATEARRAY(
pNext,
bNext,
1
)
),
// if there is only one symbol column and a byArray, filter symbol column & MUX it with he byArray - DONE
IF(
AND(
COLUMNS(clnSymArray) =
1,
NOT(ISOMITTED(byArray))
),
LET(
pFin, INDEX(
clnSymArray,
pRSeq,
1
),
fpFin, UNIQUE(
FILTER(
pFin,
pFin <> ""
)
),
MUX(fpFin, byArray)
)
)
)
)
)
)
)
)
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment