Forum Discussion
Solved
LAMBDA Halloween Challenge
PeterBartholomew1 SergeiBaklan mtarler Goal: generate an array of all possible combinations (w/repetitions) from a list of items. For example, if there are 6 total items with 4 chosen (allowing...
Finally, I have a solution!
This time I used a Lambda function to add 1 to the previous index (stored in base n) but before returning the value, I scanned the digits and copied the larger digit across.
WorksheetFormula = LET( count, COUNTA(Items), initialise, REPT("0",pick), indices, VSTACK( initialise, SCAN(initialise, SEQUENCE(combinations-1), NextCombinationλ(count,pick)) ), INDEX(Items, 1 + Explodeλ(indices)) )where
NextCombinationλ = LAMBDA(n,p, LAMBDA(initial,s, LET( place, n^SEQUENCE(1,p,p-1,-1), increment, 1 + SUM(place * Explodeλ(initial)), converted, BASE(increment,n,p), vector, Explodeλ(converted), modified, SCAN( , vector, Maxλ), CONCAT(modified) ) ) );supported by
Explodeλ = LAMBDA(term, MID(term, SEQUENCE(1,pick), 1)); Maxλ = LAMBDA(u, v, Max(u,v));I have just performed the calculation for 8 picks taken for 9 items in 300ms. Ther are 12,870 combinations and the permutations would have been over 43M!
Patrick2788 , I'm not a pythonist and a not person who is able to improve python codes. However, perhaps bit shorter will be
import itertools as it
items = xl("Items").to_numpy().tolist()
pick = xl("pick")
combinations = list(it.combinations_with_replacement(items,pick) )
np.vstack(combinations).reshape(-1,pick)not sure about performance.
That's an interesting approach. The combinations part (or permutations) is straight forward. It becomes a matter of how to best unpack the list because Excel does not like nested arrays! This gives me something to experiment with. Thank you for the reply!