excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 534
- Office Version
- 365
- Platform
- Windows
hi,
i found this function which produce all combinations, but missing a couple of things,
(the array is deliberately including blank cells)
and the formula produce blanks regardless to the range as shown below,
can it be edited so it'll not produce them?
another thing, can it generate all in single column combined with hyphens? like:
1-2-3-4-8-11
1-2-3-4-8-12
and on and on...
COUNTA(A1:M1) - is for knowing how many numbers is it
SEQUENCE^0)=6) - for generating combinations of x numbers
A1:M1, "")) - the array for generating all the combinations from
i found this function which produce all combinations, but missing a couple of things,
(the array is deliberately including blank cells)
and the formula produce blanks regardless to the range as shown below,
can it be edited so it'll not produce them?
another thing, can it generate all in single column combined with hyphens? like:
1-2-3-4-8-11
1-2-3-4-8-12
and on and on...
Rich (BB code):
=LET(y, COUNTA(A1:M1), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=6), A1:M1, ""))
COUNTA(A1:M1) - is for knowing how many numbers is it
SEQUENCE^0)=6) - for generating combinations of x numbers
A1:M1, "")) - the array for generating all the combinations from
test | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | 1 | 2 | 3 | 4 | 8 | 11 | 12 | 22 | 24 | 33 | |||||||||||||||||
2 | |||||||||||||||||||||||||||
3 | 1 | 2 | 3 | 4 | 8 | 11 | #N/A | #N/A | #N/A | ||||||||||||||||||
4 | 1 | 2 | 3 | 4 | 8 | 12 | #N/A | #N/A | #N/A | ||||||||||||||||||
5 | 1 | 2 | 3 | 4 | 11 | 12 | #N/A | #N/A | #N/A | ||||||||||||||||||
6 | 1 | 2 | 3 | 8 | 11 | 12 | #N/A | #N/A | #N/A | ||||||||||||||||||
7 | 1 | 2 | 4 | 8 | 11 | 12 | #N/A | #N/A | #N/A | ||||||||||||||||||
test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M3:Y212 | M3 | =LET(y, COUNTA(A1:M1), x, MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(, y, 0)), 2), IF(FILTER(x, MMULT(x, SEQUENCE(y)^0)=6), A1:M1, "")) |
Dynamic array formulas. |