Permutations Dynamic Array Formulas

[lrobbo314]

Using the dynamic array functions, I created a formula that will return permutations, with or without repetitions, based on a range of values you select. I used the lambda function to turn it into an easy-to-use formula.

Just wanted to share because I think it's cool, permutations come up a lot, and I'd like to see what everyone thinks. I'm sure that this formula could be optimized or shortened.

permutationx.xlsm
	A	B	C	D	E	F	G	H
1	Values		Repitions		Results
2	Dog		0		Dog	Cat	Bird	Lizard
3	Cat				Dog	Cat	Lizard	Bird
4	Bird				Dog	Bird	Cat	Lizard
5	Lizard				Dog	Bird	Lizard	Cat
6					Dog	Lizard	Cat	Bird
7					Dog	Lizard	Bird	Cat
8					Cat	Dog	Bird	Lizard
9					Cat	Dog	Lizard	Bird
10					Cat	Bird	Dog	Lizard
11					Cat	Bird	Lizard	Dog
12					Cat	Lizard	Dog	Bird
13					Cat	Lizard	Bird	Dog
14					Bird	Dog	Cat	Lizard
15					Bird	Dog	Lizard	Cat
16					Bird	Cat	Dog	Lizard
17					Bird	Cat	Lizard	Dog
18					Bird	Lizard	Dog	Cat
19					Bird	Lizard	Cat	Dog
20					Lizard	Dog	Cat	Bird
21					Lizard	Dog	Bird	Cat
22					Lizard	Cat	Dog	Bird
23					Lizard	Cat	Bird	Dog
24					Lizard	Bird	Dog	Cat
25					Lizard	Bird	Cat	Dog
Sheet3

Cell Formulas
Range	Formula
E2:H25	E2	=PermutX(A2:A5,C2)
Dynamic array formulas.

permutationx.xlsm
	A	B	C	D	E	F	G
1	Values		Repitions		Results
2	Dog		1		Dog	Dog	Dog
3	Cat				Dog	Dog	Cat
4	Bird				Dog	Dog	Bird
5					Dog	Cat	Dog
6					Dog	Cat	Cat
7					Dog	Cat	Bird
8					Dog	Bird	Dog
9					Dog	Bird	Cat
10					Dog	Bird	Bird
11					Cat	Dog	Dog
12					Cat	Dog	Cat
13					Cat	Dog	Bird
14					Cat	Cat	Dog
15					Cat	Cat	Cat
16					Cat	Cat	Bird
17					Cat	Bird	Dog
18					Cat	Bird	Cat
19					Cat	Bird	Bird
20					Bird	Dog	Dog
21					Bird	Dog	Cat
22					Bird	Dog	Bird
23					Bird	Cat	Dog
24					Bird	Cat	Cat
25					Bird	Cat	Bird
26					Bird	Bird	Dog
27					Bird	Bird	Cat
28					Bird	Bird	Bird
Sheet3

Cell Formulas
Range	Formula
E2:G28	E2	=PermutX(A2:A5,C2)
Dynamic array formulas.

Name Manager Lambda function "PermutX"

=LAMBDA(
    range,
    repetition,
        LET(v,range,
        c,COUNTA(v),
        n,MID(TEXT(BASE(SEQUENCE(PERMUTATIONA(c,c),,0),c),REPT("0",c)),SEQUENCE(,c),1)+1,
        pa,INDEX(v,n),nj,BYROW(pa,LAMBDA(ro,TEXTJOIN(,,ro))),
        f,BYROW(nj,LAMBDA(ro,SUM(IF(LEN(ro)-LEN(SUBSTITUTE(ro,v,"")),1,0))=c)),
        IF(repetition,pa,FILTER(pa,f))
        )
    )

 

[lrobbo314]

Well, it's barely possible. I like challenges, so I took a shot at it. Fluff pretty much nailed the permutations part of it, so I just stole copied that part of the formula. Removing the duplicates was tougher, but I managed. Performance wise, this is a terrible function. 3 and 4 work ok, 5 items take 20 seconds, and I haven't tried anything bigger. BYROW is definitely an improvement.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Values		Repeat						Repeat
2	Dog		0	Dog	Cat	Bird			1	Dog	Dog	Dog
3	Cat			Dog	Bird	Cat				Dog	Dog	Cat
4	Bird			Cat	Dog	Bird				Dog	Dog	Bird
5				Cat	Bird	Dog				Dog	Cat	Dog
6				Bird	Dog	Cat				Dog	Cat	Cat
7				Bird	Cat	Dog				Dog	Cat	Bird
8										Dog	Bird	Dog
9										Dog	Bird	Cat
10										Dog	Bird	Bird
11										Cat	Dog	Dog
12										Cat	Dog	Cat
13										Cat	Dog	Bird
14										Cat	Cat	Dog
15										Cat	Cat	Cat
16										Cat	Cat	Bird
17										Cat	Bird	Dog
18										Cat	Bird	Cat
19										Cat	Bird	Bird
20										Bird	Dog	Dog
21										Bird	Dog	Cat
22										Bird	Dog	Bird
23										Bird	Cat	Dog
24										Bird	Cat	Cat
25										Bird	Cat	Bird
26										Bird	Bird	Dog
27										Bird	Bird	Cat
28										Bird	Bird	Bird
Sheet16

Cell Formulas
Range	Formula
D2:F7,J2:L28	D2	=LET(aa,$A2:$A10,a,FILTER(aa,aa<>""),r,ROWS(a),b,MID(BASE(SEQUENCE(r^r,,0),r,r),SEQUENCE(,r),1)+1,sq,SEQUENCE(r),s,MOD(SMALL(b+SEQUENCE(r^r,,10,10),SEQUENCE(r^r,r)),10),m,MMULT(s,10^sq),ix,INDEX(a,b),IF(C2=1,ix,FILTER(ix,m=SUM(sq*(10^sq)))))
Dynamic array formulas.

As much as I like making formulas like this, they all paddle in comparison performance wise to VBA. I've saved some coffee to do it. It uses recursion which makes my head hurt. But yeah, like about 7 makes the formula take a long time. Even using Fluff's formula.

 

[StephenCrump]

Allowing duplicates, isn't it a simple?

=LET(a,A2:A4,r,ROWS(a),INDEX(a,MOD(SEQUENCE(r^r,,0)/r^SEQUENCE(,r,r-1,-1),r)+1))

Or am I missing something?

We could filter duplicates like this:

=LET(a,A2:A4,r,ROWS(a), n,INT(MOD(SEQUENCE(r^r,,0)/r^SEQUENCE(,r,r-1,-1),r)+1),INDEX(a,FILTER(n,1=BYROW(n,LAMBDA(rw,MAX(FREQUENCY(rw,SEQUENCE(r))))))))

... but I'm thinking there must be a slicker way.