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

 

[Fluff]

Another option for that

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Values		Repitions		Results
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
Data

Cell Formulas
Range	Formula
E2:G7	E2	=LET(a,A2:A4,r,ROWS(a),b,MID(BASE(SEQUENCE(r^r,,0),r,r),SEQUENCE(,r),1)+1,c,BYROW(b,LAMBDA(br,SUM(--(MMULT(--(br=TRANSPOSE(br)),SEQUENCE(r,,,0))=1))=r)),d,INDEX(a,b),IF(C2,d,FILTER(d,c)))
I2:K28	I2	=LET(a,A2:A4,r,ROWS(a),b,MID(BASE(SEQUENCE(r^r,,0),r,r),SEQUENCE(,r),1)+1,c,BYROW(b,LAMBDA(br,SUM(--(MMULT(--(br=TRANSPOSE(br)),SEQUENCE(r,,,0))=1))=r)),d,INDEX(a,b),IF(H2,d,FILTER(d,c)))
Dynamic array formulas.

 

[lrobbo314]

I like this @Fluff. One blind spot I've always had with Excel formulas is the MMULT function. Stepping through I see what is going on and think I have a better understanding of how to use it. Might be a while before I integrate it in a formula organically, but thanks.

 

[Fluff]

I never used to understand it, but saw a couple of posts (I think by Eric W) where it was explained & I've found it very useful.

 

[Fluff]

Realised there is no need for the MMULT, so a shorter version

=LET(a,A2:A5,r,ROWS(a),b,MID(BASE(SEQUENCE(r^r,,0),r,r),SEQUENCE(,r),1)+1,c,BYROW(b,LAMBDA(br,SUMPRODUCT(--(br=TRANSPOSE(br)))=r)),d,INDEX(a,b),IF(I2,d,FILTER(d,c)))

 

[lrobbo314]

Realised there is no need for the MMULT, so a shorter version

=LET(a,A2:A5,r,ROWS(a),b,MID(BASE(SEQUENCE(r^r,,0),r,r),SEQUENCE(,r),1)+1,c,BYROW(b,LAMBDA(br,SUMPRODUCT(--(br=TRANSPOSE(br)))=r)),d,INDEX(a,b),IF(I2,d,FILTER(d,c)))

Very cool! Same idea as the MMULT, just a more direct way. Nice.

 

[Eric W]

I saw a post on another Excel site once that said "Can you do all kinds of formulas, including SUMPRODUCT and array functions, but just can't figure out MMULT?" I took that as a challenge to figure it out. It's very useful, but sometimes there are better alternatives, like here.

I haven't tried my hand at combination functions and dynamic arrays yet. I finally got LAMBDA, but I'm still missing BYROW, so I'm still behind the curve.

 

[lrobbo314]

I saw a post on another Excel site once that said "Can you do all kinds of formulas, including SUMPRODUCT and array functions, but just can't figure out MMULT?" I took that as a challenge to figure it out. It's very useful, but sometimes there are better alternatives, like here.

I haven't tried my hand at combination functions and dynamic arrays yet. I finally got LAMBDA, but I'm still missing BYROW, so I'm still behind the curve.

I did something similar today, looking at examples of using MMULT. Finally going to force myself to learn it.

Recently got the update for BYROW, BYCOL, etc. and getting to know those. Don't know if this would be possible without them.

 

[Fluff]

Don't know if this would be possible without them.

If it is I don't know how, I tried to do it without BYROW & failed.

 

[Eric W]

Recently got the update for BYROW, BYCOL, etc. and getting to know those. Don't know if this would be possible without them.

If it is I don't know how, I tried to do it without BYROW & failed.

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.