Eight months ago when I first put forth the LAMBDA function my.permut.all to generate an array of ALL permutations WITH repetition, (e.g. 3-digit padlock with 000-999 = 1000 possible keys), I said that I would eventually deliver permutations WITHOUT repetition (and eventually combinations as well) if no one beat me to it. It was not a compelling need, just one of those mental itches that wouldn't go away.
The next month I wrote a function my.repeats to identify repeated characters in a string. This was a prerequisite step towards the goal of permutations without repetition. At the time I could not get array output even with array input, and I had not found the way to combine the steps for the desired result.
Both of these initial steps, my.permut.all and my.repeats, used recursion to do what I've now learned to do more efficiently with arrays. By using arrays to simplify LAMBDA logic I eliminate recursion and get the consistent output needed to combine the functions.
Update of my.permut.all WITH Repetition
The previous version used recursion to pick each element and concatenate them one by one until the "pick number" was reached.
permut.all.old = LAMBDA(from, pick, [max], [count], [hold], LET( hardmax, 10000, items, ROWS(from), total, MAX(hold, pick), perms, PERMUTATIONA(items, total), num, MIN(IF(max, max, hardmax), perms), list, SEQUENCE(num), rep, items ^ (pick - 1), ticker, MOD(ROUNDUP(list / rep, 0), items), digit, IF(ticker, ticker, items), IF(pick < 1,"",IF(count,perms, INDEX(from, digit) & my.permut.all.old(from, pick - 1, max, , total)
))));
Now, recursion is replaced by rectangular arrays, with concatenation by row of the final array.
"rep" uses SEQUENCE to define a horizontal array of declining powers of "items," from "pick-1" down to 0. For example, if picking 3 from 10 items, rep = {100, 10, 1}; if picking 4 from 5 items, rep = {125, 25, 5, 1}. Then "ticker" uses the vertical array "list" and the horizontal array "rep" to build a rectangular array; "digit" just replaces the zeroes with "items." Finally, the coup de grace is BYROW: the input array uses INDEX to replace the "digit" array with the "from" array members; the embedded LAMBDA operation concatenates each row to return a vertical array.
my.permut.all = LAMBDA(from, pick, [max],
LET(
hardmax, 10000,
items, ROWS(from),
perms, PERMUTATIONA(items, pick),
num, MIN(IF(max, max, hardmax), perms),
list, SEQUENCE(num),
rep, items ^ (SEQUENCE(1,pick,pick,-1) - 1),
ticker, MOD(ROUNDUP(list / rep, 0), items),
digit, IF(ticker, ticker, items),
BYROW(INDEX(from, digit),LAMBDA(row,CONCAT(row)))
)
);
Here the stages are laid out and compared to the actual function output. Formula cells are highlighted; the top left cell of each array defines the entire array. For simplicity, this illustration is limited to pick 4.
Update of my.repeats
Previously, the recursive method iterated through each character in the input string, starting from 1 up to the input length. If any character is found twice in the string, it is flagged as a repeat string.
my.repeats.old = LAMBDA(text, [rep], LET( tlen, LEN(text), pos, MAX(rep, 1), char, MID(text, pos, 1), find1, FIND(char, text), find2, ISNUMBER(FIND(char, text, find1 + 1)), IF(pos > tlen, "", OR(find2, my.repeats.old(text, pos + 1))) ) );
Now, pos is a horizontal array, generating rectangular arrays char, find1 and find2. BYROW is again applied at the output, in this case applying OR to each row. This flags each input string having any repeating character as a repeat string. OR is multiplied by 1 for binary output.
my.repeats = LAMBDA(text,
LET(
tlen, MAX(LEN(text)),
pos, SEQUENCE(1,tlen),
char, MID(text, pos, 1),
find1, FIND(char, text),
find2, ISNUMBER(FIND(char, text, find1 + 1)),
BYROW(find2, LAMBDA(row, 1*(OR(row))))
)
);
This function works for single string input or array input in which every element is the same length.
NEW my.permut.nr WITHOUT Repetition
Now these two functions can be combined by calling them from within the new function. Of course, the code could be copied instead. Since they are acting on the same inputs, the two arrays match up and can be filtered.
my.permut.nr = LAMBDA(from, pick, [max], LET( permall, my.permut.all(from, pick, max), repeats, my.repeats(permall), FILTER(permall, NOT(repeats)) ) );
The output here is =FILTER(permall,NOT(repeats)).
Comentários