ARRANGEMENTS

=ARRANGEMENTS(a,t,c)

a
array
t
type argument,"pa", permutations with repetitions; "p" permutations w/o repet. ; "ca", combinations with repet. ; "c", combinations w/o repet.
c
number_chosen

Combinatorics study, permutations, combinations w or w/o repetitions, all in one function.

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
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

Excel Formula:
=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
ABCDEFGHIJKLMNO
1Introduction: Combinatorics Excel functions:
21,) 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
42.) 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)!
63.) 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)!)
84.) 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)!)
10What ARRANGEMENTS does is "printing" or extracting all this numbers of permutations or combinations, given the object array "a" and the number_chosen "c",
11for all types "t" above : "pa" for 1.) , "p" for 2.) , "ca" for 3.) ,"c" for 4.)
12
13input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]
14
15function\nc12345678910
161,) PERMUTATIONA749343240116807117649823543576480140353607282475249
172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!
183.) COMBINA728842104629241716300350058008
194.) COMBIN72135352171#NUM!#NUM!#NUM!
20
21Note: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
RangeFormula
C15:L15C15=SEQUENCE(,10)
C16:L16C16=PERMUTATIONA(C13,C15#)
C17:L17C17=PERMUT(C13,C15#)
C18:L18C18=COMBINA(C13,C15#)
C19:L19C19=COMBIN(C13,C15#)
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1a
2ABCDEFGwhen n=nc C=1
3=PERMUTATIONA(7,7)=PERMUT(7,7)=COMBINA(7,7)=COMBIN(7,7)
4823543rws5040rws1716rws1rws
5823543rws5040rws1716rws1rws
6check: =ROWS(A9#)check: =ROWS(I9#)check: =ROWS(Q9#)check: =ROWS(Y9#)
7t,"pa",c,7t,"p",c,7t,"ca",c,7t,"c",c,7
8=ARRANGEMENTS(A2:G2,"pa",7)=ARRANGEMENTS(A2:G2,"p",7)=ARRANGEMENTS(A2:G2,"ca",7)=ARRANGEMENTS(A2:G2,"c",7)
9AAAAAAAABCDEFGAAAAAAAABCDEFG
10AAAAAABABCDEGFAAAAAAB
11AAAAAACABCDFEGAAAAAAC=COMBIN(7,6)
12AAAAAADABCDFGEAAAAAAD7rws
13AAAAAAEABCDGEFAAAAAAE7rws
14AAAAAAFABCDGFEAAAAAAFcheck: =ROWS(Y17#)
15AAAAAAGABCEDFGAAAAAAGt,"c",c,6
16AAAAABAABCEDGFAAAAABB=ARRANGEMENTS(A2:G2,"c",6)
17AAAAABBABCEFDGAAAAABCABCDEF
18AAAAABCABCEFGDAAAAABDABCDEG
19AAAAABDABCEGDFAAAAABEABCDFG
20AAAAABEABCEGFDAAAAABFABCEFG
21AAAAABFABCFDEGAAAAABGABDEFG
22AAAAABGABCFDGEAAAAACCACDEFG
23AAAAACAABCFEDGAAAAACDBCDEFG
24AAAAACBABCFEGDAAAAACE
25AAAAACCABCFGDEAAAAACF=COMBIN(7,5)
26AAAAACDABCFGEDAAAAACG21rws
27AAAAACEABCGDEFAAAAADD21rws
28AAAAACFABCGDFEAAAAADEcheck: =ROWS(Y31#)
29AAAAACGABCGEDFAAAAADFt,"c",c,5
30AAAAADAABCGEFDAAAAADG=ARRANGEMENTS(A2:G2,"c",5)
31AAAAADBABCGFDEAAAAAEEABCDE
32AAAAADCABCGFEDAAAAAEFABCDF
33AAAAADDABDCEFGAAAAAEGABCDG
34AAAAADEABDCEGFAAAAAFFABCEF
35AAAAADFABDCFEGAAAAAFGABCEG
36AAAAADGABDCFGEAAAAAGGABCFG
37AAAAAEAABDCGEFAAAABBBABDEF
38AAAAAEBABDCGFEAAAABBCABDEG
39AAAAAECABDECFGAAAABBDABDFG
40AAAAAEDABDECGFAAAABBEABEFG
41AAAAAEEABDEFCGAAAABBFACDEF
42AAAAAEFABDEFGCAAAABBGACDEG
43AAAAAEGABDEGCFAAAABCCACDFG
44AAAAAFAABDEGFCAAAABCDACEFG
45AAAAAFBABDFCEGAAAABCEADEFG
46AAAAAFCABDFCGEAAAABCFBCDEF
47AAAAAFDABDFECGAAAABCGBCDEG
48AAAAAFEABDFEGCAAAABDDBCDFG
49AAAAAFFABDFGCEAAAABDEBCEFG
50AAAAAFGABDFGECAAAABDFBDEFG
51AAAAAGAABDGCEFAAAABDGCDEFG
52AAAAAGBABDGCFEAAAABEE
53AAAAAGCABDGECFAAAABEF
ARR post 2
Cell Formulas
RangeFormula
D3,Y30,AB25,Y16,AB11,A8,I8,Q8,Y8,AB3,T3,L3D3=FORMULATEXT(D4)
D4D4=PERMUTATIONA(7,7)
D5,AB27,AB13,AB5,T5,L5D5=ROWS(A9#)
L4L4=PERMUT(7,7)
T4T4=COMBINA(7,7)
AB4AB4=COMBIN(7,7)
D6,AB28,AB14,AB6,T6,L6D6=FORMULATEXT(D5)
A9:G823551A9=ARRANGEMENTS(A2:G2,"pa",7)
I9:O5048I9=ARRANGEMENTS(A2:G2,"p",7)
Q9:W1724Q9=ARRANGEMENTS(A2:G2,"ca",7)
Y9:AE9Y9=ARRANGEMENTS(A2:G2,"c",7)
AB12AB12=COMBIN(7,6)
Y17:AD23Y17=ARRANGEMENTS(A2:G2,"c",6)
AB26AB26=COMBIN(7,5)
Y31:AC51Y31=ARRANGEMENTS(A2:G2,"c",5)
Dynamic array formulas.
 
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1Unscramble Words: find 6,5,4 letter words using this letters "aenrttd"Note:Permut is without repetition but we need a double "t"
2=MID("aenrttd",SEQUENCE(,7),1)Is no problem with that because the tool function T_P
3aenrttdcreates the index pattern without repetition, so we can
4have 2 or 1 "t" in our results, but never 3, exactly what we needed
5=PERMUT(7,6)=PERMUT(7,5)=PERMUT(7,4)textjoin byrow
65040rws2520rws840rws=BYROW(A11#,LAMBDA(a,CONCAT(a)))
75040rws2520rws840rwsaenrtt
8check: =ROWS(A11#)check: =ROWS(I11#)check: =ROWS(O11#)aenrtd=BYROW(I11#,LAMBDA(a,CONCAT(a)))
9t,"c",c,6t,"c",c,5t,"c",c,4aenrttaenrt
10=ARRANGEMENTS(A3#,"p",6)=ARRANGEMENTS(A3#,"p",5)=ARRANGEMENTS(A3#,"p",4)aenrtdaenrt=BYROW(O11#,LAMBDA(a,CONCAT(a)))
11aenrttaenrtaenraenrdtaenrdaenr
12aenrtdaenrtaentaenrdtaentraentNow we need a dictionary list
13aenrttaenrdaentaentrtaenttaentto find matches , or enough time to
14aenrtdaentraendaentrdaentdaendread them all ?✌
15aenrdtaenttaernaenttraentraern
16aenrdtaentdaertaenttdaenttaertExamples found:
17aentrtaentraertaentdraentdaert6 letters5 letters4 letters
18aentrdaenttaerdaentdtaendraerdardentantreante
19aenttraentdaetnaentrtaendtaetnattenddaterdare
20aenttdaendraetraentrdaendtaetrrantedrateddarn
21aentdraendtaettaenttraerntaettrattedlaterdart
22aentdtaendtaetdaenttdaerntaetdtetradate
23aentrtaerntaetnaentdraerndaetntradedean
24aentrdaerntaetraentdtaertnaetrtreaddear
25aenttraerndaettaendrtaerttaetttreatdent
26aenttdaertnaetdaendrtaertdaetdtrendearn
27aentdraerttaednaendtraertnaednnear
28aentdtaertdaedraendttaerttaedrneat
29aendrtaertnaedtaendtraertdaedtnerd
30aendrtaerttaedtaendttaerdnaedtnett
31aendtraertdaneraernttaerdtanerrand
32aendttaerdnanetaerntdaerdtanetrate
33aendtraerdtanetaernttaetnranetread
34aendttaerdtanedaerntdaetntanedrend
35aernttaetnranreaerndtaetndanrerent
36aerntdaetntanrtaerndtaetrnanrttare
37aernttaetndanrtaertntaetrtanrttart
38aerntdaetrnanrdaertndaetrdanrdtear
39aerndtaetrtanteaerttnaettnantetend
40aerndtaetrdantraerttdaettrantrtent
41aertntaettnanttaertdnaettdantt
ARR post 3
Cell Formulas
RangeFormula
A2,Z10,I10,O10,A10,X8,V6,P5,K5,C5A2=FORMULATEXT(A3)
A3:G3A3=MID("aenrttd",SEQUENCE(,7),1)
C6C6=PERMUT(7,6)
C7,K7C7=ROWS(A11#)
K6K6=PERMUT(7,5)
P6P6=PERMUT(7,4)
P7P7=ROWS(O11#)
V7:V5046V7=BYROW(A11#,LAMBDA(a,CONCAT(a)))
C8,K8C8=FORMULATEXT(C7)
Q8Q8=FORMULATEXT(P7)
X9:X2528X9=BYROW(I11#,LAMBDA(a,CONCAT(a)))
A11:F5050A11=ARRANGEMENTS(A3#,"p",6)
I11:M2530I11=ARRANGEMENTS(A3#,"p",5)
O11:R850O11=ARRANGEMENTS(A3#,"p",4)
Z11:Z850Z11=BYROW(O11#,LAMBDA(a,CONCAT(a)))
Dynamic array formulas.
 
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Task 1: You forgot your 4 digit pin nr. Task 2: Create 3 members teams out of 5 employees
2What you remember is that has for sure a double 5 and either of these digits 7,1,3 or 8.
3Now you have to try them all, all possibilities. If each try takes 5s, what is the max time to crack it?JohnDanMark=COMBIN(5,3)
4step 1. list of all permutes without repetitions (order is important)InaLola10
5step 2. filtering only the dbl 5 codes
6step 3. calculate max timewe use combinations without repetitions, order is not important
7
8135578=ARRANGEMENTS(U3:W4,"c",3)=APP2H(SEQUENCE(10),U9#)
9JohnDanMark1JohnDanMark
101.)2.)=FILTER(B12#,BYROW(B12#=5,LAMBDA(a,SUM(--a)))=2)JohnDanIna2JohnDanIna
11=ARRANGEMENTS(A8:F8,"p",4)JohnDanLola3JohnDanLola
12135513553.)=ROWS(H12#)JohnMarkIna4JohnMarkIna
1313571355144codes to checkJohnMarkLola5JohnMarkLola
1413581535t=144*5s=720s=12minJohnInaLola6JohnInaLola
1513551553DanMarkIna7DanMarkIna
1613571557DanMarkLola8DanMarkLola
1713581558DanInaLola9DanInaLola
1813751575MarkInaLola10MarkInaLola
1913751585
2013781535Task 3: You have 4 compartments 100 shelves to set on 3 dif color T-shirts
2113851553Is possible to set them without repeating the color pattern of any shelve?
2213851557We use permutes with rept. (can handle number chosen> nr of objects)
2313871558
2415351575red=ARRANGEMENTS(Q24:Q26,"pa",4)
2515371585whiteredredredred
2615381755blueredredredwhite
2715531755redredredblue
2815571855=PERMUTATIONA(3,4)redredwhitered
291558185581redredwhitewhite
3015733155=ROWS(U25#)redredwhiteblue
311575315581redredbluered
3215783515redredbluewhite
331583355181<100 so, let's do itredredblueblue
3415853557redwhiteredred
3515873558ARRANGEMENTS error handlingredwhiteredwhite
3615353575redwhiteredblue
3715373585=ARRANGEMENTS(Q24:Q26,"ps",3)redwhitewhitered
3815383515check typeredwhitewhitewhite
3915533551redwhitewhiteblue
4015573557=ARRANGEMENTS(Q27:Q29,"p",4)redwhitebluered
4115583558nr chosen>n !redwhitebluewhite
4215733575redwhiteblueblue
4315753585redblueredred
4415783755redblueredwhite
4515833755redblueredblue
ARR post 4
Cell Formulas
RangeFormula
Y3,N40,N37,Q30,U24,N12,Y8Y3=FORMULATEXT(Y4)
Y4Y4=COMBIN(5,3)
S8S8=FORMULATEXT(U9)
U9:W18U9=ARRANGEMENTS(U3:W4,"c",3)
Y9:AB18Y9=APP2H(SEQUENCE(10),U9#)
H10H10=FORMULATEXT(H12)
A11,P28A11=FORMULATEXT(B12)
B12:E371B12=ARRANGEMENTS(A8:F8,"p",4)
H12:K155H12=FILTER(B12#,BYROW(B12#=5,LAMBDA(a,SUM(--a)))=2)
N13N13=ROWS(H12#)
U25:X105U25=ARRANGEMENTS(Q24:Q26,"pa",4)
Q29Q29=PERMUTATIONA(3,4)
Q31Q31=ROWS(U25#)
N38N38=ARRANGEMENTS(Q24:Q26,"ps",3)
N41N41=ARRANGEMENTS(Q27:Q29,"p",4)
Dynamic array formulas.
 
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLM
1Table of PERMUTATIONA(n,r), conditional formatted to see for what values n,r, if we print them, will fit in a spreadsheet hight (1048576 rows)
2=PERMUTATIONA(B5#,C4#)
3PERMUTATIONAr
412345678910
5n11111111111
622481632641282565121024
73392781243729218765611968359049
84416642561024409616384655362621441048576
955251256253125156257812539062519531259765625
106636216129677764665627993616796161007769660466176
117749343240116807117649823543576480140353607282475249
1288645124096327682621442097152167772161342177281073741824
1399817296561590495314414782969430467213874204893486784401
141010100100010000100000100000010000000100000000100000000010000000000
151111121133114641161051177156119487171214358881235794769125937424601
161212144172820736248832298598435831808429981696515978035261917364224
171313169219728561371293482680962748517815730721106044993731.37858E+11
18141419627443841653782475295361054135041475789056206610467842.89255E+11
191515225337550625759375113906251708593752562890625384433593755.7665E+11
2016162564096655361048576167772162684354564294967296687194767361.09951E+12
21171728949138352114198572413756941033867369757574411.18588E+112.01599E+12
2218183245832104976188956834012224612220032110199605761.98359E+113.57047E+12
2319193616859130321247609947045881893871739169835630413.22688E+116.13107E+12
24202040080001600003200000640000001280000000256000000005.12E+111.024E+13
25212144192611944814084101857661211801088541378228593617.9428E+111.66799E+13
2622224841064823425651536321133799042494357888548758735361.20727E+122.65599E+13
2723235291216727984164363431480358893404825447783109852811.80115E+124.14265E+13
28242457613824331776796262419110297645864714241.10075E+112.64181E+126.34034E+13
29252562515625390625976562524414062561035156251.52588E+113.8147E+129.53674E+13
302626676175764569761188137630891577680318101762.08827E+115.4295E+121.41167E+14
3127277291968353144114348907387420489104603532032.8243E+117.6256E+122.05891E+14
3228287842195261465617210368481890304134929285123.77802E+111.05785E+132.96197E+14
3329298412438970728120511149594823321172498763095.00246E+111.45071E+134.20707E+14
3430309002700081000024300000729000000218700000006.561E+111.9683E+135.9049E+14
35
T_PA post i
Cell Formulas
RangeFormula
C2C2=FORMULATEXT(C5)
C4:L4C4=SEQUENCE(,10)
B5:B34B5=SEQUENCE(30)
C5:L34C5=PERMUTATIONA(B5#,C4#)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:L34Expression=C5<=1048576textNO
 
T_PA tool lambda, creates index patterns for permutations with repetitions.
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1highest n=c values that fit in a spreadsheet, n=c=7 (823543 rows)Task: Print all pos.permut.(with repet.) of "a", nr.chosen 3,2
2n,7,c,7 =823543 rowsn,10,c,6 =1M rowsn,4,c,3 =64rowsa
3=PERMUTATIONA(7,7)=PERMUTATIONA(10,6)=PERMUTATIONA(4,3)ABCD
4823543100000064
5
6=T_PA(7,7)=T_PA(10,6)=T_PA(4,3)=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),3))
71111111111111111AAA
81111112111112112AAB
91111113111113113AAC
101111114111114114AAD
111111115111115121ABA=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),2))
121111116111116122ABBAA
131111117111117123ABCAB
141111121111118124ABDAC
151111122111119131ACAAD
1611111231111110132ACBBA
171111124111121133ACCBB
181111125111122134ACDBC
191111126111123141ADABD
201111127111124142ADBCA
211111131111125143ADCCB
221111132111126144ADDCC
231111133111127211BAACD
241111134111128212BABDA
251111135111129213BACDB
2611111361111210214BADDC
271111137111131221BBADD16 rws
281111141111132222BBB
291111142111133223BBCcheck
301111143check111134check224BBD=PERMUTATIONA(4,2)
311111144=ROWS(B7#)111135=ROWS(J7#)231BCA16
3211111458235431111361000000232BCB
331111146111137233BCC
341111147down to111138down to234BCDdown to
351111151823543 rws1111391M rws241BDA64 rws
361111152⬇⬇⬇1111310⬇⬇⬇242BDB⬇⬇⬇
371111153111141243BDC
T_PA post
Cell Formulas
RangeFormula
B3,P31,I31,AA30,V6,Q6,J6,B6,J3,Q3B3=FORMULATEXT(B4)
B4B4=PERMUTATIONA(7,7)
J4J4=PERMUTATIONA(10,6)
Q4Q4=PERMUTATIONA(4,3)
B7:H823549B7=T_PA(7,7)
J7:O1000006J7=T_PA(10,6)
Q7:S70Q7=T_PA(4,3)
V7:X70V7=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),3))
Z11Z11=FORMULATEXT(AA12)
AA12:AB27AA12=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),2))
AA31AA31=PERMUTATIONA(4,2)
I32I32=ROWS(B7#)
P32P32=ROWS(J7#)
Dynamic array formulas.
 
T_P tool lambda, filters an array, keeping only the rows with no dups. Can be used as a standalone function. Applied to T_PA, will create the index patterns for permutations without repetitions.
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1=PERMUTATIONA(7,7)=PERMUT(7,7)=PERMUT(10,6)=PERMUT(4,3)Task: Print all pos.permut.(no repet.) of "a", nr.chosen 3,2
2823543504015120024aABCD
3n,7,c,7n,7,c,7n,10,c,6n,4,c,3
4=T_PA(7,7)=T_P(A5#)=T_P(T_PA(10,6))=T_P(T_PA(4,3))=INDEX(AC2:AF2,T_P(T_PA(4,3)))
511111111234567123456123ABC
611111121234576123457124ABD
711111131234657123458132ACB=INDEX(AC2:AF2,T_P(T_PA(4,2)))
811111141234675123459134ACDAB
9111111512347561234510142ADBAC
1011111161234765123465143ADCAD
1111111171235467123467213BACBA
1211111211235476123468214BADBC
1311111221235647123469231BCABD
14111112312356741234610234BCDCA
1511111241235746123475241BDACB
1611111251235764123476243BDCCD
1711111261236457123478312CABDA
1811111271236475123479314CADDB
19111113112365471234710321CBADC12 rws
2011111321236574123485324CBD
2111111331236745123486341CDAcheck
2211111341236754123487342CDB=PERMUT(4,2)
2311111351237456123489412DAB12
24111113612374651234810413DAC
2511111371237546123495421DBA
2611111411237564123496423DBC
2711111421237645123497431DCA
281111143123765412349843224 rwsDCB24 rws
29111114412435671234910
301111145check1243576check1234105check
311111146=ROWS(A5#)1243657=ROWS(I5#)1234106=ROWS(Q5#)
321111147823543124367550401234107151200
33111115112437561234108
341111152down to1243765down to1234109down to
351111153823543 rws12453675040 rws123546151200 rws
361111154⬇⬇⬇1245376⬇⬇⬇123547⬇⬇⬇
3711111551245637123548
T_P post
Cell Formulas
RangeFormula
A1,W31,P31,H31,AH22,AH7,AC4,X4,Q4,I4,A4,X1,Q1,I1A1=FORMULATEXT(A2)
A2A2=PERMUTATIONA(7,7)
I2I2=PERMUT(7,7)
Q2Q2=PERMUT(10,6)
X2X2=PERMUT(4,3)
A5:G823547A5=T_PA(7,7)
I5:O5044I5=T_P(A5#)
Q5:V151204Q5=T_P(T_PA(10,6))
X5:Z28X5=T_P(T_PA(4,3))
AC5:AE28AC5=INDEX(AC2:AF2,T_P(T_PA(4,3)))
AH8:AI19AH8=INDEX(AC2:AF2,T_P(T_PA(4,2)))
AH23AH23=PERMUT(4,2)
H32,P32H32=ROWS(A5#)
W32W32=ROWS(Q5#)
Dynamic array formulas.
 
T_CA filters an array, keeping only the rows that are in ascending order. We do this because, for combinations, order is not important. Can be used as a standalone function. Applied to T_PA, will create the index patterns for combinations with repetitions, applied to T_P, (next post) creates the index patterns for combinations without repetitions. Same function applied to 2 different arrays.
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1=PERMUTATIONA(7,7)=COMBINA(7,7)=COMBINA(10,6)=COMBINA(4,3)Task: Print all pos.combin.(with repet.) of "a", nr.chosen 3,2
28235431716500520aABCD
3n,7,c,7n,7,c,7n,10,c,6n,4,c,3
4=T_PA(7,7)=T_CA(T_PA(7,7))=T_CA(T_PA(10,6))=T_CA(T_PA(4,3))=INDEX(AC2:AF2,T_CA(T_PA(4,3)))
511111111111111111111111AAA
611111121111112111112112AAB
711111131111113111113113AAC=INDEX(AC2:AF2,T_CA(T_PA(4,2)))
811111141111114111114114AADAA
911111151111115111115122ABBAB
1011111161111116111116123ABCAC
1111111171111117111117124ABDAD
1211111211111122111118133ACCBB
1311111221111123111119134ACDBC
14111112311111241111110144ADDBD
1511111241111125111122222BBBCC
1611111251111126111123223BBCCD
1711111261111127111124224BBDDD12 rws
1811111271111133111125233BCC
1911111311111134111126234BCDcheck
2011111321111135111127244BDD=PERMUT(4,2)
2111111331111136111128333CCC12
2211111341111137111129334CCD
23111113511111441111210344CDD
241111136111114511113344420 rwsDDD20 rws
2511111371111146111134
261111141check1111147check111135check
271111142=ROWS(A5#)1111155=ROWS(I5#)111136=ROWS(Q5#)
281111143823543111115617161111375005
2911111441111157111138
301111145down to1111166down to111139down to
311111146823543 rws11111671716 rws11113105005 rws
321111147⬇⬇⬇1111177⬇⬇⬇111144⬇⬇⬇
3311111511111222111145
T_CA post
Cell Formulas
RangeFormula
A1,W27,P27,H27,AG20,X4,AB4,Q4,I4,A4,X1,Q1,I1A1=FORMULATEXT(A2)
A2A2=PERMUTATIONA(7,7)
I2I2=COMBINA(7,7)
Q2Q2=COMBINA(10,6)
X2X2=COMBINA(4,3)
A5:G823547A5=T_PA(7,7)
I5:O1720I5=T_CA(T_PA(7,7))
Q5:V5009Q5=T_CA(T_PA(10,6))
X5:Z24X5=T_CA(T_PA(4,3))
AB5:AD24AB5=INDEX(AC2:AF2,T_CA(T_PA(4,3)))
AF7AF7=FORMULATEXT(AG8)
AG8:AH17AG8=INDEX(AC2:AF2,T_CA(T_PA(4,2)))
AG21AG21=PERMUT(4,2)
H28,P28H28=ROWS(A5#)
W28W28=ROWS(Q5#)
Dynamic array formulas.
 
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1=PERMUTATIONA(10,6)=PERMUT(10,6)=COMBIN(10,6)=COMBIN(5,3)Task: Print all pos.combin.(no repet.) of "a", nr.chosen 3
210000001E+0615120015120021021010aABCDEF
3n,10,c,6checkn,10,c,6checkn,10,c,6checkn,5,c,3
4=T_PA(10,6)=T_P(T_PA(10,6))=T_CA(T_P(T_PA(10,6)))=T_CA(T_P(T_PA(5,3)))=INDEX(AB2:AG2,T_CA(T_P(T_PA(5,3))))
5111111123456123456123ABC
6111112123457123457124ABD
7111113123458123458125ABE
8111114123459123459134ACD
911111512345101234510135ACE
10111116123465123467145ADE
11111117123467123468234BCD
12111118123468123469235BCE
131111191234691234610245BDE
141111110123461012347834510 rwsCDE10 rws
15111121123475123479
161111221234761234710
17111123123478123489
181111241234791234810
1911112512347101234910
20111126123485123567
21111127123486123568
22111128123487123569
231111291234891235610
2411112101234810123578
25111131123495123579
261111321234961235710
27111133123497123589
281111341234981235810
2911113512349101235910
301111361234105123678
311111371234106123679
3211113812341071236710
T_CA post 2
Cell Formulas
RangeFormula
A1,H1,O4,V4,AA4,H4,A4,O1A1=FORMULATEXT(A2)
U1U1=FORMULATEXT(V2)
A2A2=PERMUTATIONA(10,6)
E2,S2,L2E2=ROWS(A5#)
H2H2=PERMUT(10,6)
O2O2=COMBIN(10,6)
V2V2=COMBIN(5,3)
A5:F1000004A5=T_PA(10,6)
H5:M151204H5=T_P(T_PA(10,6))
O5:T214O5=T_CA(T_P(T_PA(10,6)))
V5:X14V5=T_CA(T_P(T_PA(5,3)))
AA5:AC14AA5=INDEX(AB2:AG2,T_CA(T_P(T_PA(5,3))))
Dynamic array formulas.
 
The 2 recursive lambda tools as standalone functions.
T_P - filters only rows of an array that have no dups
T_CA - filters only the rows of an array that are in ascending order
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1sample
21234no dups
31223dups
4abcd1 2 3 4ascending order
5abcc1 3 2 4not ascending order
64123=T_CA(A2:D12)
7cabd=T_P(A2:D12)1234
8aaad12341223
9ab12abcdabcd
1012ab4123abcc
11dcaccabdaaad
121334ab1212ab
1312ab1334
14
15=T_CA(F8#)=T_P(K7#)
1612341234
17abcdabcd
1812ab12ab
19
20T_CA(T_P(a))=T_P(T_CA(a))
21
ARR T_P T_CA
Cell Formulas
RangeFormula
K6,K15,F15,F7K6=FORMULATEXT(K7)
K7:N13K7=T_CA(A2:D12)
F8:I13F8=T_P(A2:D12)
F16:I18F16=T_CA(F8#)
K16:N18K16=T_P(K7#)
Dynamic array formulas.
 

Forum statistics

Threads
1,223,875
Messages
6,175,114
Members
452,613
Latest member
amorehouse

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