'upgrade' a formula to generate combinations?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
534
Office Version
  1. 365
Platform
  1. 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...

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(y)^0)=6) - for generating combinations of x numbers
A1:M1, "")) - the array for generating all the combinations from


test
ABCDEFGHIJKLMNOPQRSTUVWXY
1123481112222433
2
31234811#N/A#N/A#N/A
41234812#N/A#N/A#N/A
512341112#N/A#N/A#N/A
612381112#N/A#N/A#N/A
712481112#N/A#N/A#N/A
test
Cell Formulas
RangeFormula
M3:Y212M3=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.
 
cmowla
i appreciate all your help,
but since i need to repeat the formula several times, with differnet arrays, in the same sheet, it complicated things
You're quite welcome. I tried! I don't know what you're doing, but you could simply copy that sheet and have a formula/combination set on each page. (The code in the sheet is unique to the sheet. The code in the standard module is universal for all sheets.)

but.... is there anyway to bypass/modify the limit of 20 numbers? up to 35? for the modified formula in post #3?
This formula has:
Excel Formula:
SEQUENCE(2^y)
That's actually one of its fundamental components. Therefore I highly doubt it.

But even if it could, it would only allow for lists of 21 and 22 lists of numbers before the output will potentially go off the page. So trying to improve it wouldn't be worth it.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
thanks, just installed it
still not working for me
Excel Formula:
=LET(r,6,rng,A1:M1,y,COUNTA(rng),x,MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(,y,0)),2),DROP(TRANSPOSE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("-",1,HSTACK(IFERROR(IF(FILTER(x,MMULT(x,SEQUENCE(y)^0)=r),rng,""),""),RIGHT(SEQUENCE(COMBIN(y,r))*0&"|",1))),"|-","|"),"-|","|"),"|")),-1))

check for HSTACK and TEXTSPLIT - not working, isn't it suppose to work in 365?


StephenCrump
Excel Formula:
=BYROW(B2:D4,LAMBDA(r,SUM(r)))
now it's working

also this working now too
Excel Formula:
=LET(d,A1:t1,N,6,c,COUNT(d),x,BYROW(IF(MOD(INT(SEQUENCE(2^c,,0)/2^SEQUENCE(,c,0)),2),d,""),LAMBDA(r,IF(COUNT(r)=N,TEXTJOIN("-",,r),""))),FILTER(x,x<>""))

but like cmowla said, up to 20 numbers only, no more
 
Upvote 0
check for HSTACK and TEXTSPLIT - not working, isn't it suppose to work in 365?
It should. For some reason, some who have 365 don't have it. You might want to ask @Fluff about why this happens. If it's simply because people didn't get the latest update, that makes no sense in your case (as it should be that if you just download the thing, it should be the latest version!) I don't know.
 
Upvote 0
If you click on File, Account what does it say your channel is?
1663935395016.png
 
Upvote 0
Just for confirmation, mine says the same as Fluff's.
 
Upvote 0
thanks fluff, i had 2204 and update it to 2208
now all is working

but still have two problems, which is none ignore blanks, and limit of 20 won't cut it, any chance of modifications to fix this?

also these two
Excel Formula:
=LET(r,6,rng,A1:M1,y,COUNTA(rng),x,MOD(INT((SEQUENCE(2^y)-1)/2^SEQUENCE(,y,0)),2),DROP(TRANSPOSE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("-",1,HSTACK(IFERROR(IF(FILTER(x,MMULT(x,SEQUENCE(y)^0)=r),rng,""),""),RIGHT(SEQUENCE(COMBIN(y,r))*0&"|",1))),"|-","|"),"-|","|"),"|")),-1))
and
Excel Formula:
=DROP(TRANSPOSE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("-",1,HSTACK(IFERROR(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, "")),""),RIGHT(SEQUENCE(COMBIN(COUNTA(A1:M1),6))*0&"|",1))),"|-","|"),"-|","|"),"|")),-1)
have a limit of 13 numbers

so best one yet is this, but still not enough for the mention problems above
Excel Formula:
=LET(d,A1:t1,N,6,c,COUNT(d),x,BYROW(IF(MOD(INT(SEQUENCE(2^c,,0)/2^SEQUENCE(,c,0)),2),d,""),LAMBDA(r,IF(COUNT(r)=N,TEXTJOIN("-",,r),""))),FILTER(x,x<>""))
 
Upvote 0
hi,
if possible i might handle with just 24?
if not, can it be change to ignore blanks? mainly at the end of the range, like a1:t1 when t1 just for example, is blank

Excel Formula:
  =LET(d,A1:t1,N,6,c,COUNT(d),x,BYROW(IF(MOD(INT(SEQUENCE(2^c,,0)/2^SEQUENCE(,c,0)),2),d,""),LAMBDA(r,IF(COUNT(r)=N,TEXTJOIN("-",,r),""))),FILTER(x,x<>""))
 
Upvote 0
thanks a lot to dave3009

test
ABCDEFGHIJ
11345678910
2
31-3-4-5-6-7
41-3-4-5-6-8
51-3-4-5-7-8
61-3-4-6-7-8
71-3-5-6-7-8
81-4-5-6-7-8
93-4-5-6-7-8
101-3-4-5-6-9
111-3-4-5-7-9
121-3-4-6-7-9
131-3-5-6-7-9
test
Cell Formulas
RangeFormula
J3:J86J3=LET(d,TOROW(A1:J1,1,1),N,6,c,COUNT(d),x,BYROW(IF(MOD(INT(SEQUENCE(2^c,,0)/2^SEQUENCE(,c,0)),2),d,""),LAMBDA(r,IF(COUNT(r)=N,TEXTJOIN("-",,r),""))),FILTER(x,x<>""))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top