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



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNO1Introduction: 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^nc42.) 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.)1213input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]1415function\nc12345678910161,) PERMUTATIONA749343240116807117649823543576480140353607282475249172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!183.) COMBINA728842104629241716300350058008194.) COMBIN72135352171#NUM!#NUM!#NUM!2021Note:It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects22ARR post 1Cell FormulasRangeFormulaC15: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.


----------



## Xlambda (Aug 17, 2021)

LAMBDA 1.1.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG1a2ABCDEFGwhen n=nc C=13=PERMUTATIONA(7,7)=PERMUT(7,7)=COMBINA(7,7)=COMBIN(7,7)4823543rws5040rws1716rws1rws5823543rws5040rws1716rws1rws6check: =ROWS(A9#)check: =ROWS(I9#)check: =ROWS(Q9#)check: =ROWS(Y9#)7t,"pa",c,7t,"p",c,7t,"ca",c,7t,"c",c,78=ARRANGEMENTS(A2:G2,"pa",7)=ARRANGEMENTS(A2:G2,"p",7)=ARRANGEMENTS(A2:G2,"ca",7)=ARRANGEMENTS(A2:G2,"c",7)9AAAAAAAABCDEFGAAAAAAAABCDEFG10AAAAAABABCDEGFAAAAAAB11AAAAAACABCDFEGAAAAAAC=COMBIN(7,6)12AAAAAADABCDFGEAAAAAAD7rws13AAAAAAEABCDGEFAAAAAAE7rws14AAAAAAFABCDGFEAAAAAAFcheck: =ROWS(Y17#)15AAAAAAGABCEDFGAAAAAAGt,"c",c,616AAAAABAABCEDGFAAAAABB=ARRANGEMENTS(A2:G2,"c",6)17AAAAABBABCEFDGAAAAABCABCDEF18AAAAABCABCEFGDAAAAABDABCDEG19AAAAABDABCEGDFAAAAABEABCDFG20AAAAABEABCEGFDAAAAABFABCEFG21AAAAABFABCFDEGAAAAABGABDEFG22AAAAABGABCFDGEAAAAACCACDEFG23AAAAACAABCFEDGAAAAACDBCDEFG24AAAAACBABCFEGDAAAAACE25AAAAACCABCFGDEAAAAACF=COMBIN(7,5)26AAAAACDABCFGEDAAAAACG21rws27AAAAACEABCGDEFAAAAADD21rws28AAAAACFABCGDFEAAAAADEcheck: =ROWS(Y31#)29AAAAACGABCGEDFAAAAADFt,"c",c,530AAAAADAABCGEFDAAAAADG=ARRANGEMENTS(A2:G2,"c",5)31AAAAADBABCGFDEAAAAAEEABCDE32AAAAADCABCGFEDAAAAAEFABCDF33AAAAADDABDCEFGAAAAAEGABCDG34AAAAADEABDCEGFAAAAAFFABCEF35AAAAADFABDCFEGAAAAAFGABCEG36AAAAADGABDCFGEAAAAAGGABCFG37AAAAAEAABDCGEFAAAABBBABDEF38AAAAAEBABDCGFEAAAABBCABDEG39AAAAAECABDECFGAAAABBDABDFG40AAAAAEDABDECGFAAAABBEABEFG41AAAAAEEABDEFCGAAAABBFACDEF42AAAAAEFABDEFGCAAAABBGACDEG43AAAAAEGABDEGCFAAAABCCACDFG44AAAAAFAABDEGFCAAAABCDACEFG45AAAAAFBABDFCEGAAAABCEADEFG46AAAAAFCABDFCGEAAAABCFBCDEF47AAAAAFDABDFECGAAAABCGBCDEG48AAAAAFEABDFEGCAAAABDDBCDFG49AAAAAFFABDFGCEAAAABDEBCEFG50AAAAAFGABDFGECAAAABDFBDEFG51AAAAAGAABDGCEFAAAABDGCDEFG52AAAAAGBABDGCFEAAAABEE53AAAAAGCABDGECFAAAABEFARR post 2Cell FormulasRangeFormulaD3,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.


----------



## Xlambda (Aug 17, 2021)

LAMBDA 1.1.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE1Unscramble 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_P3aenrttdcreates the index pattern without repetition, so we can4have 2 or 1 "t" in our results, but never 3, exactly what we needed5=PERMUT(7,6)=PERMUT(7,5)=PERMUT(7,4)textjoin byrow65040rws2520rws840rws=BYROW(A11#,LAMBDA(a,CONCAT(a)))75040rws2520rws840rwsaenrtt8check: =ROWS(A11#)check: =ROWS(I11#)check: =ROWS(O11#)aenrtd=BYROW(I11#,LAMBDA(a,CONCAT(a)))9t,"c",c,6t,"c",c,5t,"c",c,4aenrttaenrt10=ARRANGEMENTS(A3#,"p",6)=ARRANGEMENTS(A3#,"p",5)=ARRANGEMENTS(A3#,"p",4)aenrtdaenrt=BYROW(O11#,LAMBDA(a,CONCAT(a)))11aenrttaenrtaenraenrdtaenrdaenr12aenrtdaenrtaentaenrdtaentraentNow we need a dictionary list13aenrttaenrdaentaentrtaenttaentto find matches , or enough time to14aenrtdaentraendaentrdaentdaendread them all ?✌15aenrdtaenttaernaenttraentraern16aenrdtaentdaertaenttdaenttaertExamples found:17aentrtaentraertaentdraentdaert6 letters5 letters4 letters18aentrdaenttaerdaentdtaendraerdardentantreante19aenttraentdaetnaentrtaendtaetnattenddaterdare20aenttdaendraetraentrdaendtaetrrantedrateddarn21aentdraendtaettaenttraerntaettrattedlaterdart22aentdtaendtaetdaenttdaerntaetdtetradate23aentrtaerntaetnaentdraerndaetntradedean24aentrdaerntaetraentdtaertnaetrtreaddear25aenttraerndaettaendrtaerttaetttreatdent26aenttdaertnaetdaendrtaertdaetdtrendearn27aentdraerttaednaendtraertnaednnear28aentdtaertdaedraendttaerttaedrneat29aendrtaertnaedtaendtraertdaedtnerd30aendrtaerttaedtaendttaerdnaedtnett31aendtraertdaneraernttaerdtanerrand32aendttaerdnanetaerntdaerdtanetrate33aendtraerdtanetaernttaetnranetread34aendttaerdtanedaerntdaetntanedrend35aernttaetnranreaerndtaetndanrerent36aerntdaetntanrtaerndtaetrnanrttare37aernttaetndanrtaertntaetrtanrttart38aerntdaetrnanrdaertndaetrdanrdtear39aerndtaetrtanteaerttnaettnantetend40aerndtaetrdantraerttdaettrantrtent41aertntaettnanttaertdnaettdanttARR post 3Cell FormulasRangeFormulaA2,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.


----------



## Xlambda (Aug 17, 2021)

LAMBDA 1.1.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1Task 1: You forgot your 4 digit pin nr. Task 2: Create 3 members teams out of 5 employees2What 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)InaLola105step 2. filtering only the dbl 5 codes 6step 3. calculate max timewe use combinations without repetitions, order is not important78135578=ARRANGEMENTS(U3:W4,"c",3)=APP2H(SEQUENCE(10),U9#)9JohnDanMark1JohnDanMark101.)2.)=FILTER(B12#,BYROW(B12#=5,LAMBDA(a,SUM(--a)))=2)JohnDanIna2JohnDanIna11=ARRANGEMENTS(A8:F8,"p",4)JohnDanLola3JohnDanLola12135513553.)=ROWS(H12#)JohnMarkIna4JohnMarkIna1313571355144codes to checkJohnMarkLola5JohnMarkLola1413581535t=144*5s=720s=12minJohnInaLola6JohnInaLola1513551553DanMarkIna7DanMarkIna1613571557DanMarkLola8DanMarkLola1713581558DanInaLola9DanInaLola1813751575MarkInaLola10MarkInaLola19137515852013781535Task 3: You have 4 compartments 100 shelves to set on 3 dif color T-shirts2113851553Is possible to set them without repeating the color pattern of any shelve?2213851557We use permutes with rept. (can handle number chosen> nr of objects)23138715582415351575red=ARRANGEMENTS(Q24:Q26,"pa",4)2515371585whiteredredredred2615381755blueredredredwhite2715531755redredredblue2815571855=PERMUTATIONA(3,4)redredwhitered291558185581redredwhitewhite3015733155=ROWS(U25#)redredwhiteblue311575315581redredbluered3215783515redredbluewhite331583355181<100 so, let's do itredredblueblue3415853557redwhiteredred3515873558ARRANGEMENTS error handlingredwhiteredwhite3615353575redwhiteredblue3715373585=ARRANGEMENTS(Q24:Q26,"ps",3)redwhitewhitered3815383515check typeredwhitewhitewhite3915533551redwhitewhiteblue4015573557=ARRANGEMENTS(Q27:Q29,"p",4)redwhitebluered4115583558nr chosen>n !redwhitebluewhite4215733575redwhiteblueblue4315753585redblueredred4415783755redblueredwhite4515833755redblueredblueARR post 4Cell FormulasRangeFormulaY3,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.


----------



## Xlambda (Aug 17, 2021)

LAMBDA 1.1.1.xlsxABCDEFGHIJKLM1Table 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#)3PERMUTATIONAr4123456789105n11111111111622481632641282565121024733927812437292187656119683590498441664256102440961638465536262144104857695525125625312515625781253906251953125976562510663621612967776466562799361679616100776966046617611774934324011680711764982354357648014035360728247524912886451240963276826214420971521677721613421772810737418241399817296561590495314414782969430467213874204893486784401141010100100010000100000100000010000000100000000100000000010000000000151111121133114641161051177156119487171214358881235794769125937424601161212144172820736248832298598435831808429981696515978035261917364224171313169219728561371293482680962748517815730721106044993731.37858E+1118141419627443841653782475295361054135041475789056206610467842.89255E+11191515225337550625759375113906251708593752562890625384433593755.7665E+112016162564096655361048576167772162684354564294967296687194767361.09951E+1221171728949138352114198572413756941033867369757574411.18588E+112.01599E+122218183245832104976188956834012224612220032110199605761.98359E+113.57047E+122319193616859130321247609947045881893871739169835630413.22688E+116.13107E+1224202040080001600003200000640000001280000000256000000005.12E+111.024E+1325212144192611944814084101857661211801088541378228593617.9428E+111.66799E+132622224841064823425651536321133799042494357888548758735361.20727E+122.65599E+132723235291216727984164363431480358893404825447783109852811.80115E+124.14265E+1328242457613824331776796262419110297645864714241.10075E+112.64181E+126.34034E+1329252562515625390625976562524414062561035156251.52588E+113.8147E+129.53674E+13302626676175764569761188137630891577680318101762.08827E+115.4295E+121.41167E+143127277291968353144114348907387420489104603532032.8243E+117.6256E+122.05891E+143228287842195261465617210368481890304134929285123.77802E+111.05785E+132.96197E+143329298412438970728120511149594823321172498763095.00246E+111.45071E+134.20707E+143430309002700081000024300000729000000218700000006.561E+111.9683E+135.9049E+1435T_PA post iCell FormulasRangeFormulaC2C2=FORMULATEXT(C5)C4:L4C4=SEQUENCE(,10)B5:B34B5=SEQUENCE(30)C5:L34C5=PERMUTATIONA(B5#,C4#)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueC5:L34Expression=C5<=1048576textNO


----------



## Xlambda (Aug 17, 2021)

*T_PA tool lambda, creates index patterns for permutations with repetitions.
LAMBDA 1.1.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF1highest 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,22n,7,c,7 =823543 rowsn,10,c,6 =1M rowsn,4,c,3 =64rowsa3=PERMUTATIONA(7,7)=PERMUTATIONA(10,6)=PERMUTATIONA(4,3)ABCD482354310000006456=T_PA(7,7)=T_PA(10,6)=T_PA(4,3)=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),3))71111111111111111AAA81111112111112112AAB91111113111113113AAC101111114111114114AAD111111115111115121ABA=INDEX(V3:Y3,T_PA(COUNTA(V3:Y3),2))121111116111116122ABBAA131111117111117123ABCAB141111121111118124ABDAC151111122111119131ACAAD1611111231111110132ACBBA171111124111121133ACCBB181111125111122134ACDBC191111126111123141ADABD201111127111124142ADBCA211111131111125143ADCCB221111132111126144ADDCC231111133111127211BAACD241111134111128212BABDA251111135111129213BACDB2611111361111210214BADDC271111137111131221BBADD16 rws281111141111132222BBB291111142111133223BBCcheck301111143check111134check224BBD=PERMUTATIONA(4,2)311111144=ROWS(B7#)111135=ROWS(J7#)231BCA163211111458235431111361000000232BCB331111146111137233BCC341111147down to111138down to234BCDdown to351111151823543 rws1111391M rws241BDA64 rws361111152⬇⬇⬇1111310⬇⬇⬇242BDB⬇⬇⬇371111153111141243BDCT_PA postCell FormulasRangeFormulaB3,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.*


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL1=PERMUTATIONA(7,7)=PERMUT(7,7)=PERMUT(10,6)=PERMUT(4,3)Task: Print all pos.permut.(no repet.) of "a", nr.chosen 3,22823543504015120024aABCD3n,7,c,7n,7,c,7n,10,c,6n,4,c,34=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)))511111111234567123456123ABC611111121234576123457124ABD711111131234657123458132ACB=INDEX(AC2:AF2,T_P(T_PA(4,2)))811111141234675123459134ACDAB9111111512347561234510142ADBAC1011111161234765123465143ADCAD1111111171235467123467213BACBA1211111211235476123468214BADBC1311111221235647123469231BCABD14111112312356741234610234BCDCA1511111241235746123475241BDACB1611111251235764123476243BDCCD1711111261236457123478312CABDA1811111271236475123479314CADDB19111113112365471234710321CBADC12 rws2011111321236574123485324CBD2111111331236745123486341CDAcheck2211111341236754123487342CDB=PERMUT(4,2)2311111351237456123489412DAB1224111113612374651234810413DAC2511111371237546123495421DBA2611111411237564123496423DBC2711111421237645123497431DCA281111143123765412349843224 rwsDCB24 rws29111114412435671234910301111145check1243576check1234105check311111146=ROWS(A5#)1243657=ROWS(I5#)1234106=ROWS(Q5#)32111114782354312436755040123410715120033111115112437561234108341111152down to1243765down to1234109down to351111153823543 rws12453675040 rws123546151200 rws361111154⬇⬇⬇1245376⬇⬇⬇123547⬇⬇⬇3711111551245637123548T_P postCell FormulasRangeFormulaA1,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.


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM1=PERMUTATIONA(7,7)=COMBINA(7,7)=COMBINA(10,6)=COMBINA(4,3)Task: Print all pos.combin.(with repet.) of "a", nr.chosen 3,228235431716500520aABCD3n,7,c,7n,7,c,7n,10,c,6n,4,c,34=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)))511111111111111111111111AAA611111121111112111112112AAB711111131111113111113113AAC=INDEX(AC2:AF2,T_CA(T_PA(4,2)))811111141111114111114114AADAA911111151111115111115122ABBAB1011111161111116111116123ABCAC1111111171111117111117124ABDAD1211111211111122111118133ACCBB1311111221111123111119134ACDBC14111112311111241111110144ADDBD1511111241111125111122222BBBCC1611111251111126111123223BBCCD1711111261111127111124224BBDDD12 rws1811111271111133111125233BCC1911111311111134111126234BCDcheck2011111321111135111127244BDD=PERMUT(4,2)2111111331111136111128333CCC122211111341111137111129334CCD23111113511111441111210344CDD241111136111114511113344420 rwsDDD20 rws2511111371111146111134261111141check1111147check111135check271111142=ROWS(A5#)1111155=ROWS(I5#)111136=ROWS(Q5#)2811111438235431111156171611113750052911111441111157111138301111145down to1111166down to111139down to311111146823543 rws11111671716 rws11113105005 rws321111147⬇⬇⬇1111177⬇⬇⬇111144⬇⬇⬇3311111511111222111145T_CA postCell FormulasRangeFormulaA1,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.


----------



## Xlambda (Aug 17, 2021)

LAMBDA 1.1.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH1=PERMUTATIONA(10,6)=PERMUT(10,6)=COMBIN(10,6)=COMBIN(5,3)Task: Print all pos.combin.(no repet.) of "a", nr.chosen 3210000001E+0615120015120021021010aABCDEF3n,10,c,6checkn,10,c,6checkn,10,c,6checkn,5,c,34=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))))5111111123456123456123ABC6111112123457123457124ABD7111113123458123458125ABE8111114123459123459134ACD911111512345101234510135ACE10111116123465123467145ADE11111117123467123468234BCD12111118123468123469235BCE131111191234691234610245BDE141111110123461012347834510 rwsCDE10 rws151111211234751234791611112212347612347101711112312347812348918111124123479123481019111125123471012349102011112612348512356721111127123486123568221111281234871235692311112912348912356102411112101234810123578251111311234951235792611113212349612357102711113312349712358928111134123498123581029111135123491012359103011113612341051236783111113712341061236793211113812341071236710T_CA post 2Cell FormulasRangeFormulaA1,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.


----------



## Xlambda (Aug 22, 2021)

*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.xlsxABCDEFGHIJKLMNO1sample21234no dups31223dups4abcd1 2 3 4ascending order5abcc1 3 2 4not ascending order64123=T_CA(A2:D12)7cabd=T_P(A2:D12)12348aaad123412239ab12abcdabcd1012ab4123abcc11dcaccabdaaad121334ab1212ab1312ab13341415=T_CA(F8#)=T_P(K7#)161234123417abcdabcd1812ab12ab1920T_CA(T_P(a))=T_P(T_CA(a))21ARR T_P T_CACell FormulasRangeFormulaK6,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.


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNO1Introduction: 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^nc42.) 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.)1213input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]1415function\nc12345678910161,) PERMUTATIONA749343240116807117649823543576480140353607282475249172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!183.) COMBINA728842104629241716300350058008194.) COMBIN72135352171#NUM!#NUM!#NUM!2021Note:It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects22ARR post 1Cell FormulasRangeFormulaC15: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.


----------



## Xlambda (Dec 11, 2021)

Example inspired by today's (12-Dec-21) MrExcel YT: Random Combination Of PBPBBP - 2448
The challenge is: "*Jon wants to generate 6-letter sequences using only the letters B and P.
So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE.*"
Check the video please, you will find out that using of BASE function is a super cool trick.
Also, this can be solved with *ARRANGEMENTS* function. Other functions on minisheet ATEXTJOIN
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNO1LettersNumber chosen2BP6341. Why there are 64 possible "combinations" (permutations with repetition) ?5Because PERMUTATIONA(2,6)=646=PERMUTATIONA(2,6)7648For example, for 3 letters total nr. will be PERMUTATIONA(3,6)=7299=PERMUTATIONA(3,6)1072911Now , the only thing to do is to concatenate the columns(BYROW)12=ARRANGEMENTS(A2:B2,"pa",6)=BYROW(A13#,LAMBDA(a,CONCAT(a)))13BBBBBBBBBBBB14BBBBBPBBBBBPOr a single cell using ATEXTJOIN15BBBBPBBBBBPB=ATEXTJOIN(ARRANGEMENTS(A2:B2,"pa",6),"")16BBBBPPBBBBPPBBBBBB17BBBPBBBBBPBBBBBBBPchecks18BBBPBPBBBPBPBBBBPB=ROWS(A13#)=ROWS(J16#)19BBBPPBBBBPPBBBBBPP646420BBBPPPBBBPPPBBBPBB21BBPBBBBBPBBBBBBPBP=INDEX(J16#,1)22BBPBBPBBPBBPBBBPPBfirst value :BBBBBB23BBPBPBBBPBPBBBBPPPlast value:PPPPPP24BBPBPPBBPBPPBBPBBB=INDEX(J16#,64)25BBPPBBBBPPBBBBPBBP26BBPPBPBBPPBPBBPBPB27BBPPPBBBPPPBBBPBPPOther functions28BBPPPPBBPPPPBBPPBBATEXTJOIN29BPBBBBBPBBBBBBPPBP30BPBBBPBPBBBPBBPPPB31BPBBPBBPBBPBBBPPPP32BPBBPPBPBBPPBPBBBB33BPBPBBBPBPBBBPBBBP34BPBPBPBPBPBPBPBBPB35BPBPPBBPBPPBBPBBPP36BPBPPPBPBPPPBPBPBB37BPPBBBBPPBBBBPBPBP38BPPBBPBPPBBPBPBPPB39BPPBPBBPPBPBBPBPPP40BPPBPPBPPBPPBPPBBB41BPPPBBBPPPBBBPPBBP42BPPPBPBPPPBPBPPBPB43BPPPPBBPPPPBBPPBPP44BPPPPPBPPPPPBPPPBB45PBBBBBPBBBBBBPPPBP46PBBBBPPBBBBPBPPPPB47PBBBPBPBBBPBBPPPPP48PBBBPPPBBBPPPBBBBB49PBBPBBPBBPBBPBBBBP50PBBPBPPBBPBPPBBBPB51PBBPPBPBBPPBPBBBPP52PBBPPPPBBPPPPBBPBB53PBPBBBPBPBBBPBBPBP54PBPBBPPBPBBPPBBPPB55PBPBPBPBPBPBPBBPPP56PBPBPPPBPBPPPBPBBB57PBPPBBPBPPBBPBPBBP58PBPPBPPBPPBPPBPBPB59PBPPPBPBPPPBPBPBPP60PBPPPPPBPPPPPBPPBB61PPBBBBPPBBBBPBPPBP62PPBBBPPPBBBPPBPPPB63PPBBPBPPBBPBPBPPPP64PPBBPPPPBBPPPPBBBB65PPBPBBPPBPBBPPBBBP66PPBPBPPPBPBPPPBBPB67PPBPPBPPBPPBPPBBPP68PPBPPPPPBPPPPPBPBB69PPPBBBPPPBBBPPBPBP70PPPBBPPPPBBPPPBPPB71PPPBPBPPPBPBPPBPPP72PPPBPPPPPBPPPPPBBB73PPPPBBPPPPBBPPPBBP74PPPPBPPPPPBPPPPBPB75PPPPPBPPPPPBPPPBPP76PPPPPPPPPPPPPPPPBB77PPPPBP78PPPPPB79PPPPPP80ARRG ex 1Cell FormulasRangeFormulaA6,M21,L18,N18,J15,A12,H12,A9A6=FORMULATEXT(A7)A7A7=PERMUTATIONA(2,6)A10A10=PERMUTATIONA(3,6)A13:F76A13=ARRANGEMENTS(A2:B2,"pa",6)H13:H76H13=BYROW(A13#,LAMBDA(a,CONCAT(a)))J16:J79J16=ATEXTJOIN(ARRANGEMENTS(A2:B2,"pa",6),"")L19L19=ROWS(A13#)N19N19=ROWS(J16#)M22M22=INDEX(J16#,1)M23M23=INDEX(J16#,64)M24M24=FORMULATEXT(M23)Dynamic array formulas.


----------



## Xlambda (Dec 13, 2021)

*Study of using BASE function, as feasible alternative for creating arrays of permutations with repetitions, replacing T_PA function concept, the function that is triggered by ARRANGEMENTS when this this type of permutation is needed. *
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Using of BASE function concept vs. T_PA function. Part 1.2As we know, the t="pa" (type) argument in ARRANGEMENTS(a,t,c) is triggering T_PA(n,c) function34total arrangements5LettersNr. chosen=COLUMNS(A6:B6)^C6To avoid using nested SUBSTITUTE functions there are 2 approaches6BP532- calling custom made function AREPLACE (deals with nested substitute's)7Note: For shorter formula , replaced permutationa(n,c) with n^c- creating an array of the arrangements "indexes"T_PA returns indexes array8=BASE(SEQUENCE(COLUMNS(A6:B6)^C6)-1,COLUMNS(A6:B6),C6)AREPLACEby default, no AREPLACE needed9↓=SUBSTITUTE(SUBSTITUTE(A10#,0,A6),1,B6)=AREPLACE(A10#,{0,1},A6:B6)=MID(A10#,SEQUENCE(,C6),1)+1=T_PA(2,5)=INDEX(A6:B6,R10#)1000000BBBBBBBBBB1111111111BBBBB1100001BBBBPBBBBP1111211112BBBBP1200010BBBPBBBBPB1112111121BBBPB1300011BBBPPBBBPP1112211122BBBPP1400100BBPBBBBPBB1121111211BBPBB1500101BBPBPBBPBP1121211212BBPBP1600110BBPPBBBPPB1122111221BBPPB1700111BBPPPBBPPP1122211222BBPPP1801000BPBBBBPBBB1211112111BPBBB1901001BPBBPBPBBP1211212112BPBBP2001010BPBPBBPBPB1212112121BPBPB2101011BPBPPBPBPP1212212122BPBPP2201100BPPBBBPPBB1221112211BPPBB2301101BPPBPBPPBP1221212212BPPBP2401110BPPPBBPPPB1222112221BPPPB2501111BPPPPBPPPP1222212222BPPPP2610000PBBBBPBBBB2111121111PBBBB2710001PBBBPPBBBP2111221112PBBBP2810010PBBPBPBBPB2112121121PBBPB2910011PBBPPPBBPP2112221122PBBPP3010100PBPBBPBPBB2121121211PBPBB3110101PBPBPPBPBP2121221212PBPBP3210110PBPPBPBPPB2122121221PBPPB3310111PBPPPPBPPP2122221222PBPPP3411000PPBBBPPBBB2211122111PPBBB3511001PPBBPPPBBP2211222112PPBBP3611010PPBPBPPBPB2212122121PPBPB3711011PPBPPPPBPP2212222122PPBPP3811100PPPBBPPPBB2221122211PPPBB3911101PPPBPPPPBP2221222212PPPBP4011110PPPPBPPPPB2222122221PPPPB4111111PPPPPPPPPP2222222222PPPPP42ARRG ex 2Cell FormulasRangeFormulaE5,X9,I9,L9,R9,C9E5=FORMULATEXT(E6)E6E6=COLUMNS(A6:B6)^C6A8A8=FORMULATEXT(A10)A10:A41A10=BASE(SEQUENCE(COLUMNS(A6:B6)^C6)-1,COLUMNS(A6:B6),C6)C10:C41C10=SUBSTITUTE(SUBSTITUTE(A10#,0,A6),1,B6)I10:I41I10=AREPLACE(A10#,{0,1},A6:B6)L10:P41L10=MID(A10#,SEQUENCE(,C6),1)+1R10:V41R10=T_PA(2,5)X10:AB41X10=INDEX(A6:B6,R10#)Dynamic array formulas.


----------



## Xlambda (Dec 13, 2021)

LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUV1Using of BASE function concept vs. T_PA function. Part 2.2We have seen that BASE function can have good premises to replace T_PA(n,c) function, or not?3T_PA(n,c)=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1)4Also we have seen that, to avoid nested SUBSTITUTE functions, it is better to create array of "indexes" concept,5that avoids calling other function AREPLACE6Designing T_PAB(n,c) function using BASE for a simple scenario n=2,c=3 (n<c scenario)78step 1. BASE(SEQUENCE(n^c)-1,n,c)9=BASE(SEQUENCE(2^3)-1,2,3)1000011001step 2. expanding values tostep 3. for corect indexesstep 4. The function12010an array of indexeswe have to add 113011=MID(A10#,SEQUENCE(,3),1)=C14#+1=LAMBDA(n,c,LET(b,BASE(SEQUENCE(n^c)-1,n,c),MID(b,SEQUENCE(,c),1)+1))(2,3)14100000111111151010011121121611001012112117111011122122181002112111910121221220110221221211112222222223 T_PAB(n,c)=LAMBDA(n,c,LET(b,BASE(SEQUENCE(n^c)-1,n,c),MID(b,SEQUENCE(,c),1)+1))2425=T_PAB(2,3)=T_PA(2,3)26111111271121122812112129122122302112113121221232221221332222223435So far so good, both functions return same outcome,on the next post will cover scenarios where n>c36ARRG ex 3Cell FormulasRangeFormulaA9,H25,C25,M13,C13,H13A9=FORMULATEXT(A10)A10:A17A10=BASE(SEQUENCE(2^3)-1,2,3)C14:E21C14=MID(A10#,SEQUENCE(,3),1)H14:J21H14=C14#+1M14:O21M14=LAMBDA(n,c,LET(b,BASE(SEQUENCE(n^c)-1,n,c),MID(b,SEQUENCE(,c),1)+1))(2,3)C26:E33C26=T_PAB(2,3)H26:J33H26=T_PA(2,3)Dynamic array formulas.


----------



## Xlambda (Dec 13, 2021)

LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTU1Using of BASE function concept vs. T_PA function. Part 3.(n>c scenario)2Checking T_PAB(n,c) function using BASE for n=13,c=3The reason T_PAB returns errors is because BASE, when radix argument n>10, returns letters3- total possibilities n^c=2197This problem can be addressed easily with AREPLACE, but this is exactly what we wanted to avoid.4Conclusion: T_PA is better for covering both scenarios.5So T_PA will stay, and on following posts will cover what I have stated since day one, alternatives for the recursive ones T_P and T_CA.6"There are 2 other approaches, one using AFUSBYROW/BYCOL functions, and the other using new functions." (lambda helper functions)78=T_PAB(13,3)=T_PA(13,3)=BASE(SEQUENCE(13^3)-1,13,3)=MID(I9#,SEQUENCE(,3),1)=K9#+19111111000000111checking10112112001001112=ROWS(A9#)11113113002002113219712114114003003114=ROWS(E9#)13115115004004115219714116116005005116=ROWS(I9#)15117117006006117219716118118007007118=ROWS(O9#)171191190080081192197181110111000900911101911#VALUE!111100A00A11#VALUE!2011#VALUE!111200B00B11#VALUE!2111#VALUE!111300C00C11#VALUE!221211210100101212312212201101112224123123012012123251241240130131242612512501401412527126126015015126281271270160161272912812801701712830129129018018129down to311210121001901912102197 rows3212#VALUE!121101A01A12#VALUE!↓↓↓↓3312#VALUE!121201B01B12#VALUE!3412#VALUE!121301C01C12#VALUE!ARRG ex 4Cell FormulasRangeFormulaD3D3=13^3A8,S10,S12,S14,S16,I8,K8,O8,E8A8=FORMULATEXT(A9)A9:C2205A9=T_PAB(13,3)E9:G2205E9=T_PA(13,3)I9:I2205I9=BASE(SEQUENCE(13^3)-1,13,3)K9:M2205K9=MID(I9#,SEQUENCE(,3),1)O9:Q2205O9=K9#+1S11S11=ROWS(A9#)S13S13=ROWS(E9#)S15S15=ROWS(I9#)S17S17=ROWS(O9#)Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

When I first started this thread, I've said: *"There are 2 other approaches, one using AFUSBYROW/BYCOL functions, and the other using new functions." *Here are the other approaches.
Recap: *Permutations*
As we know PERMUTATIONA(n,c) calculates all permutations with repetitions of "n" by nr. chosen "c", and PERMUT(n,c) calculates the number of permutations without repetitions.
T_PA(n,c) is a function we created to "print" an array "a" of all these permutations with repetitions. Is simple and non-recursive.
T_P(a) is a recursive function filters the rows of "a" that have repetitions (dups), will keep only the rows with no dups. To check these results, rows(T_P(a))=PERMUT(n,c), the same "n" and "c" that created "a".
T_PN(a) will replace the recursive one using New REDUCE and BYROW. 
T_PF(a) will replace the recursive one with other existing Functions AFUSBYROW and AHCLEAN.
Note: All functions T_P(a),T_PN(a),T_PF(a), can be used as standalone functions that will be capable to filter any array "a" for rows w/o dups, not only the array "a" created by T_PA(n,c)
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Concept of T_P(a), T_PN(a)23 T_P(a)=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))))45clm 1=aclm 2=aclm 3=aif we sum all these arrayssumming by rows6a=A7:A16=A7:C16=B7:B16=A7:C16=C7:C16=A7:C16=E7#+I7#+M7#=SUM(Q7:S7)7112TRUETRUEFALSETRUETRUEFALSEFALSEFALSETRUE22158abbTRUEFALSEFALSEFALSETRUETRUEFALSETRUETRUE12259cdcTRUEFALSETRUEFALSETRUEFALSETRUEFALSETRUE212510222TRUETRUETRUETRUETRUETRUETRUETRUETRUE333911123TRUEFALSEFALSEFALSETRUEFALSEFALSEFALSETRUE111312213TRUEFALSEFALSEFALSETRUEFALSEFALSEFALSETRUE111313321TRUEFALSEFALSEFALSETRUEFALSEFALSEFALSETRUE111314abcTRUEFALSEFALSEFALSETRUEFALSEFALSEFALSETRUE111315ccbTRUETRUEFALSETRUETRUEFALSEFALSEFALSETRUE221516bacTRUEFALSEFALSEFALSETRUEFALSEFALSEFALSETRUE11131718Note: There is also a solution to do iterations by rows, but I chose column orientation iterations for being the short dimension.1920T_P does sum of arrays clm i=a recursively,T_PN with REDUCE21The rows that deliver 1,1,1 coresponds to the rows of "a" that hold unique values22On other words, if we sum by rows, the rows with unique values =3 or =clms(a)23T_P does the sum by rows with MMULT, T_PN with by BYROW2425=MMULT(Q7#,SEQUENCE(3)^0)=BYROW(Q7#,LAMBDA(x,SUM(x)))26552755For final result both use FILTER28552999=FILTER(A7:C16,J26#=3)=T_P(A7:C16)3033123123313321321332333213213333abcabc3455bacbac353336ARRG np1Cell FormulasRangeFormulaE6,M29,Q29,E25,J25,Q6,U6,M6,I6E6=FORMULATEXT(E7)E7:G16E7=A7:A16=A7:C16I7:K16I7=B7:B16=A7:C16M7:O16M7=C7:C16=A7:C16Q7:S16Q7=E7#+I7#+M7#U7:U16U7=SUM(Q7:S7)E26:E35E26=MMULT(Q7#,SEQUENCE(3)^0)J26:J35J26=BYROW(Q7#,LAMBDA(x,SUM(x)))M30:O34M30=FILTER(A7:C16,J26#=3)Q30:S34Q30=T_P(A7:C16)Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

*T_PN(a*) *T*ool function calculating *P*ermutations indexes without repetitions, using *N*ew lambda helper functions *REDUCE, BYROW*.

```
=LAMBDA(a,LET(c,COLUMNS(a),FILTER(a,BYROW(REDUCE(0,SEQUENCE(c),LAMBDA(v,i,v+(INDEX(a,,i)=a))),LAMBDA(x,SUM(x)=c)))))
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1T_PN(a) : alternative of T_P(a) (recursive) , using NEW lambda helper functions (REDUCE,BYROW) - filters rows w/o dups23If a=T_PA(n,c) "prints" all PERMUTATIONA(n,c) (permutations w repetitions) , T_PN(T_PA(n,c))=T_PN(a) will "print" all PERMUT(n,c) (permutations w/o repetitions)45n,4,c,36=PERMUTATIONA(4,3)step 1 (clm1=a)+(clm2=a)+(clm3=a)764check=REDUCE(0,SEQUENCE(COLUMNS(A11#)),LAMBDA(v,i,v+(INDEX(A11#,,i)=A11#)))864=ROWS(A11#)⬇check9⬇step 2 sum BYROW(a')24=PERMUT(4,3)10=T_PA(4,3)a⬇a'=BYROW(E11#,LAMBDA(a,SUM(a)))24=ROWS(P14#)111113339a''NEW!! T_PN(a)121122215step 3 FILTER(a,a''=clms(a))step 4 single cellrecursive one T_P(a)131132215=FILTER(A11#,I11#=COLUMNS(A11#))=T_PN(A11#)=T_P(A11#)14114221512312312315121212512412412416122122513213213217123111313413413418124111314214214219131212514314314320132111321321321321133122521421421422134111323123123123141212523423423424142111324124124125143111324324324326144122531231231227211122531431431428212212532132132129213111332432432430214111334134134131221221534234234232222333941241241233223221541341341334224221542142142135231111342342342336232212543143143137233122543243243238234111339241111340242212541243111342244122543311122544312111345313212546314111347321111348322122549323212550324111351331221552332221553333333954334221555341111356342111357343212558344122559411122560412111361413111362414212563421111364422122565423111366424212567431111368432111369433122570434212571441221572442221573443221574444333975ARRG np2Cell FormulasRangeFormulaA6,T13,K13,P13,A10,I10A6=FORMULATEXT(A7)A7A7=PERMUTATIONA(4,3)E7E7=FORMULATEXT(E11)A8A8=ROWS(A11#)B8,Q9:Q10B8=FORMULATEXT(A8)P9P9=PERMUT(4,3)P10P10=ROWS(P14#)A11:C74A11=T_PA(4,3)E11:G74E11=REDUCE(0,SEQUENCE(COLUMNS(A11#)),LAMBDA(v,i,v+(INDEX(A11#,,i)=A11#)))I11:I74I11=BYROW(E11#,LAMBDA(a,SUM(a)))K14:M37K14=FILTER(A11#,I11#=COLUMNS(A11#))P14:R37P14=T_PN(A11#)T14:V37T14=T_P(A11#)Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

*T_PF(a)* *T*ool function calculating *P*ermutations indexes without repetitions, using other *F*unctions AFUSBYROW, AHCLEAN

```
=LAMBDA(a,AHCLEAN(AFUSBYROW(a,,1)))
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQ1T_PF(a) , alternative of T_P(a)  or T_PN(a) , using other Functions (AFUSBYROW,AHCLEAN) - filters rows w/o dups2( f,u,s are the filter, unique, sort, arguments of AFUSBYROW )3step 1step 24permutationa(4,3)f,omitted,u,1,s,omittedn,omittedstep 3other functions5n,4,c,3(unique by each row)(filters only full rows)single cellAFUSBYROW6=T_PA(4,3)=AFUSBYROW(A7#,,1)=AHCLEAN(E7#)=T_PF(A7#)AHCLEAN71111123123811212124124911313132132101141413413411121121421421212212143143131231232132131412412421421415131132312311613213223423417133132412411813413424324319141143123122014214231431421143143321321221441432432423211213413412421221342342252132134124122621421441341327221214214212822224234232922323431431302242443243231231231322322333233233423423435241241362422437243243382442439311314031231241313314231431443321321443223245323324632432447331314833232493333503343451341341523423425334334543443455411415641241257413413584144159421421604224261423423624244263431431644324326543343664344367441416844242694434370444471ARRG np3Cell FormulasRangeFormulaA6,M6,E6,I6A6=FORMULATEXT(A7)A7:C70A7=T_PA(4,3)E7:G70E7=AFUSBYROW(A7#,,1)I7:K30I7=AHCLEAN(E7#)M7:O30M7=T_PF(A7#)Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZ1Task: Filter only the rows of an array that hold unique values, general use, all 3 methods T_P(a) (recursive),T_PN(a) (New lambda helper fctions),T_PF(a) (calls other Functions)2sample created with RANDARRAY(10,10,1,30,1)3sample=AFUSBYROW(A4:J23,,1)42522519202034928252192034928561223132919742986122313291974861827182528262526221827252826227131815192521411923131815192521412384111032871413162041110328714131620915161832115171881215161832117812102323132271719252231322717192511124627719172726412462771917261274178823616282874178236162813212126821631139212682163119145523201238131523201281311519114282682912201019114282682912201016292017731612122818292017731612281817514297281627243055142972816272430188307253091920195830725919205191926302782212231219263027822122312202913539231027921291353923102721211873825209414261873825209414262220202927431410212220292743141021222316821252115151917111682125151917112425NEW lambda helper functions methodAFUSBYROW method, other Functions method26=T_PN(A4:J23)=T_PF(A4:J23)27411103287141316204111032871413162028191142826829122010191142826829122010291926302782212231219263027822122312301873825209414261873825209414263132recursive method33=T_P(A4:J23)34411103287141316203519114282682912201036192630278221223123718738252094142638ARRG np4Cell FormulasRangeFormulaL3,A33,L26,A26L3=FORMULATEXT(L4)L4:U23L4=AFUSBYROW(A4:J23,,1)A27:J30A27=T_PN(A4:J23)L27:U30L27=T_PF(A4:J23)A34:J37A34=T_P(A4:J23)Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

Recap: *Combinations*
COMBINA(n,c) returns the number of combinations with repetitions for a given number of items.
COMBIN(n,c) returns the number of combinations for a given number of items (w/o repetitions).
For permutations order is important, for combinations order is not important.
If order is not important, if we choose only one type of order (ascending) and we filter by this condition any other permutations, will get the combinations indexes array.
T_CA(a) is a recursion function we created to filter only the rows of an array "a" that are in ascending order.
If we apply this to the array of permutations w repetitions will get an array of combinations w repetitions.
If we apply same function, but this time, to the array of permutations w/o repetitions, we will get an array of combinations w/o repetitions.
T_PA(n,c) = a = array of permutations w repetitions.
T_P(a)=T_P(T_PA(n,c)) = array of permutations w/o repetitions.
T_CA(T_PA(n,c)) = array of combinations w repetitions.
T_CA(T_P(a))=T_CA(T_P(T_PA(n,c))) = array of combinations w/o repetitions.
Note: If we already have the array of combinations w repetitions, let's say "b", to get array of combinations w/o repetitions we can also apply T_P(b)
In other words, T_CA(T_P(a))=T_P(T_CA(a))
T_CAN(a) will replace the recursive one using New REDUCE
T_CAF(a) will replace the recursive one with other existing Functions AFUSBYROW
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRST1Concept of T_CA(a), T_CAN(a)23T_CA(a)=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))))451. combinations with repetitions6clm 1<=clm 2clm 2<=clm 3clm 3<=clm 4if we multiply all arraysfilter only rows in ascending order7a=A8:A17<=B8:B17=B8:B17<=C8:C17=C8:C17<=D8:D17=F8#*H8#*J8#=FILTER(A8:D17,L8#)81123TRUETRUETRUE111239abbaTRUETRUEFALSE0222210cdceTRUEFALSETRUE01234112222TRUETRUETRUE1abcd121234TRUETRUETRUE1bccd132132FALSETRUEFALSE0143211FALSEFALSETRUE0=T_CA(A8:D17)15abcdTRUETRUETRUE1112316bccdTRUETRUETRUE1222217bacdFALSETRUETRUE0123418abcd19For i=1 to clms(a)-1 we do clm i<=clm i+1 and we multiply the results.bccd20(clm 1<=clm2)*(clm 2<=clm 3)*(clm 3<=clm 4)21The rows that corespond to 1's values will be the rows in ascending order22T_CA does this recursively, T_CAN will use REDUCE23242. "combinations" w/o repetitions25Will have to apply same algoritm but this time , to an array that have no dups, OR, to apply T_P (keeps rows no dups) to array in T152627=T_P(A8:D17)=T_CA(A28#)OR=T_P(O15#)2812341234123429abcdabcdabcd30bacd3132directly from "a"33a=T_CA(T_P(A34:D43))<=>=T_P(T_CA(A34:D43))3411231234123435abbaabcdabcd36cdce37222238123439213240321141abcd42bccd43bacd44ARRG np5Cell FormulasRangeFormulaF7,H7,J7,L7,K33,F33,K27,F27,A27,O14,O7F7=FORMULATEXT(F8)F8:F17F8=A8:A17<=B8:B17H8:H17H8=B8:B17<=C8:C17J8:J17J8=C8:C17<=D8:D17L8:L17L8=F8#*H8#*J8#O8:R12O8=FILTER(A8:D17,L8#)O15:R19O15=T_CA(A8:D17)A28:D30A28=T_P(A8:D17)F28:I29F28=T_CA(A28#)K28:N29K28=T_P(O15#)F34:I35F34=T_CA(T_P(A34:D43))K34:N35K34=T_P(T_CA(A34:D43))Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

*T_CAN(a)* *T*ool function calculating *C*ombinations indexes, ( COMBIN*A* ) using *N*ew lambda helper functions *REDUCE*.
If input array a=T_PA(n,c), T_CAN(a) returns combinations with repetitions indexes array of "n" by nr. chosen "c".
If input array a=T_P(T_PA(n,c)), T_CAN(a) returns combinations w/o repetitions indexes array of "n" by nr. chosen "c".

```
=LAMBDA(a,LET(c,COLUMNS(a),FILTER(a,REDUCE(1,SEQUENCE(c-1),LAMBDA(v,i,v*(INDEX(a,,i)<=INDEX(a,,i+1)))))))
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF1T_CAN(a) , alternative of T_CA(a) (recursive) , using NEW lambda helper functions (REDUCE) - filters only rows in ascending order, order is not important231. if a=T_PA(n,c) "prints" all PERMUTATIONA(n,c) (permutations w repetitions) , T_CAN(a)=T_CAN(T_PA(n,c)) will "print" all COMBINA(n,c) (combinations w repetitions)42. if b=T_PN(a) "prints" all PERMUT(n,c) (permutations w/o repetitions) , T_CAN(b)=T_CAN(T_PN(a))=T_CAN(T_PN(T_PA(n,c))) will "print" all COMBIN(n,c) (combinations w/o repetitions)561. combinations w repetitions7permutationa(4,3)step 1 <=> for i=1 to clms(a)-1, 1*(clm i<=clm i+1)8n,4,c,3=LET(a,A10#,c,COLUMNS(a),REDUCE(1,SEQUENCE(c-1),LAMBDA(v,i,v*(INDEX(a,,i)<=INDEX(a,,i+1)))))9=T_PA(4,3)aa'check101111=COMBINA(4,3)2. combinations w/o repetitionssame result with recursion11112120b=T_PN(a)T_CAN(b)T_CA(b)121131=ROWS(K17#)=T_PN(A10#)=T_CAN(S13#)=T_CA(S13#)13114120123123123141210step 2, FILTER(a,a')step 3 single cell124124124151221NEW!! T_CAN(a)recursive one T_CA(a)132134134161231=FILTER(A10#,E10#)=T_CAN(A10#)=T_CA(A10#)134234234171241111111111142181310112112112143check191320113113113213=COMBIN(4,3)2013311141141142144211341122122122231=ROWS(W13#)2214101231231232344231420124124124241241430133133133243or directly from "a"251441134134134312=T_CAN(T_PN(A10#))=T_PN(T_CAN(A10#))26211014414414431412312327212022222222232112412428213022322322332413413429214022422422434123423430221023323323334231222123423423441232223124424424441333224133333333342134231033433433442335232034434434443136233144444444443237234138241039242040243041244142311043312044313045314046321047322048323049324050331051332052333153334154341055342056343057344158411059412060413061414062421063422064423065424066431067432068433069434070441071442072443073444174ARRG np6Cell FormulasRangeFormulaE8E8=FORMULATEXT(E10)A9,W25,AA25,W21,W19,G16,K16,O16,W12,AA12,O12,S12,O10A9=FORMULATEXT(A10)A10:C73A10=T_PA(4,3)E10:E73E10=LET(a,A10#,c,COLUMNS(a),REDUCE(1,SEQUENCE(c-1),LAMBDA(v,i,v*(INDEX(a,,i)<=INDEX(a,,i+1)))))O11O11=COMBINA(4,3)O13O13=ROWS(K17#)S13:U36S13=T_PN(A10#)W13:Y16W13=T_CAN(S13#)AA13:AC16AA13=T_CA(S13#)G17:I36G17=FILTER(A10#,E10#)K17:M36K17=T_CAN(A10#)O17:Q36O17=T_CA(A10#)W20W20=COMBIN(4,3)W22W22=ROWS(W13#)W26:Y29W26=T_CAN(T_PN(A10#))AA26:AC29AA26=T_PN(T_CAN(A10#))Dynamic array formulas.


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNO1Introduction: 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^nc42.) 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.)1213input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]1415function\nc12345678910161,) PERMUTATIONA749343240116807117649823543576480140353607282475249172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!183.) COMBINA728842104629241716300350058008194.) COMBIN72135352171#NUM!#NUM!#NUM!2021Note:It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects22ARR post 1Cell FormulasRangeFormulaC15: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.


----------



## Xlambda (Dec 19, 2021)

*T_CAF(a)* *T*ool function calculating *C*ombinations indexes, ( COMBIN*A* ) using other *F*unction AFUSBYROW.

```
=LAMBDA(a,UNIQUE(AFUSBYROW(a,,,1)))
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1T_CAF(a) , alternative of T_CA(a) or T_CAN(a), using other Functions (AFUSBYROW) - filters only rows in ascending order, order is not important2( f,u,s are the filter, unique, sort, arguments of AFUSBYROW )341. combinations with repetitions2. combinations w/o repetitionsother functions5permutationa(4,3)step 1 sort ascending by each row of aAFUSBYROW6n,4,c,3f,u,omitted,s,1step 2 unique(a')step 3 single cell7=T_PA(4,3)a=AFUSBYROW(A8#,,,1)=UNIQUE(E8#)=T_CAF(A8#)=T_PF(T_PA(4,3))=T_CAF(Q8#)8111111a'11111112312391121121121121241241011311311311313213411114114114114134234121211121221221421312212212312314314123123124124213=T_PF(T_CAF(A8#))15124124133133214123161311131341342311241713212314414423413418133133222222241234191341342232232432014111422422431221142124233233314221431342342343212314414424424432424211112333333341252121223343343422621312334434441227214124444444413282211224212922222242330223223431312242244323223112333232223342332333523423436241124372422243824323439244244403111134131212342313133433141344432112345322223463232334732423448331133493322335033333351334334523411345334223454343334553443445641111457412124584131345941414460421124614222246242323463424244644311346543223466433334674343446844114469442244704433447144444472ARRG np7Cell FormulasRangeFormulaA7,U14,Q7,U7,E7,I7,M7A7=FORMULATEXT(A8)A8:C71A8=T_PA(4,3)E8:G71E8=AFUSBYROW(A8#,,,1)I8:K27I8=UNIQUE(E8#)M8:O27M8=T_CAF(A8#)Q8:S31Q8=T_PF(T_PA(4,3))U8:W11U8=T_CAF(Q8#)U15:W18U15=T_PF(T_CAF(A8#))Dynamic array formulas.


----------



## Xlambda (Dec 19, 2021)

LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Task: filtering only rows of an array that are in ascending order, general use, all 3 methods T_CA(a) , T_CAN(a) , T_CAF(a)23recursivelambda helperother functions4samplea=T_CA(A5:E12)=T_CAN(A5:E12)=T_CAF(A5:E12)5abcdeabcdeabcdeabcde6acbde123451234512345712345aabcdaabcdaabcd8132451223412234122349aabcd123ab123ab123ab101223411ab12312123ab1314a without rows with dups15=T_P(A5:E12)=T_CA(A16#)=T_CAN(A16#)=T_CAF(A16#)16abcdeabcdeabcdeabcde17acbde1234512345123451812345123ab123ab123ab191324520ab12321123abfrom a directly2223=T_CA(T_P(A5:E12))=T_CAN(T_P(A5:E12))=T_CAF(T_PF(A5:E12))24abcdeabcdeabcde2512345123451234526123ab123ab123ab2728=T_P(T_CA(A5:E12))=T_PN(T_CAN(A5:E12))=T_PF(T_CAF(A5:E12))29abcdeabcdeabcde3012345123451234531123ab123ab123ab32ARRG np8Cell FormulasRangeFormulaG4,S28,M28,G28,S23,M23,G23,S15,M15,G15,A15,S4,M4G4=FORMULATEXT(G5)G5:K9G5=T_CA(A5:E12)M5:Q9M5=T_CAN(A5:E12)S5:W9S5=T_CAF(A5:E12)A16:E21A16=T_P(A5:E12)G16:K18G16=T_CA(A16#)M16:Q18M16=T_CAN(A16#)S16:W18S16=T_CAF(A16#)G24:K26G24=T_CA(T_P(A5:E12))M24:Q26M24=T_CAN(T_P(A5:E12))S24:W26S24=T_CAF(T_PF(A5:E12))G29:K31G29=T_P(T_CA(A5:E12))M29:Q31M29=T_PN(T_CAN(A5:E12))S29:W31S29=T_PF(T_CAF(A5:E12))Dynamic array formulas.


----------



## Xlambda (Dec 21, 2021)

Regarding component functions, the only stone left unturned is *T_PA(n,c)*, a simple and straight forward function using MOD.
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUVW1T_PA(n,c) step by step. 2 =LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1)3n=4,c=3check4=SEQUENCE(4^3)=SEQUENCE(,3)=SEQUENCE(4^3)/4^(3-SEQUENCE(,3))=ROUNDUP(H5#,0)=MOD(L5#-1,4)+1=T_PA(4,3)511230.06250.251111111111620.1250.5211211211273=3-SEQUENCE(,3)0.18750.753113113113842100.2514114114114950.31251.255125121121106=4^(3-SEQUENCE(,3))0.3751.5612612212211716410.43751.7571271231231280.5281281241241390.56252.25913913113114100.6252.510131013213215110.68752.7511131113313316120.75312131213413417130.81253.2513141314114118140.8753.514141414214219150.93753.7515141514314320161416141614414421171.06254.2517251721121122181.1254.518251821221223191.18754.7519251921321324201.25520252021421425211.31255.2521262122122126221.3755.522262222222227231.43755.7523262322322328241.5624262422422429251.56256.2525272523123130261.6256.526272623223231271.68756.7527272723323332281.75728272823423433291.81257.2529282924124134301.8757.530283024224235311.93757.7531283124324336322832283224424437332.06258.2533393331131138342.1258.534393431231239352.18758.7535393531331340362.25936393631431441372.31259.25373103732132142382.3759.5383103832232243392.43759.75393103932332344402.510403104032432445412.562510.25413114133133146422.62510.5423114233233247432.687510.75433114333333348442.7511443114433433449452.812511.25453124534134150462.87511.5463124634234251472.937511.7547312473433435248312483124834434453493.062512.25494134941141154503.12512.5504135041241255513.187512.75514135141341356523.2513524135241441457533.312513.25534145342142158543.37513.5544145442242259553.437513.75554145542342360563.514564145642442461573.562514.25574155743143162583.62514.5584155843243263593.687514.75594155943343364603.7515604156043443465613.812515.25614166144144166623.87515.5624166244244267633.937515.7563416634434436864416644166444444469ARRG np9Cell FormulasRangeFormulaB4,D4,H4,L4,P4,T4,D10,D7B4=FORMULATEXT(B5)B5:B68B5=SEQUENCE(4^3)D5:F5D5=SEQUENCE(,3)H5:J68H5=SEQUENCE(4^3)/4^(3-SEQUENCE(,3))L5:N68L5=ROUNDUP(H5#,0)P5:R68P5=MOD(L5#-1,4)+1T5:V68T5=T_PA(4,3)D8:F8D8=3-SEQUENCE(,3)D11:F11D11=4^(3-SEQUENCE(,3))Dynamic array formulas.


----------



## Xlambda (May 8, 2022)

We have seen *3 design variants* for tool lambdas, using recursion, lambda helper functions, and other custom-made function.
Chose as *final variant* the set of tool lambdas that use lambda helper functions.
*T_PA(n,c)*

```
=LAMBDA(n, c, MOD(ROUNDUP(SEQUENCE(n ^ c) / n ^ (c - SEQUENCE(, c)), 0) - 1, n) + 1)
```

*T_P(a)*

```
=LAMBDA(a,
    LET(c, COLUMNS(a),
        FILTER(a,BYROW(REDUCE(0, SEQUENCE(c), LAMBDA(v,i, v + (INDEX(a, , i) = a))),LAMBDA(x, SUM(x) = c)))
    )
)
```

*T_CA(a)*

```
=LAMBDA(a,
    LET(c, COLUMNS(a),
        FILTER(a,REDUCE(1,SEQUENCE(c - 1),LAMBDA(v,i, v * (INDEX(a, , i) <= INDEX(a, , i + 1)))))
    )
)
```

*ARRANGEMENTS(a,t,c)*   Note: Only modification to the initial one, replaced AFLAT(a) with TOCOL(IF(a = "", NA(), a), 3)

```
=LAMBDA(a,t,c,
    IF(AND(t <> {"p","pa","c","ca"}),"check type",
        LET(k, MAX(1, c),x, TOCOL(IF(a = "", NA(), a), 3),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)))))
        )
    )
)
```


----------



## Xlambda (May 8, 2022)

Example inspired from today's (8May22) ExcelIsFun YT Excel Statistical Analysis 36: Sampling & Sampling Distribution of Sample Means (Xbar)
Create the following table using ARRANGEMENTS (*min 15:48*)
Ch07-ESA.xlsmABCDEFGHIJKL1Pop Size = N =72Sample Size = n =33Number of Samples Possible:3545Sales RepSalesSales Rep 1Sales Rep 2Sales Rep 3Sales 1Sales 2Sales 3XbarP(Sample)6Jo185JoSiouxChin1852502102150.0285714297Sioux250JoSiouxSheliadawn185250310248.33333330.0285714298Chin210JoSiouxGigi185250298244.33333330.0285714299Sheliadawn310JoSiouxTyrone1852504022790.02857142910Gigi298JoSiouxKip185250370268.33333330.02857142911Tyrone402JoChinSheliadawn1852103102350.02857142912Kip370JoChinGigi1852102982310.02857142913JoChinTyrone185210402265.66666670.02857142914JoChinKip1852103702550.02857142915JoSheliadawnGigi185310298264.33333330.02857142916JoSheliadawnTyrone1853104022990.02857142917JoSheliadawnKip185310370288.33333330.02857142918JoGigiTyrone1852984022950.02857142919JoGigiKip185298370284.33333330.02857142920JoTyroneKip1854023703190.02857142921SiouxChinSheliadawn250210310256.66666670.02857142922SiouxChinGigi250210298252.66666670.02857142923SiouxChinTyrone250210402287.33333330.02857142924SiouxChinKip250210370276.66666670.02857142925SiouxSheliadawnGigi2503102982860.02857142926SiouxSheliadawnTyrone250310402320.66666670.02857142927SiouxSheliadawnKip2503103703100.02857142928SiouxGigiTyrone250298402316.66666670.02857142929SiouxGigiKip2502983703060.02857142930SiouxTyroneKip250402370340.66666670.02857142931ChinSheliadawnGigi210310298272.66666670.02857142932ChinSheliadawnTyrone210310402307.33333330.02857142933ChinSheliadawnKip210310370296.66666670.02857142934ChinGigiTyrone210298402303.33333330.02857142935ChinGigiKip210298370292.66666670.02857142936ChinTyroneKip210402370327.33333330.02857142937SheliadawnGigiTyrone310298402336.66666670.02857142938SheliadawnGigiKip3102983703260.02857142939SheliadawnTyroneKip310402370360.66666670.02857142940GigiTyroneKip298402370356.66666670.02857142941All Possible Samples (an)Cell FormulasRangeFormulaE1E1=COUNTA(A6:A12)E3E3=COMBIN(E1,E2)G6:I40G6=XLOOKUP(D6:F40,A6:A12,B6:B12)J6:J40J6=AVERAGE(G6:I6)K6:K40K6=1/$E$3Dynamic array formulas.


----------



## Xlambda (May 8, 2022)

Ch07-ESA.xlsmABCDEFGHIJKLMNOPQ1Pop Size = N =72Sample Size = n =33Number of Samples Possible:35=MAP(D6#,LAMBDA(x,VLOOKUP(x,A6:B12,2,0)))4↓↓=BYROW(H6#,LAMBDA(x,AVERAGE(x)))5Sales RepSales=ARRANGEMENTS(A6:A12,"c",E2)↓↓↓↓=SEQUENCE(ROWS(D6#))^0/ROWS(D6#)6Jo185JoSiouxChin1852502102150.0285714297Sioux250JoSiouxSheliadawn185250310248.33333330.0285714298Chin210JoSiouxGigi185250298244.33333330.0285714299Sheliadawn310JoSiouxTyrone1852504022790.02857142910Gigi298JoSiouxKip185250370268.33333330.02857142911Tyrone402JoChinSheliadawn1852103102350.02857142912Kip370JoChinGigi1852102982310.02857142913JoChinTyrone185210402265.66666670.02857142914JoChinKip1852103702550.02857142915JoSheliadawnGigi185310298264.33333330.02857142916JoSheliadawnTyrone1853104022990.02857142917JoSheliadawnKip185310370288.33333330.02857142918JoGigiTyrone1852984022950.02857142919JoGigiKip185298370284.33333330.02857142920JoTyroneKip1854023703190.02857142921SiouxChinSheliadawn250210310256.66666670.02857142922SiouxChinGigi250210298252.66666670.02857142923SiouxChinTyrone250210402287.33333330.02857142924SiouxChinKip250210370276.66666670.02857142925SiouxSheliadawnGigi2503102982860.02857142926SiouxSheliadawnTyrone250310402320.66666670.02857142927SiouxSheliadawnKip2503103703100.02857142928SiouxGigiTyrone250298402316.66666670.02857142929SiouxGigiKip2502983703060.02857142930SiouxTyroneKip250402370340.66666670.02857142931ChinSheliadawnGigi210310298272.66666670.02857142932ChinSheliadawnTyrone210310402307.33333330.02857142933ChinSheliadawnKip210310370296.66666670.02857142934ChinGigiTyrone210298402303.33333330.02857142935ChinGigiKip210298370292.66666670.02857142936ChinTyroneKip210402370327.33333330.02857142937SheliadawnGigiTyrone310298402336.66666670.02857142938SheliadawnGigiKip3102983703260.02857142939SheliadawnTyroneKip310402370360.66666670.02857142940GigiTyroneKip298402370356.66666670.02857142941Sheet1Cell FormulasRangeFormulaE1E1=COUNTA(A6:A12)E3E3=COMBIN(E1,E2)H3H3=FORMULATEXT(H6)L4L4=FORMULATEXT(L6)D5,N5D5=FORMULATEXT(D6)D6:F40D6=ARRANGEMENTS(A6:A12,"c",E2)H6:J40H6=MAP(D6#,LAMBDA(x,VLOOKUP(x,A6:B12,2,0)))L6:L40L6=BYROW(H6#,LAMBDA(x,AVERAGE(x)))N6:N40N6=SEQUENCE(ROWS(D6#))^0/ROWS(D6#)Dynamic array formulas.


----------



## Xlambda (May 8, 2022)

Single cell formula in D6:

```
=LET(rp,A8:A14,sl,B8:B14,n,E2,
         a,ARRANGEMENTS(rp,"c",n),r,ROWS(a),
         b,MAP(a,LAMBDA(x,XLOOKUP(x,rp,sl))),
         c,BYROW(b,LAMBDA(x,AVERAGE(x))),
         d,SEQUENCE(r)^0/r,
         HSTACK(a,b,c,d)
 )
```
Ch07-ESA.xlsmABCDEFGHIJKL1Pop Size = N =72Sample Size = n =33Number of Samples Possible:3545Sales RepSalesSales Rep 1Sales Rep 2Sales Rep 3Sales 1Sales 2Sales 3XbarP(Sample)6Jo185JoSiouxChin1852502102150.0285714297Sioux250JoSiouxSheliadawn185250310248.33333330.0285714298Chin210JoSiouxGigi185250298244.33333330.0285714299Sheliadawn310JoSiouxTyrone1852504022790.02857142910Gigi298JoSiouxKip185250370268.33333330.02857142911Tyrone402JoChinSheliadawn1852103102350.02857142912Kip370JoChinGigi1852102982310.02857142913JoChinTyrone185210402265.66666670.02857142914JoChinKip1852103702550.02857142915JoSheliadawnGigi185310298264.33333330.02857142916JoSheliadawnTyrone1853104022990.02857142917JoSheliadawnKip185310370288.33333330.02857142918JoGigiTyrone1852984022950.02857142919JoGigiKip185298370284.33333330.02857142920JoTyroneKip1854023703190.02857142921SiouxChinSheliadawn250210310256.66666670.02857142922SiouxChinGigi250210298252.66666670.02857142923SiouxChinTyrone250210402287.33333330.02857142924SiouxChinKip250210370276.66666670.02857142925SiouxSheliadawnGigi2503102982860.02857142926SiouxSheliadawnTyrone250310402320.66666670.02857142927SiouxSheliadawnKip2503103703100.02857142928SiouxGigiTyrone250298402316.66666670.02857142929SiouxGigiKip2502983703060.02857142930SiouxTyroneKip250402370340.66666670.02857142931ChinSheliadawnGigi210310298272.66666670.02857142932ChinSheliadawnTyrone210310402307.33333330.02857142933ChinSheliadawnKip210310370296.66666670.02857142934ChinGigiTyrone210298402303.33333330.02857142935ChinGigiKip210298370292.66666670.02857142936ChinTyroneKip210402370327.33333330.02857142937SheliadawnGigiTyrone310298402336.66666670.02857142938SheliadawnGigiKip3102983703260.02857142939SheliadawnTyroneKip310402370360.66666670.02857142940GigiTyroneKip298402370356.66666670.02857142941All Possible Samples (an) (2)Cell FormulasRangeFormulaE1E1=COUNTA(A6:A12)E3E3=COMBIN(E1,E2)D6:K40D6=LET(rp,A6:A12,sl,B6:B12,n,E2,a,ARRANGEMENTS(rp,"c",n),r,ROWS(a),b,MAP(a,LAMBDA(x,XLOOKUP(x,rp,sl))),c,BYROW(b,LAMBDA(x,AVERAGE(x))),d,SEQUENCE(r)^0/r,HSTACK(a,b,c,d))Dynamic array formulas.


----------



## Xlambda (May 8, 2022)

For the ones familiar with probabilities, ARRANGEMENTS can also be useful to create so-called sample points.
A deck of cards has face cards "F"(A,J,Q,K) and no face cards "NF".
*Task: List all possible distributions of 5 card extractions considering only the ones that contain 2 face "F" cards.*
Will use permutations with repetitions,"pa", n=2 (F,NF) , nr. chosen c=5 with FILTER.
Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRST12aFNF34t,"pa",c,5=BYROW(B6#,LAMBDA(x,SUM(--(x="f"))=2))5=ARRANGEMENTS(B2:C2,"pa",5)↓↓=FILTER(B6#,H6#)6FFFFFFALSEFFNFNFNF7FFFFNFFALSEFNFFNFNF8FFFNFFFALSEFNFNFFNF9FFFNFNFFALSEFNFNFNFF10FFNFFFFALSENFFFNFNF11FFNFFNFFALSENFFNFFNF12FFNFNFFFALSENFFNFNFF13FFNFNFNFTRUENFNFFFNF14FNFFFFFALSENFNFFNFF15FNFFFNFFALSENFNFNFFF16FNFFNFFFALSE17FNFFNFNFTRUEhow many?18FNFNFFFFALSE=ROWS(J6#)19FNFNFFNFTRUE1020FNFNFNFFTRUE21FNFNFNFNFFALSEBecause n<c (2<5) permutations or combinations w/o repetitions ("p" or "c") will not work22NFFFFFFALSE23NFFFFNFFALSE=ARRANGEMENTS(B2:C2,"p",5)24NFFFNFFFALSEnr chosen>n !25NFFFNFNFTRUE26NFFNFFFFALSE=ARRANGEMENTS(B2:C2,"c",5)27NFFNFFNFTRUEnr chosen>n !28NFFNFNFFTRUE29NFFNFNFNFFALSE30NFNFFFFFALSE31NFNFFFNFTRUE32NFNFFNFFTRUE33NFNFFNFNFFALSE34NFNFNFFFTRUE35NFNFNFFNFFALSE36NFNFNFNFFFALSE37NFNFNFNFNFFALSE38Sheet3Cell FormulasRangeFormulaH4H4=FORMULATEXT(H6)B5,J26,J23,K18,J5B5=FORMULATEXT(B6)B6:F37B6=ARRANGEMENTS(B2:C2,"pa",5)H6:H37H6=BYROW(B6#,LAMBDA(x,SUM(--(x="f"))=2))J6:N15J6=FILTER(B6#,H6#)K19K19=ROWS(J6#)J24J24=ARRANGEMENTS(B2:C2,"p",5)J27J27=ARRANGEMENTS(B2:C2,"c",5)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueH6:H37Expression=H6textNOB6:F37Expression=$H6textNO


----------



## Xlambda (May 8, 2022)

For this example, face cards "F"=(J,Q,K), "A" the aces, and the rest no face cards "NF".
*Task: List all possible distributions of 6 card extractions considering only the ones that contain 2 face cards "F" and 1 Ace card "A".*
Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRSTUVWX12aAFNF3=BYROW(B5#,LAMBDA(x,AND(SUM(--(x="a"))=1,SUM(--(x="f"))=2)))4=ARRANGEMENTS(B2:D2,"pa",6)↓↓=FILTER(B5#,I5#)5AAAAAAFALSEchecking AFFNFNFNFhow many ?6AAAAAFFALSE=PERMUTATIONA(3,6)AFNFFNFNF=ROWS(P5#)7AAAAANFFALSE729AFNFNFFNF608AAAAFAFALSEAFNFNFNFF9AAAAFFFALSE=ROWS(B5#)ANFFFNFNF10AAAAFNFFALSE729ANFFNFFNF11AAAANFAFALSEANFFNFNFF12AAAANFFFALSEANFNFFFNF13AAAANFNFFALSEANFNFFNFF14AAAFAAFALSEANFNFNFFF15AAAFAFFALSEFAFNFNFNF16AAAFANFFALSEFANFFNFNF17AAAFFAFALSEFANFNFFNF18AAAFFFFALSEFANFNFNFF19AAAFFNFFALSEFFANFNFNF20AAAFNFAFALSEFFNFANFNF21AAAFNFFFALSEFFNFNFANF22AAAFNFNFFALSEFFNFNFNFA23AAANFAAFALSEFNFAFNFNF24AAANFAFFALSEFNFANFFNF25AAANFANFFALSEFNFANFNFF26AAANFFAFALSEFNFFANFNF27AAANFFFFALSEFNFFNFANF28AAANFFNFFALSEFNFFNFNFA29AAANFNFAFALSEFNFNFAFNF30AAANFNFFFALSEFNFNFANFF31AAANFNFNFFALSEFNFNFFANF32AAFAAAFALSEFNFNFFNFA33AAFAAFFALSEFNFNFNFAF34AAFAANFFALSEFNFNFNFFA35AAFAFAFALSENFAFFNFNF36AAFAFFFALSENFAFNFFNF37AAFAFNFFALSENFAFNFNFF38AAFANFAFALSENFANFFFNF39AAFANFFFALSENFANFFNFF40AAFANFNFFALSENFANFNFFF41AAFFAAFALSENFFAFNFNF42AAFFAFFALSENFFANFFNF43AAFFANFFALSENFFANFNFF44AAFFFAFALSENFFFANFNF45AAFFFFFALSENFFFNFANF46AAFFFNFFALSENFFFNFNFA47AAFFNFAFALSENFFNFAFNF48AAFFNFFFALSENFFNFANFF49AAFFNFNFFALSENFFNFFANF50AAFNFAAFALSENFFNFFNFA51AAFNFAFFALSENFFNFNFAF52AAFNFANFFALSENFFNFNFFA53AAFNFFAFALSENFNFAFFNF54AAFNFFFFALSENFNFAFNFF55AAFNFFNFFALSENFNFANFFF56AAFNFNFAFALSENFNFFAFNF57AAFNFNFFFALSENFNFFANFF58AAFNFNFNFFALSENFNFFFANF59AANFAAAFALSENFNFFFNFA60AANFAAFFALSENFNFFNFAF61AANFAANFFALSENFNFFNFFA62AANFAFAFALSENFNFNFAFF63AANFAFFFALSENFNFNFFAF64AANFAFNFFALSEdown to 729 rowsNFNFNFFFA65AANFANFAFALSE↓↓↓↓↓↓↓↓↓↓Sheet4Cell FormulasRangeFormulaI3I3=FORMULATEXT(I5)B4,K9,W6,K6,P4B4=FORMULATEXT(B5)B5:G733B5=ARRANGEMENTS(B2:D2,"pa",6)I5:I733I5=BYROW(B5#,LAMBDA(x,AND(SUM(--(x="a"))=1,SUM(--(x="f"))=2)))P5:U64P5=FILTER(B5#,I5#)K7K7=PERMUTATIONA(3,6)W7W7=ROWS(P5#)K10K10=ROWS(B5#)Dynamic array formulas.


----------



## Xlambda (May 10, 2022)

Sample point distributions for the same task, all 4 scenarios.
*Task: Sample point distributions of throwing 3 dice that together sum 13.*
*1st scenario - repetitions are allowed, order is important (type argument t=pa)*
*2nd scenario - no repetitions, order is important (type argument t=p)*
*3rd scenario - repetitions are allowed, order not important (type argument t=ca)*
*4th scenario - no repetitions, order not important (type argument t=c)*
ExcelIsFun Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRS1Task: Sample points of throwing  3 dice that togheter sum 1321st scenario - repetitions are allowed, order is important (type argument t=pa)3=PERMUTATIONA(6,3)=ARRANGEMENTS(SEQUENCE(6),"pa",3)4216↓↓=BYROW(D6#,LAMBDA(x,SUM(x)))5=ROWS(D6#)↓↓↓↓=FILTER(D6#,H6#=13)=UNICHAR(J6#+9855)62161113166⚀⚅⚅how many?71124256⚁⚄⚅=ROWS(J6#)8=SEQUENCE(6,,9856)1135265⚁⚅⚄219↓↓=UNICHAR(A10#)1146346⚂⚃⚅109856⚀1157355⚂⚄⚄119857⚁1168364⚂⚅⚃129858⚂1214436⚃⚂⚅139859⚃1225445⚃⚃⚄149860⚄1236454⚃⚄⚃159861⚅1247463⚃⚅⚂161258526⚄⚁⚅171269535⚄⚂⚄181315544⚄⚃⚃191326553⚄⚄⚂201337562⚄⚅⚁211348616⚅⚀⚅221359625⚅⚁⚄2313610634⚅⚂⚃241416643⚅⚃⚂251427652⚅⚄⚁261438661⚅⚅⚀271449281451029146113015173115283215393315410341551135156123616183716293816310391641140165124116613422114432125442136452147462158472169482215492226502237512248522259532261054231655232756233857234958235105923611602417down to 216 rows612428↓↓↓↓↓↓↓↓↓↓622439DICE 1Cell FormulasRangeFormulaA3,B9,R7,J5,N5,A5A3=FORMULATEXT(A4)D3D3=FORMULATEXT(D6)A4A4=PERMUTATIONA(6,3)H4,A8H4=FORMULATEXT(H6)A6A6=ROWS(D6#)D6:F221D6=ARRANGEMENTS(SEQUENCE(6),"pa",3)H6:H221H6=BYROW(D6#,LAMBDA(x,SUM(x)))J6:L26J6=FILTER(D6#,H6#=13)N6:P26N6=UNICHAR(J6#+9855)R8R8=ROWS(J6#)A10:A15A10=SEQUENCE(6,,9856)B10:B15B10=UNICHAR(A10#)Dynamic array formulas.


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNO1Introduction: 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^nc42.) 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.)1213input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]1415function\nc12345678910161,) PERMUTATIONA749343240116807117649823543576480140353607282475249172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!183.) COMBINA728842104629241716300350058008194.) COMBIN72135352171#NUM!#NUM!#NUM!2021Note:It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects22ARR post 1Cell FormulasRangeFormulaC15: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.


----------



## Xlambda (May 10, 2022)

ExcelIsFun Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRS1Task: Sample points of throwing  3 dice that togheter sum 1322nd scenario - no repetitions, order is important (type argument t=p)3=PERMUT(6,3)=ARRANGEMENTS(SEQUENCE(6),"p",3)4120↓↓=BYROW(D6#,LAMBDA(x,SUM(x)))5=ROWS(D6#)↓↓↓↓=FILTER(D6#,H6#=13)=UNICHAR(J6#+9855)61201236256⚁⚄⚅how many?71247265⚁⚅⚄=ROWS(J6#)8=SEQUENCE(6,,9856)1258346⚂⚃⚅129↓↓=UNICHAR(A10#)1269364⚂⚅⚃109856⚀1326436⚃⚂⚅119857⚁1348463⚃⚅⚂129858⚂1359526⚄⚁⚅139859⚃13610562⚄⚅⚁149860⚄1427625⚅⚁⚄159861⚅1438634⚅⚂⚃1614510643⚅⚃⚂1714611652⚅⚄⚁1815281915392015410211561222162923163102416411251651226213627214728215829216930231631234932235103323611342417352439362451137246123825183925310402541141256134226194326311442641245265134631264731484831594931610503216513249523251053326115434185534295634512573461358351959352106035412down to 120 rows6135614↓↓↓↓↓↓↓↓↓↓6236110DICE 2Cell FormulasRangeFormulaA3,B9,R7,J5,N5,A5A3=FORMULATEXT(A4)D3D3=FORMULATEXT(D6)A4A4=PERMUT(6,3)H4,A8H4=FORMULATEXT(H6)A6A6=ROWS(D6#)D6:F125D6=ARRANGEMENTS(SEQUENCE(6),"p",3)H6:H125H6=BYROW(D6#,LAMBDA(x,SUM(x)))J6:L17J6=FILTER(D6#,H6#=13)N6:P17N6=UNICHAR(J6#+9855)R8R8=ROWS(J6#)A10:A15A10=SEQUENCE(6,,9856)B10:B15B10=UNICHAR(A10#)Dynamic array formulas.


----------



## Xlambda (May 10, 2022)

ExcelIsFun Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRS1Task: Sample points of throwing  3 dice that togheter sum 1323rd scenario - repetitions are allowed, order not important (type argument t=ca)3=COMBINA(6,3)=ARRANGEMENTS(SEQUENCE(6),"ca",3)456↓↓=BYROW(D6#,LAMBDA(x,SUM(x)))5=ROWS(D6#)↓↓↓↓=FILTER(D6#,H6#=13)=UNICHAR(J6#+9855)6561113166⚀⚅⚅how many?71124256⚁⚄⚅=ROWS(J6#)8=SEQUENCE(6,,9856)1135346⚂⚃⚅59↓↓=UNICHAR(A10#)1146355⚂⚄⚄109856⚀1157445⚃⚃⚄119857⚁1168129858⚂1225139859⚃1236149860⚄1247159861⚅1258161269171337181348191359201361021144922145102314611241551125156122616613272226282237292248302259312261032233833234934235103523611362441037245113824612392551240256134126614423339433341044335114533612463441147345124834613493551350356145136615524441253445135444614554551456456155746616585551559556166056617616661862DICE 3Cell FormulasRangeFormulaA3,B9,R7,J5,N5,A5A3=FORMULATEXT(A4)D3D3=FORMULATEXT(D6)A4A4=COMBINA(6,3)H4,A8H4=FORMULATEXT(H6)A6A6=ROWS(D6#)D6:F61D6=ARRANGEMENTS(SEQUENCE(6),"ca",3)H6:H61H6=BYROW(D6#,LAMBDA(x,SUM(x)))J6:L10J6=FILTER(D6#,H6#=13)N6:P10N6=UNICHAR(J6#+9855)R8R8=ROWS(J6#)A10:A15A10=SEQUENCE(6,,9856)B10:B15B10=UNICHAR(A10#)Dynamic array formulas.


----------



## Xlambda (May 10, 2022)

ExcelIsFun Ch07-ESA.xlsmABCDEFGHIJKLMNOPQRS1Task: Sample points of throwing  3 dice that togheter sum 1324th scenario - no repetitions, order not important (type argument t=c)3=COMBIN(6,3)=ARRANGEMENTS(SEQUENCE(6),"c",3)420↓↓=BYROW(D6#,LAMBDA(x,SUM(x)))5=ROWS(D6#)↓↓↓↓=FILTER(D6#,H6#=13)=UNICHAR(J6#+9855)6201236256⚁⚄⚅how many?71247346⚂⚃⚅=ROWS(J6#)8=SEQUENCE(6,,9856)125829↓↓=UNICHAR(A10#)1269109856⚀1348119857⚁1359129858⚂13610139859⚃14510149860⚄14611159861⚅1561216234917235101823611192451120246122125613223451223346132435614254561526DICE 4Cell FormulasRangeFormulaA3,B9,R7,J5,N5,A5A3=FORMULATEXT(A4)D3D3=FORMULATEXT(D6)A4A4=COMBIN(6,3)H4,A8H4=FORMULATEXT(H6)A6A6=ROWS(D6#)D6:F25D6=ARRANGEMENTS(SEQUENCE(6),"c",3)H6:H25H6=BYROW(D6#,LAMBDA(x,SUM(x)))J6:L7J6=FILTER(D6#,H6#=13)N6:P7N6=UNICHAR(J6#+9855)R8R8=ROWS(J6#)A10:A15A10=SEQUENCE(6,,9856)B10:B15B10=UNICHAR(A10#)Dynamic array formulas.


----------



## Xlambda (Jun 5, 2022)

An interesting challenge came to my attention: Choose cells that come closest to adding up to a certain total
Arrangements solution.xlsxABCDEFGHIJKLMNOP1Task: Find cells of "Charge" clm that summed will get as  close as possible to clm "A" total, respectively clm "B" totals.2Nr.ChargeAD31200.8866.96133.92Estimating numbers of cells needed to be summed to get to the min value out of the 2 totals (1425.1)42275.8491.95183.8953255.673.03182.57Average "Charge"How many average values to get to smallest amount 1425.164267.1198.41168.7=AVERAGE(B3:B18)=C19/F775187.0658.46128.6258.44755.514079 => 5 or 686250.0492.12157.9297319.09124.86194.23Short preamble about excel limitations and their reflection to the performance of ARRANGEMENTS108325.41113.89211.52Memory resources allocated that Excel formulas have access to, even in background calculations, 119224.4642.09182.37are quite limited when comes to combinatorics calculations minimal requirements.1210226.571.53154.97rows limitation 10485761311281.71114.77166.94Examples1412256.2976.89179.4=MIN(SEQUENCE(1048576))=MIN(SEQUENCE(,1048576))1513222.9478.68144.26111614260.39140.21120.181048576+1 rows1715353.41117.8235.61=MIN(SEQUENCE(1048577))=MIN(SEQUENCE(1048577))1816228.4363.45164.98#VALUE!#VALUE!19Sum4135.161425.12710.0620Or if we call =sequence(1000000,53) works, but =sequence(1000000,54) returns: "Excel run out of resources…" message2122ARRANGEMENTS function is designed first to calculate permutations w repetitions pattern and refining this result 23can get us successively to permutations w/o repetitions, combinations w repetitions or combinations w/o repetitions.2425So, in our case we need to check if combin(16,5) and combin(16,6) will work.26For that we have to check if permutationa(16,5) and permutationa(16,6) will exceed excel resources or not2728=PERMUTATIONA(16,5)291048576 => this will work, value <=104857630rows=PERMUTATIONA(E31:E37,6)=F31#<=1048576311616777216FALSE321511390625FALSE33147529536FALSE34134826809FALSE35122985984FALSE36111771561FALSE37101000000TRUE => for grouping 6 cells the array can have max 10 rows38SUM 1Cell FormulasRangeFormulaA3:A18A3=SEQUENCE(ROWS(B3:B18))F6,F30,I30,F28,F17,I17,F14,I14,H6F6=FORMULATEXT(F7)F7F7=AVERAGE(B3:B18)H7H7=C19/F7F15F15=MIN(SEQUENCE(1048576))I15I15=MIN(SEQUENCE(,1048576))F18,I18F18=MIN(SEQUENCE(1048577))B19:D19B19=SUM(B3:B18)F29F29=PERMUTATIONA(16,5)F31:F37F31=PERMUTATIONA(E31:E37,6)I31:I37I31=F31#<=1048576Dynamic array formulas.


----------



## Xlambda (Jun 5, 2022)

Arrangements solution.xlsxBCDEFGHIJKLMNOPQRSTUVWXYZAA11. Grouping 5 cells. Concept. Step by step2ChargeADIndex pattern calculationSum by row3200.8866.96133.92=ARRANGEMENTS(SEQUENCE(16),"c",5)=BYROW(L6#,LAMBDA(x,SUM(x)))4275.8491.95183.89↓↓Array grouping 5 cells all combinations↓↓Abs diffrenceExtracting smallest 12 values5255.673.03182.57↓↓=ARRANGEMENTS(B3:B18,"c",5)↓↓=ABS(C19-R6#)=SMALL(T6#,SEQUENCE(12))6267.1198.41168.712345200.88275.84255.6267.11187.061186.49238.610.067187.0658.46128.612346200.88275.84255.6267.11250.041249.47175.630.118250.0492.12157.9212347200.88275.84255.6267.11319.091318.52106.580.199319.09124.86194.2312348200.88275.84255.6267.11325.411324.84100.260.2210325.41113.89211.5212349200.88275.84255.6267.11224.461223.89201.210.6311224.4642.09182.37123410200.88275.84255.6267.11226.51225.93199.170.6412226.571.53154.97123411200.88275.84255.6267.11281.711281.14143.960.713281.71114.77166.94123412200.88275.84255.6267.11256.291255.72169.380.7714256.2976.89179.4123413200.88275.84255.6267.11222.941222.37202.730.8815222.9478.68144.26123414200.88275.84255.6267.11260.391259.82165.280.8916260.39140.21120.18123415200.88275.84255.6267.11353.411352.8472.261.1717353.41117.8235.61123416200.88275.84255.6267.11228.431227.86197.241.418228.4363.45164.9812356200.88275.84255.6187.06250.041169.42255.68194135.161425.12710.0612357200.88275.84255.6187.06319.091238.47186.63Index distribution corresponding to these values2012358200.88275.84255.6187.06325.411244.79180.31=INDEX(F6#,XMATCH(V6#,T6#),SEQUENCE(,5))21rows calculation check12359200.88275.84255.6187.06224.461143.84281.264812131522=COMBIN(16,5)123510200.88275.84255.6187.06226.51145.88279.222781113234368123511200.88275.84255.6187.06281.711201.09224.01178101524=ROWS(F6#)123512200.88275.84255.6187.06256.291175.67249.432671015254368123513200.88275.84255.6187.06222.941142.32282.78348131526123514200.88275.84255.6187.06260.391179.77245.33479141527123515200.88275.84255.6187.06353.411272.79152.31468151628123516200.88275.84255.6187.06228.431147.81277.29471215162912367200.88275.84255.6250.04319.091301.45123.65236783012368200.88275.84255.6250.04325.411307.77117.333489153112369200.88275.84255.6250.04224.461206.82218.2881214151632123610200.88275.84255.6250.04226.51208.86216.244710141533123611200.88275.84255.6250.04281.711264.07161.0334123612200.88275.84255.6250.04256.291238.65186.45checking results35123613200.88275.84255.6250.04222.941205.3219.8=BYROW(INDEX(B3:B18,V21#),LAMBDA(x,SUM(x)))-C1936123614200.88275.84255.6250.04260.391242.75182.350.0637123615200.88275.84255.6250.04353.411335.7789.33-0.1138123616200.88275.84255.6250.04228.431210.79214.310.193912378200.88275.84255.6319.09325.411376.8248.28-0.224012379200.88275.84255.6319.09224.461275.87149.23-0.6341123710200.88275.84255.6319.09226.51277.91147.19-0.6442123711200.88275.84255.6319.09281.711333.1291.98-0.743123712200.88275.84255.6319.09256.291307.7117.4-0.7744123713200.88275.84255.6319.09222.941274.35150.750.8845123714200.88275.84255.6319.09260.391311.8113.30.8946123715200.88275.84255.6319.09353.411404.8220.28-1.1747123716200.88275.84255.6319.09228.431279.84145.261.4484368 rows12389200.88275.84255.6325.41224.461282.19142.9149↓↓↓↓↓↓↓123810200.88275.84255.6325.41226.51284.23140.87SUM 2Cell FormulasRangeFormulaF3,R3F3=FORMULATEXT(F6)L5,V35,B24,B22,V20,T5,V5L5=FORMULATEXT(L6)F6:J4373F6=ARRANGEMENTS(SEQUENCE(16),"c",5)L6:P4373L6=ARRANGEMENTS(B3:B18,"c",5)R6:R4373R6=BYROW(L6#,LAMBDA(x,SUM(x)))T6:T4373T6=ABS(C19-R6#)V6:V17V6=SMALL(T6#,SEQUENCE(12))B19:D19B19=SUM(B3:B18)V21:Z32V21=INDEX(F6#,XMATCH(V6#,T6#),SEQUENCE(,5))B23B23=COMBIN(16,5)B25B25=ROWS(F6#)V36:V47V36=BYROW(INDEX(B3:B18,V21#),LAMBDA(x,SUM(x)))-C19Dynamic array formulas.


----------



## Xlambda (Jun 5, 2022)

The function *GPRSUM*, Grouping Sums, calls ARRANGEMENTS (functions set this thread post #24)
*GPRSUM (ar,sm,nc,[fn])*
*ar*: array
*sm*: reference value to be compared
*nc*: number chosen, group size
*[fn]*: first n values that return the smallest differences to reference value in ascending order, first is best, if omitted only first value is returned
Returns as first column the difference to the reference value followed by nc columns with the indexes distribution of the initial array ar 

```
=LAMBDA(ar, sm, nc, [fn],
    LET(
        i, ARRANGEMENTS(SEQUENCE(ROWS(ar)), "c", nc),
        a, INDEX(ar, i, ),
        s, BYROW(a, LAMBDA(x, SUM(x))),
        d, ABS(sm - s),
        m, SMALL(d, SEQUENCE(MAX(fn, 1))),
        x, XMATCH(m, d),
        b, INDEX(i, x, SEQUENCE(, nc)),
        HSTACK(m, b)
    )
)
```
Arrangements solution.xlsxABCDEFGHIJKLM1The function GRPSUM2Nr.ChargeAD31200.8866.96133.92fn argument omitted, => best result42275.8491.95183.89difindex distribution53255.673.03182.570.064812131564267.1198.41168.7=GRPSUM(B3:B18,C19,5)75187.0658.46128.686250.0492.12157.92listing 12 smallest dif's, ascending order, fn=1297319.09124.86194.23=GRPSUM(B3:B18,C19,5,12)108325.41113.89211.520.0648121315119224.4642.09182.370.1127811131210226.571.53154.970.1917810151311281.71114.77166.940.2226710151412256.2976.89179.40.6334813151513222.9478.68144.260.6447914151614260.39140.21120.180.746815161715353.41117.8235.610.77471215161816228.4363.45164.980.882367819Sum4135.161425.12710.060.89348915201.17812141516211.447101415222324Fun fact: The author of the original post uploaded a picture (see attachment)25of some results he managed to calculate manualy, also using 5 grouping26These are:2728Nr.Charge291200.88A1423.93=SUM(G36,G40,G42:G44)302275.84D2711.23=SUM(G29:G35,G37:G39,G41)313255.6324267.11dif335187.06=C19-J29346250.041.17357319.09368325.41In our ranking this correspond to the 11th best.379224.46Conclusion:3810226.5Using the function we were able to find other3911281.7110 distributions better than the "manual" one4012256.294113222.944214260.394315353.414416228.4345SUM 3Cell FormulasRangeFormulaA3:A18,F29:F44A3=SEQUENCE(ROWS(B3:B18))F5:K5F5=GRPSUM(B3:B18,C19,5)F6F6=FORMULATEXT(F5)F9,J33F9=FORMULATEXT(F10)F10:K21F10=GRPSUM(B3:B18,C19,5,12)B19:D19B19=SUM(B3:B18)J29J29=SUM(G36,G40,G42:G44)K29:K30K29=FORMULATEXT(J29)J30J30=SUM(G29:G35,G37:G39,G41)J34J34=C19-J29Dynamic array formulas.


----------



## Xlambda (Jun 5, 2022)

Arrangements solution.xlsxABCDEFGHIJKLMNOPQR12. Grouping 6 cells => max array has to have 10 rows2Nr.ChargeAD31200.8866.96133.92Choosing 10 elements out of total 16 can be another combination problem on itself.42275.8491.95183.89=COMBIN(16,10)53255.673.03182.57There are8008ways to arrange 10 elements to cover all posibilities64267.1198.41168.775187.0658.46128.6At least what we can do is to try iterativley 16-10+1=7 "consecutive" arrays top to bottom,86250.0492.12157.92and see what diffrences will get:97319.09124.86194.23=GRPSUM(B3:B12,C19,6,5)108325.41113.89211.52first 10 B3:B120.001457910119224.4642.09182.37fn=50.5113469101210226.571.53154.975.1913589101311281.71114.77166.945.623569101412256.2976.89179.45.9124569101513222.9478.68144.261614260.39140.21120.18We were lucky, we have found the perfect grouping, dif=01715353.41117.8235.61=GRPSUM(B4:B13,C19,6,5)1816228.4363.45164.98scnd 10 B4:B130.27245891019Sum4135.161425.12710.06fn=55.6124589205.911345892111.471234892211.7834589102324Note: Index values apply to B4:B13 array => relative index numbers252627array nrdifindexes (absolute)2810.0014579102920.27356910113030.27356910113140.5656101112133250.56561011121333615.5269101213143476.09910121314163536range H28:H34=LET(x,GRPSUM(INDEX($B$3:$B$18,SEQUENCE(10,,G28)),$C$19,6),IF(SEQUENCE(,7)>1,x+G28-1,x))37SUM 4Cell FormulasRangeFormulaA3:A18A3=SEQUENCE(ROWS(B3:B18))G4,H17,H9G4=FORMULATEXT(G5)G5G5=COMBIN(16,10)H10:N14H10=GRPSUM(B3:B12,C19,6,5)H18:N22H18=GRPSUM(B4:B13,C19,6,5)B19:D19B19=SUM(B3:B18)H28:N34H28=LET(x,GRPSUM(INDEX($B$3:$B$18,SEQUENCE(10,,G28)),$C$19,6),IF(SEQUENCE(,7)>1,x+G28-1,x))G36G36=FORMULATEXT(H28)Dynamic array formulas.


----------



## Xlambda (Jun 5, 2022)

*Humble conclusion:*
Approaching this challenge using combinatorics is a fair straight solution. Because of its limitations in general, the performance is quite low, but is far better than nothing.
Actually, any combinatorics approach of any task can run out of resources quite fast. With better resources it could be feasible.
For larger arrays, algorithms can be designed to work with arrangements, using partial sections and partial values.
Probably other solutions should be considered, like using statistical tools.
Or using random engines for random selections. It's fun.
Arrangements solution.xlsxABCDEFGHIJKLMNOPQ1To take advantage of the function, we can also try random selection until we get satisfactory results.2Nr.ChargeADRandom selection 10 elements, 6 cells grouping31200.8866.96133.92random 10 indexes42275.8491.95183.89=INDEX(D26#,SEQUENCE(10))53255.673.03182.57↓↓↓indexes valuesHit F9 until dif is small enough64267.1198.41168.7↓↓↓=INDEX(B3:B18,F7#)75187.0658.46128.65187.060.18345681086250.0492.12157.9214260.3997319.09124.86194.2310226.5Values extraction108325.41113.89211.529224.46=INDEX(H7#,K7:P7)119224.4642.09182.3712256.29226.5224.46256.29200.88250.04267.111210226.571.53154.971200.881311281.71114.77166.9416228.431412256.2976.89179.46250.041513222.9478.68144.2615353.411614260.39140.21120.184267.111715353.41117.8235.611816228.4363.45164.9819Sum4135.161425.12710.06202122Random selection engine indexes23=SEQUENCE(16)24↓↓↓=RANDARRAY(16)25↓↓↓↓↓↓=SORTBY(B26#,C26#)2610.415152720.9496142830.9619102940.827593050.0239123160.643613270.9423163380.905163490.31861535100.1133436110.87921137120.3674838130.9935739140.0692240150.7518341160.60921342SUM 5Cell FormulasRangeFormulaF4,B23F4=FORMULATEXT(F7)H6,D25,K10H6=FORMULATEXT(H7)F7:F16F7=INDEX(D26#,SEQUENCE(10))H7:H16H7=INDEX(B3:B18,F7#)J7:P7J7=GRPSUM(H7#,C19,6)K11:P11K11=INDEX(H7#,K7:P7)C24C24=FORMULATEXT(C26)B26:B41B26=SEQUENCE(16)C26:C41C26=RANDARRAY(16)D26:D41D26=SORTBY(B26#,C26#)Dynamic array formulas.


----------



## Xlambda (Aug 15, 2022)

Latest update, check this out: Cells-that-come-closest-to-adding-up-to-a-certain-total


----------



## st001 (Sep 21, 2022)

Really wonderful function!
I couldn't get this to work for an array {1, 2, 3, 4, 5, 6, 7}, for "c", with the nc = 7. 
It freezes my Excel. 
Maybe it's just me? 
Or its trying to do too many recursions, on both T_CA and T_P?


----------



## Xlambda (Aug 17, 2021)

*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.xlsxABCDEFGHIJKLMNO1Introduction: 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^nc42.) 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.)1213input n:7Table of values returned by all functions for an array of n objects ,nc [1,10]1415function\nc12345678910161,) PERMUTATIONA749343240116807117649823543576480140353607282475249172,) PERMUT742210840252050405040#NUM!#NUM!#NUM!183.) COMBINA728842104629241716300350058008194.) COMBIN72135352171#NUM!#NUM!#NUM!2021Note:It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects22ARR post 1Cell FormulasRangeFormulaC15: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.


----------



## Xlambda (Sep 21, 2022)

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 (Sep 21, 2022)

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 (Sep 21, 2022)

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
Book1ABCDEFGHI123123456745=ARRANGEMENTS(B3:H3,"c",7)6123456778Sheet1Cell FormulasRangeFormulaB5B5=FORMULATEXT(B6)B6:H6B6=ARRANGEMENTS(B3:H3,"c",7)Dynamic array formulas.


----------



## Xlambda (Sep 21, 2022)

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.
Book1ABCDEFGHIJKLMNOPQRSTUVWXYZAA1123456723=PERMUTATIONA(7,7)=ROWS(B7#)=PERMUT(7,7)=ROWS(K7#)=COMBINA(7,7)=ROWS(T7#)4823543823543504050401716171656=ARRANGEMENTS(B1#,"pa",7)=ARRANGEMENTS(B1#,"p",7)=ARRANGEMENTS(B1#,"ca",7)7111111112345671111111811111121234576111111291111113123465711111131011111141234675111111411111111512347561111115121111116123476511111161311111171235467111111714111112112354761111122151111122123564711111231611111231235674111112417111112412357461111125181111125123576411111261911111261236457111112720111112712364751111133211111131123654711111342211111321236574111113523111113312367451111136241111134123675411111372511111351237456111114426111113612374651111145271111137123754611111462811111411237564111114729111114212376451111155301111143123765411111563111111441243567111115732111114512435761111166331111146124365711111673411111471243675111117735111115112437561111222361111152124376511112233711111531245367111122438111115412453761111225391111155124563711112264011111561245673111122741111115712457361111233421111161124576311112344311111621246357111123544111116312463751111236451111164124653711112374611111651246573111124447111116612467351111245481111167124675311112464911111711247356111124750111117212473651111255511111173124753611112565211111741247563111125753111117512476351111266541111176124765311112675511111771253467111127756111121112534761111333571111212125364711113345811112131253674111133559111121412537461111336601111215125376411113376111112161254367111134462111121712543761111345631111221125463711113466411112221254673111134765111122312547361111355661111224125476311113566711112251256347111135768111122612563741111366691111227125643711113677011112311256473111137771111123212567341111444721111233125674311114457311112341257346111144674111123512573641111447751111236125743611114557611112371257463111145677111124112576341111457781111242125764311114667911112431263457111146780111124412634751111477Sheet2Cell FormulasRangeFormulaB1:H1B1=SEQUENCE(,7)B3,T6,K6,B6,O3,G3,X3,T3,K3B3=FORMULATEXT(B4)B4B4=PERMUTATIONA(7,7)G4G4=ROWS(B7#)K4K4=PERMUT(7,7)O4,X4O4=ROWS(K7#)T4T4=COMBINA(7,7)B7:H823549B7=ARRANGEMENTS(B1#,"pa",7)K7:Q5046K7=ARRANGEMENTS(B1#,"p",7)T7:Z1722T7=ARRANGEMENTS(B1#,"ca",7)Dynamic array formulas.


----------



## st001 (Sep 22, 2022)

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 (Nov 24, 2022)

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.xlsxABCDEFGHIJKLM12=T_PA(3,6)-1=BYROW(T_PA(3,6)-1,LAMBDA(x,TEXTJOIN("-",,x)))30000000-0-0-0-0-040000010-0-0-0-0-1checking rows50000020-0-0-0-0-2=PERMUTATIONA(3,6)60000100-0-0-0-1-072970000110-0-0-0-1-180000120-0-0-0-1-2=ROWS(B3#)90000200-0-0-0-2-0729100000210-0-0-0-2-1110000220-0-0-0-2-2=ROWS(I3#)120001000-0-0-1-0-0729130001010-0-0-1-0-1140001020-0-0-1-0-2150001100-0-0-1-1-0160001110-0-0-1-1-1170001120-0-0-1-1-2180001200-0-0-1-2-0190001210-0-0-1-2-1200001220-0-0-1-2-2210002000-0-0-2-0-0220002010-0-0-2-0-1230002020-0-0-2-0-2240002100-0-0-2-1-0250002110-0-0-2-1-1260002120-0-0-2-1-2270002200-0-0-2-2-0280002210-0-0-2-2-1290002220-0-0-2-2-2300010000-0-1-0-0-0310010010-0-1-0-0-1320010020-0-1-0-0-2330010100-0-1-0-1-0340010110-0-1-0-1-1350010120-0-1-0-1-2360010200-0-1-0-2-0370010210-0-1-0-2-1380010220-0-1-0-2-2390011000-0-1-1-0-0400011010-0-1-1-0-1410011020-0-1-1-0-2420011100-0-1-1-1-0430011110-0-1-1-1-1440011120-0-1-1-1-2450011200-0-1-1-2-0460011210-0-1-1-2-1470011220-0-1-1-2-2480012000-0-1-2-0-0490012010-0-1-2-0-1500012020-0-1-2-0-2510012100-0-1-2-1-0520012110-0-1-2-1-1530012120-0-1-2-1-2540012200-0-1-2-2-0550012210-0-1-2-2-1560012220-0-1-2-2-2570020000-0-2-0-0-0580020010-0-2-0-0-1590020020-0-2-0-0-2600020100-0-2-0-1-0610020110-0-2-0-1-1620020120-0-2-0-1-2630020200-0-2-0-2-0640020210-0-2-0-2-1650020220-0-2-0-2-2660021000-0-2-1-0-0670021010-0-2-1-0-1680021020-0-2-1-0-2690021100-0-2-1-1-0700021110-0-2-1-1-1710021120-0-2-1-1-2720021200-0-2-1-2-0730021210-0-2-1-2-1740021220-0-2-1-2-2750022000-0-2-2-0-0760022010-0-2-2-0-1770022020-0-2-2-0-2780022100-0-2-2-1-0790022110-0-2-2-1-1800022120-0-2-2-1-2810022200-0-2-2-2-0820022210-0-2-2-2-1830022220-0-2-2-2-2840100000-1-0-0-0-0850100010-1-0-0-0-1860100020-1-0-0-0-2870100100-1-0-0-1-0880100110-1-0-0-1-1890100120-1-0-0-1-2900100200-1-0-0-2-0Bill YTCell FormulasRangeFormulaB2,K11,K8,K5,I2B2=FORMULATEXT(B3)B3:G731B3=T_PA(3,6)-1I3:I731I3=BYROW(T_PA(3,6)-1,LAMBDA(x,TEXTJOIN("-",,x)))K6K6=PERMUTATIONA(3,6)K9K9=ROWS(B3#)K12K12=ROWS(I3#)Dynamic array formulas.


----------

