'upgrade' a formula to generate combinations?

[excelNewbie22]

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...

=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.

 

[cmowla]

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:

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.

 

[excelNewbie22]

thanks, i'll first try to upgrade to 365 and see what is best for me

 

[cmowla]

thanks, i'll first try to upgrade to 365 and see what is best for me

Just get the trial version. It's free. If you don't like it, revert back to 2021.

 

[excelNewbie22]

thanks, just installed it
still not working for me

=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

=BYROW(B2:D4,LAMBDA(r,SUM(r)))

now it's working

also this working now too

=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

 

[cmowla]

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.

 

[Fluff]

If you click on File, Account what does it say your channel is?

 

[cmowla]

Just for confirmation, mine says the same as Fluff's.

 

[excelNewbie22]

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

=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

=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

=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<>""))

 

[excelNewbie22]

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

  =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<>""))

 

[excelNewbie22]

thanks a lot to dave3009

test
	A	B	C	D	E	F	G	H	I	J
1	1		3	4	5	6	7	8	9	10
2
3										1-3-4-5-6-7
4										1-3-4-5-6-8
5										1-3-4-5-7-8
6										1-3-4-6-7-8
7										1-3-5-6-7-8
8										1-4-5-6-7-8
9										3-4-5-6-7-8
10										1-3-4-5-6-9
11										1-3-4-5-7-9
12										1-3-4-6-7-9
13										1-3-5-6-7-9
test

Cell Formulas
Range	Formula
J3:J86	J3	=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.