Forum Discussion
Efficient approach to generate list of combinations with no repetition
Forgive me for asking, but have I got the wrong end of the stick here?
= IF(Combinationsλ(n,m), "●","-") Combinationsλ = LET( k, 2 ^ SEQUENCE(1, n, 0), h, SEQUENCE(2 ^ n), p, SIGN(BITAND(h, k)), f, BYROW(p, LAMBDA(p, SUM(p))) = m, FILTER(p, f) )
PeterBartholomew1to be fair, I tested it under the same conditions on an Excel desktop, and your solution takes less than 2secs. COMBIN.SETE around 2.2 secs. All previous tests I ran using the free version of Excel Web, I don't know why I am getting different results on each Excel version, maybe they have a different implementation of the underlying functions used, or I would need to do more stress tests. The thing is that for n=20, m=10 around 184K combinations, both solutions don't return the result after waiting several minutes.
@Peter Bartholomew another comment BYROW is less efficient than MMULT per my performance testing. I modified your approach as follows:
COMBIN.SETPB_A = LAMBDA(x, m,
LET(
y, TOROW(x),
n, COLUMNS(y),
k, 2 ^ SEQUENCE(1, n, 0),
h, SEQUENCE(2 ^ n),
p, SIGN(BITAND(h, k)),
f, MMULT(p, SEQUENCE(n, , 1, 0)) = m,
z, IF(FILTER(p, f), y, NA()),
WRAPROWS(TOCOL(z, 2), m)
)
)
When I tested it under Excel Web, now it provides the best performance overall, considering the limitation mentioned before about the limit of m <= 48 due to the binary function limitation, but the truth is that any solution will crash before reaching that number at least with the current computing excel capacity.
I used SEQUENCE(n,,1,0) over SEQUENCE(n)^0 because I tested them separately and the first one seem to be a little bit faster.