ARRANGEMENTS

[Xlambda]

ARRANGEMENTS Extracts all permutations or combinations, with or without repetitions, of all elements of an array, by a number chosen.
Study inspired by latest MrExcel's YT (15aug21) Excel All Combinations Using Power Query - 2424
This is the first draft I came with, of course, the recursion way deserves first place. There are 2 other approaches, one using AFUSBYROW/BYCOL functions, and the other using new functions.
Calls 3 tool lambdas, 1 non recursive and 2 recursive (no risk to reach limitations, recursion takes place on the "short" direction, the columns one) . The recursive ones were created with ease using the ARF recursive kits concepts. Also, both recursive ones can be used as standalone lambdas, T_P is filtering an array by rows with no dups, and T_CA is filtering an array by rows that are in ascending order. The challenge was to do this by columns iterations and not by rows iteration (too many).
For ARRANGEMENTS function we use them to create the index patterns for extracting array's elements as are required. T_PA (non-recursive) creates the index pattern for permutations with repetitions, Once we have that, T_P (recursive) creates the index pattern for permutations without repetitions T_P(T_PA), T_CA creates the patterns for combinations with repetitions T_CA(T_PA), and for combinations without repetitions we use the same T_CA but this time, as input, we use T_P, T_CA(T_P). Calculation time for arrays having 1M rows 1 to 3 seconds.
T_PA(n,c)=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1) where n: number ; c: number chosen
T_P(a,[ai],[ i ] )=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,FILTER(a,MMULT(ai,SEQUENCE( n)^0)=n),T_P(a,ai+(x=a),j-1)))) !! recursive !!
T_CA(a,[ai],[ i ])=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",1,i),aj,IF(ai="",1,ai),x,INDEX(a,,j),IF(j=n,FILTER(a,aj),T_CA(a,aj*(x<=INDEX(a,,j+1)),j+1)))) !! recursive !! where a:array, ai,i,omitted

=LAMBDA(a,t,c,
    IF(AND(t<>{"p","pa","c","ca"}),"check type",
      LET(k,MAX(1,c),x,AFLAT(a),n,ROWS(x),IF(AND(OR(t={"p","c"}),k>n),"nr chosen>n !",LET(y,T_PA(n,k),
       SWITCH(t,"pa",INDEX(x,y),"p",INDEX(x,T_P(y)),"ca",INDEX(x,T_CA(y)),"c",LET(z,T_P(y),w,T_CA(z),INDEX(x,w))))))
    )
)

LAMBDA 1.1.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Introduction: Combinatorics Excel functions:
2	1,) PERMUTATIONA Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects.
3		- PERMUTATIONA(number, number-chosen); "nc" or "c" (number chosen) can be >=n (number of elements/objects) ; order is important; PA=n^nc
4	2.) PERMUT Returns the number of permutations for a given number of objects (no repetitions) that can be selected from the total objects.
5		- PERMUT(number, number_chosen); if nc>n returns #NUM! error; also called arrangements; order is important ;P=n!/(n-nc)!
6	3.) COMBINA Returns the number of combinations (with repetitions) for a given number of items.
7		- COMBINA(number, number_chosen) ; nc can be > n; order is not important; CA=(n+nc-1)!/(nc!*(n-1)!)
8	4.) COMBIN Returns the number of combinations (no repetitions) for a given number of items.
9		- COMBINA(number, number_chosen) ; nc can be > n; order is not important; C=P/nc! or C=n!/(nc!*(n-nc)!)
10	What ARRANGEMENTS does is "printing" or extracting all this numbers of permutations or combinations, given the object array "a" and the number_chosen "c",
11	for all types "t" above : "pa" for 1.) , "p" for 2.) , "ca" for 3.) ,"c" for 4.)
12
13		input n:	7	Table of values returned by all functions for an array of n objects ,nc [1,10]
14
15		function\nc	1	2	3	4	5	6	7	8	9	10
16		1,) PERMUTATIONA	7	49	343	2401	16807	117649	823543	5764801	40353607	282475249
17		2,) PERMUT	7	42	210	840	2520	5040	5040	#NUM!	#NUM!	#NUM!
18		3.) COMBINA	7	28	84	210	462	924	1716	3003	5005	8008
19		4.) COMBIN	7	21	35	35	21	7	1	#NUM!	#NUM!	#NUM!
20
21		Note:	It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects
22
ARR post 1

Cell Formulas
Range	Formula
C15:L15	C15	=SEQUENCE(,10)
C16:L16	C16	=PERMUTATIONA(C13,C15#)
C17:L17	C17	=PERMUT(C13,C15#)
C18:L18	C18	=COMBINA(C13,C15#)
C19:L19	C19	=COMBIN(C13,C15#)
Dynamic array formulas.

 

[Xlambda]

Please read all the posts first, there are all explanations there. The limits are Excel's limits, post #5
If you read the posts, there is no recursion limit because the direction of iterations is by the nr of columns not nr. of rows.

 

[st001]

Thank you for explaining this. I think I found the right post.
My Excel returns a zero (i.e. fails) when I go up to an array of 7. i.e. on ARRANGEMENTS({1,2,3,4,5,6,7}, "c", 7)

 

[Xlambda]

You do not need any function or calculator to calculate that.
COMBIN(7,7)=1
Anyhow ARRANGEMENTS works fine in all its designed aspects and in this case returns the only possible combination 1,2,3,4,5,6,7

Book1
	A	B	C	D	E	F	G	H	I
1
2
3		1	2	3	4	5	6	7
4
5		=ARRANGEMENTS(B3:H3,"c",7)
6		1	2	3	4	5	6	7
7
8
Sheet1

Cell Formulas
Range	Formula
B5	B5	=FORMULATEXT(B6)
B6:H6	B6	=ARRANGEMENTS(B3:H3,"c",7)
Dynamic array formulas.

 

[Xlambda]

Here are the other lists, Permutations (w and w/o repetitions) and Combinations with repetitions, for same array of 7 elementes and a nr.chosen of 7
Could be something with your computer that something makes it toooo slooooow.

Book1
	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	Z	AA
1		1	2	3	4	5	6	7
2
3		=PERMUTATIONA(7,7)					=ROWS(B7#)				=PERMUT(7,7)				=ROWS(K7#)					=COMBINA(7,7)				=ROWS(T7#)
4		823543					823543				5040				5040					1716				1716
5
6		=ARRANGEMENTS(B1#,"pa",7)									=ARRANGEMENTS(B1#,"p",7)									=ARRANGEMENTS(B1#,"ca",7)
7		1	1	1	1	1	1	1			1	2	3	4	5	6	7			1	1	1	1	1	1	1
8		1	1	1	1	1	1	2			1	2	3	4	5	7	6			1	1	1	1	1	1	2
9		1	1	1	1	1	1	3			1	2	3	4	6	5	7			1	1	1	1	1	1	3
10		1	1	1	1	1	1	4			1	2	3	4	6	7	5			1	1	1	1	1	1	4
11		1	1	1	1	1	1	5			1	2	3	4	7	5	6			1	1	1	1	1	1	5
12		1	1	1	1	1	1	6			1	2	3	4	7	6	5			1	1	1	1	1	1	6
13		1	1	1	1	1	1	7			1	2	3	5	4	6	7			1	1	1	1	1	1	7
14		1	1	1	1	1	2	1			1	2	3	5	4	7	6			1	1	1	1	1	2	2
15		1	1	1	1	1	2	2			1	2	3	5	6	4	7			1	1	1	1	1	2	3
16		1	1	1	1	1	2	3			1	2	3	5	6	7	4			1	1	1	1	1	2	4
17		1	1	1	1	1	2	4			1	2	3	5	7	4	6			1	1	1	1	1	2	5
18		1	1	1	1	1	2	5			1	2	3	5	7	6	4			1	1	1	1	1	2	6
19		1	1	1	1	1	2	6			1	2	3	6	4	5	7			1	1	1	1	1	2	7
20		1	1	1	1	1	2	7			1	2	3	6	4	7	5			1	1	1	1	1	3	3
21		1	1	1	1	1	3	1			1	2	3	6	5	4	7			1	1	1	1	1	3	4
22		1	1	1	1	1	3	2			1	2	3	6	5	7	4			1	1	1	1	1	3	5
23		1	1	1	1	1	3	3			1	2	3	6	7	4	5			1	1	1	1	1	3	6
24		1	1	1	1	1	3	4			1	2	3	6	7	5	4			1	1	1	1	1	3	7
25		1	1	1	1	1	3	5			1	2	3	7	4	5	6			1	1	1	1	1	4	4
26		1	1	1	1	1	3	6			1	2	3	7	4	6	5			1	1	1	1	1	4	5
27		1	1	1	1	1	3	7			1	2	3	7	5	4	6			1	1	1	1	1	4	6
28		1	1	1	1	1	4	1			1	2	3	7	5	6	4			1	1	1	1	1	4	7
29		1	1	1	1	1	4	2			1	2	3	7	6	4	5			1	1	1	1	1	5	5
30		1	1	1	1	1	4	3			1	2	3	7	6	5	4			1	1	1	1	1	5	6
31		1	1	1	1	1	4	4			1	2	4	3	5	6	7			1	1	1	1	1	5	7
32		1	1	1	1	1	4	5			1	2	4	3	5	7	6			1	1	1	1	1	6	6
33		1	1	1	1	1	4	6			1	2	4	3	6	5	7			1	1	1	1	1	6	7
34		1	1	1	1	1	4	7			1	2	4	3	6	7	5			1	1	1	1	1	7	7
35		1	1	1	1	1	5	1			1	2	4	3	7	5	6			1	1	1	1	2	2	2
36		1	1	1	1	1	5	2			1	2	4	3	7	6	5			1	1	1	1	2	2	3
37		1	1	1	1	1	5	3			1	2	4	5	3	6	7			1	1	1	1	2	2	4
38		1	1	1	1	1	5	4			1	2	4	5	3	7	6			1	1	1	1	2	2	5
39		1	1	1	1	1	5	5			1	2	4	5	6	3	7			1	1	1	1	2	2	6
40		1	1	1	1	1	5	6			1	2	4	5	6	7	3			1	1	1	1	2	2	7
41		1	1	1	1	1	5	7			1	2	4	5	7	3	6			1	1	1	1	2	3	3
42		1	1	1	1	1	6	1			1	2	4	5	7	6	3			1	1	1	1	2	3	4
43		1	1	1	1	1	6	2			1	2	4	6	3	5	7			1	1	1	1	2	3	5
44		1	1	1	1	1	6	3			1	2	4	6	3	7	5			1	1	1	1	2	3	6
45		1	1	1	1	1	6	4			1	2	4	6	5	3	7			1	1	1	1	2	3	7
46		1	1	1	1	1	6	5			1	2	4	6	5	7	3			1	1	1	1	2	4	4
47		1	1	1	1	1	6	6			1	2	4	6	7	3	5			1	1	1	1	2	4	5
48		1	1	1	1	1	6	7			1	2	4	6	7	5	3			1	1	1	1	2	4	6
49		1	1	1	1	1	7	1			1	2	4	7	3	5	6			1	1	1	1	2	4	7
50		1	1	1	1	1	7	2			1	2	4	7	3	6	5			1	1	1	1	2	5	5
51		1	1	1	1	1	7	3			1	2	4	7	5	3	6			1	1	1	1	2	5	6
52		1	1	1	1	1	7	4			1	2	4	7	5	6	3			1	1	1	1	2	5	7
53		1	1	1	1	1	7	5			1	2	4	7	6	3	5			1	1	1	1	2	6	6
54		1	1	1	1	1	7	6			1	2	4	7	6	5	3			1	1	1	1	2	6	7
55		1	1	1	1	1	7	7			1	2	5	3	4	6	7			1	1	1	1	2	7	7
56		1	1	1	1	2	1	1			1	2	5	3	4	7	6			1	1	1	1	3	3	3
57		1	1	1	1	2	1	2			1	2	5	3	6	4	7			1	1	1	1	3	3	4
58		1	1	1	1	2	1	3			1	2	5	3	6	7	4			1	1	1	1	3	3	5
59		1	1	1	1	2	1	4			1	2	5	3	7	4	6			1	1	1	1	3	3	6
60		1	1	1	1	2	1	5			1	2	5	3	7	6	4			1	1	1	1	3	3	7
61		1	1	1	1	2	1	6			1	2	5	4	3	6	7			1	1	1	1	3	4	4
62		1	1	1	1	2	1	7			1	2	5	4	3	7	6			1	1	1	1	3	4	5
63		1	1	1	1	2	2	1			1	2	5	4	6	3	7			1	1	1	1	3	4	6
64		1	1	1	1	2	2	2			1	2	5	4	6	7	3			1	1	1	1	3	4	7
65		1	1	1	1	2	2	3			1	2	5	4	7	3	6			1	1	1	1	3	5	5
66		1	1	1	1	2	2	4			1	2	5	4	7	6	3			1	1	1	1	3	5	6
67		1	1	1	1	2	2	5			1	2	5	6	3	4	7			1	1	1	1	3	5	7
68		1	1	1	1	2	2	6			1	2	5	6	3	7	4			1	1	1	1	3	6	6
69		1	1	1	1	2	2	7			1	2	5	6	4	3	7			1	1	1	1	3	6	7
70		1	1	1	1	2	3	1			1	2	5	6	4	7	3			1	1	1	1	3	7	7
71		1	1	1	1	2	3	2			1	2	5	6	7	3	4			1	1	1	1	4	4	4
72		1	1	1	1	2	3	3			1	2	5	6	7	4	3			1	1	1	1	4	4	5
73		1	1	1	1	2	3	4			1	2	5	7	3	4	6			1	1	1	1	4	4	6
74		1	1	1	1	2	3	5			1	2	5	7	3	6	4			1	1	1	1	4	4	7
75		1	1	1	1	2	3	6			1	2	5	7	4	3	6			1	1	1	1	4	5	5
76		1	1	1	1	2	3	7			1	2	5	7	4	6	3			1	1	1	1	4	5	6
77		1	1	1	1	2	4	1			1	2	5	7	6	3	4			1	1	1	1	4	5	7
78		1	1	1	1	2	4	2			1	2	5	7	6	4	3			1	1	1	1	4	6	6
79		1	1	1	1	2	4	3			1	2	6	3	4	5	7			1	1	1	1	4	6	7
80		1	1	1	1	2	4	4			1	2	6	3	4	7	5			1	1	1	1	4	7	7
Sheet2

Cell Formulas
Range	Formula
B1:H1	B1	=SEQUENCE(,7)
B3,T6,K6,B6,O3,G3,X3,T3,K3	B3	=FORMULATEXT(B4)
B4	B4	=PERMUTATIONA(7,7)
G4	G4	=ROWS(B7#)
K4	K4	=PERMUT(7,7)
O4,X4	O4	=ROWS(K7#)
T4	T4	=COMBINA(7,7)
B7:H823549	B7	=ARRANGEMENTS(B1#,"pa",7)
K7:Q5046	K7	=ARRANGEMENTS(B1#,"p",7)
T7:Z1722	T7	=ARRANGEMENTS(B1#,"ca",7)
Dynamic array formulas.

 

[st001]

Yes, I think it’s something wrong with my Excel. I’m on a Mac, and a combination, without repetitions, on a set of length 7, choosing 7 elements kills it. Choosing 6 elements works fine (about 10 seconds).

 

[Xlambda]

Cool challenge MrExcel latest YT : World Cup Group Stage Tiebreaker Odds - 2531
Single cell formula alternative to (10:49) Generating 729 input cells
Formula alternative using only T_PA (permutations with repetitions index patterns formula - 1)

Arrangements.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		=T_PA(3,6)-1							=BYROW(T_PA(3,6)-1,LAMBDA(x,TEXTJOIN("-",,x)))
3		0	0	0	0	0	0		0-0-0-0-0-0
4		0	0	0	0	0	1		0-0-0-0-0-1		checking rows
5		0	0	0	0	0	2		0-0-0-0-0-2		=PERMUTATIONA(3,6)
6		0	0	0	0	1	0		0-0-0-0-1-0		729
7		0	0	0	0	1	1		0-0-0-0-1-1
8		0	0	0	0	1	2		0-0-0-0-1-2		=ROWS(B3#)
9		0	0	0	0	2	0		0-0-0-0-2-0		729
10		0	0	0	0	2	1		0-0-0-0-2-1
11		0	0	0	0	2	2		0-0-0-0-2-2		=ROWS(I3#)
12		0	0	0	1	0	0		0-0-0-1-0-0		729
13		0	0	0	1	0	1		0-0-0-1-0-1
14		0	0	0	1	0	2		0-0-0-1-0-2
15		0	0	0	1	1	0		0-0-0-1-1-0
16		0	0	0	1	1	1		0-0-0-1-1-1
17		0	0	0	1	1	2		0-0-0-1-1-2
18		0	0	0	1	2	0		0-0-0-1-2-0
19		0	0	0	1	2	1		0-0-0-1-2-1
20		0	0	0	1	2	2		0-0-0-1-2-2
21		0	0	0	2	0	0		0-0-0-2-0-0
22		0	0	0	2	0	1		0-0-0-2-0-1
23		0	0	0	2	0	2		0-0-0-2-0-2
24		0	0	0	2	1	0		0-0-0-2-1-0
25		0	0	0	2	1	1		0-0-0-2-1-1
26		0	0	0	2	1	2		0-0-0-2-1-2
27		0	0	0	2	2	0		0-0-0-2-2-0
28		0	0	0	2	2	1		0-0-0-2-2-1
29		0	0	0	2	2	2		0-0-0-2-2-2
30		0	0	1	0	0	0		0-0-1-0-0-0
31		0	0	1	0	0	1		0-0-1-0-0-1
32		0	0	1	0	0	2		0-0-1-0-0-2
33		0	0	1	0	1	0		0-0-1-0-1-0
34		0	0	1	0	1	1		0-0-1-0-1-1
35		0	0	1	0	1	2		0-0-1-0-1-2
36		0	0	1	0	2	0		0-0-1-0-2-0
37		0	0	1	0	2	1		0-0-1-0-2-1
38		0	0	1	0	2	2		0-0-1-0-2-2
39		0	0	1	1	0	0		0-0-1-1-0-0
40		0	0	1	1	0	1		0-0-1-1-0-1
41		0	0	1	1	0	2		0-0-1-1-0-2
42		0	0	1	1	1	0		0-0-1-1-1-0
43		0	0	1	1	1	1		0-0-1-1-1-1
44		0	0	1	1	1	2		0-0-1-1-1-2
45		0	0	1	1	2	0		0-0-1-1-2-0
46		0	0	1	1	2	1		0-0-1-1-2-1
47		0	0	1	1	2	2		0-0-1-1-2-2
48		0	0	1	2	0	0		0-0-1-2-0-0
49		0	0	1	2	0	1		0-0-1-2-0-1
50		0	0	1	2	0	2		0-0-1-2-0-2
51		0	0	1	2	1	0		0-0-1-2-1-0
52		0	0	1	2	1	1		0-0-1-2-1-1
53		0	0	1	2	1	2		0-0-1-2-1-2
54		0	0	1	2	2	0		0-0-1-2-2-0
55		0	0	1	2	2	1		0-0-1-2-2-1
56		0	0	1	2	2	2		0-0-1-2-2-2
57		0	0	2	0	0	0		0-0-2-0-0-0
58		0	0	2	0	0	1		0-0-2-0-0-1
59		0	0	2	0	0	2		0-0-2-0-0-2
60		0	0	2	0	1	0		0-0-2-0-1-0
61		0	0	2	0	1	1		0-0-2-0-1-1
62		0	0	2	0	1	2		0-0-2-0-1-2
63		0	0	2	0	2	0		0-0-2-0-2-0
64		0	0	2	0	2	1		0-0-2-0-2-1
65		0	0	2	0	2	2		0-0-2-0-2-2
66		0	0	2	1	0	0		0-0-2-1-0-0
67		0	0	2	1	0	1		0-0-2-1-0-1
68		0	0	2	1	0	2		0-0-2-1-0-2
69		0	0	2	1	1	0		0-0-2-1-1-0
70		0	0	2	1	1	1		0-0-2-1-1-1
71		0	0	2	1	1	2		0-0-2-1-1-2
72		0	0	2	1	2	0		0-0-2-1-2-0
73		0	0	2	1	2	1		0-0-2-1-2-1
74		0	0	2	1	2	2		0-0-2-1-2-2
75		0	0	2	2	0	0		0-0-2-2-0-0
76		0	0	2	2	0	1		0-0-2-2-0-1
77		0	0	2	2	0	2		0-0-2-2-0-2
78		0	0	2	2	1	0		0-0-2-2-1-0
79		0	0	2	2	1	1		0-0-2-2-1-1
80		0	0	2	2	1	2		0-0-2-2-1-2
81		0	0	2	2	2	0		0-0-2-2-2-0
82		0	0	2	2	2	1		0-0-2-2-2-1
83		0	0	2	2	2	2		0-0-2-2-2-2
84		0	1	0	0	0	0		0-1-0-0-0-0
85		0	1	0	0	0	1		0-1-0-0-0-1
86		0	1	0	0	0	2		0-1-0-0-0-2
87		0	1	0	0	1	0		0-1-0-0-1-0
88		0	1	0	0	1	1		0-1-0-0-1-1
89		0	1	0	0	1	2		0-1-0-0-1-2
90		0	1	0	0	2	0		0-1-0-0-2-0
Bill YT

Cell Formulas
Range	Formula
B2,K11,K8,K5,I2	B2	=FORMULATEXT(B3)
B3:G731	B3	=T_PA(3,6)-1
I3:I731	I3	=BYROW(T_PA(3,6)-1,LAMBDA(x,TEXTJOIN("-",,x)))
K6	K6	=PERMUTATIONA(3,6)
K9	K9	=ROWS(B3#)
K12	K12	=ROWS(I3#)
Dynamic array formulas.