I admit the allure of demonstrating know-how whilst know-why remains elusive. As I continue the search for truly relevant applications, I claim baby steps at best. I share them here in the hope of sparking interest and ideas in you. Please give feedback when you can.
Contents:
Programming a "hold" argument
Recall the function shared in an earlier post to list permutations with repetitions, "pick r of N" as an array. As explained in that post, I used two arguments to mean the same thing, one for the fixed "r" pick number (called "hold"), and the other for the dynamic one required for recursion ("select"). At the time, I couldn't find a way to avoid having to input the number twice, which was quite vexing. I'm happy to say I found a solution for that.
The solution introduces the idea of using an optional argument to pass a value between recursion calls. The argument is still visible in the argument list when the function is entered in the sheet but is intended to be used only by the function itself. You might even think to name it "[donotuse]", but I think that's unnecessary if it's explained in the name comments.
As I also previously promised, I've added governance to avoid huge output lists. Optional argument [max] caps the rows in the output array and has a coded default of 10000. This allows you to see the permutation pattern without overloading the sheet. Optional argument [count] returns the PERMUTATIONA result so you can do a quick check without having to use another cell.
In addition to these argument changes, I've renamed others and the function itself. Below the image is the explanation of how to hold one parameter steady while also decrementing it in recursion.
Below in line 5, total = MAX(hold,pick) copies pick on the first call since hold is blank. Line 15 then passes total to the next call as [hold], while decrementing pick. Thus, [hold] takes the hand-off from pick in the first call and carries it through recursion for the touchdown.
permut.all = 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(from, pick - 1, max, , total) ) ) ) );
Here's a simple example of "pick 2 of 3". The first call generates the left digit, the second call generates the right digit. Recursion generates both and concatenates them.
Identify repeat characters in a string
I started working on the continuation of my challenge, to develop the other three variants of permutations and combinations, beginning with permutations without repetition, which corresponds to the PERMUT function.
The quandary is how to get from here to there. Should I copy the existing function to generate a list of all permutations, then identify those with repetition and filter the output list? Or should I attempt to modify the logic to only generate output without repetition in the first place? I'm interested in your input, please consider it.
As often happens in the process of developing a complex solution, I ended up with a byproduct, not what I was looking for, but which might have value in itself. The function identifies repeat characters within a string. It is one thing to find repeats within a range, easy with COUNTIF. To find repeats within a single cell requires recursive text functions to pick apart the string and check for repeats one character at a time.
This method also uses the technique described above to pass an invisible argument between recursion calls. The first call starts with pos =1 since [rep] is not given. char = the "pos character" within text = MID(text, pos, 1). find1 = the first position of char in text, FIND(char , text). FIND is used because it is case-sensitive. find2 = the second instance of char in text = FIND(char , text, find1 +1), starting at the character after find1 . find2 is then converted to Boolean by ISNUMBER. Thus, if find2 is true then char is repeated in text.
The function then recurses for each character of the string by passing pos+1 to [rep]. pos then picks it up via MAX(rep,1); char picks the next pos character, etc. In the end, all character repeats are combined in OR so that any repeated character counts as true. The recursion exit is when pos is greater than the length of the text.
Here's an example of a three-step process that I'm trying to combine into one: 1) my.permut.all, 2) my.repeats flagged and highlighted, 3) FILTER of step 1 with no repeats. Step 2 is not array-capable and thus must be copied down. Please, let me know if you have any idea how to combine the two functions to return only the no-repeat permutations.
Comments