# WR  	Set of functions and study for solving the Word Maze Excel Challenge



## Xlambda (Oct 27, 2021)

*WR: * *W*ord maze *R*esults challenge. Please do see latest (26-Oct-21) YT video of MrExcel Solving Word Search With Excel (FMWC) - 2439
This is a compact set of functions that can solve all lookup scenarios.
Defined names: *ar*, sample array ( *A1:T15* )  ; *rw*, nr. rows of sample array (*15*) (width is fixed to 20) ; *cs*, constant array values for "snake" lookup *{-1,1, 20,-20} *; *cd*, constant array values for "diagonal" lookup *{-21,-19,19,21}*

*WL(l)=LAMBDA(l,LET(x,IF(ar=l,1),y,IF(ar="?",1),z,IF(x+y,1),v,IF(z,SEQUENCE(rw,20)),SMALL(v,SEQUENCE(COUNT(v)))))*
*WA(a,c)=LAMBDA(a,c,LET(x,a-c,y,IF(x>0,x),UNIQUE(SMALL(y,SEQUENCE(COUNT(y ))))))
WB(a,b)=LAMBDA(a,b,FILTER(a,IFNA(XMATCH(a,b,,2),0)))*
*WR(w,c,[r],[i ])=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y )),n-1))))*
Last one *WR is !! recursive !!* Define the names, then the functions and we are good to go. (Gaps in [i ] are to keep the text no to turn italics)
*Will come back with the "concept" explanation asap.*

```
=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,
     IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y)),n-1))
    )
)
```
WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Z1SGPE2THEDIZBANTSNSdefined names2SP?AKIWK1FHVZG2IEKAXar: A1:T15 (sample array)3?ITQE?LASIXQJNSX4TJKrw: 15 (width is always 20)4LENJFXA4TIKNSBX1DJCPcs: {-1,1,20,-20} (constant array for "snake" type lookup5GDL33DMVLTGK?CU3OQOOcd: {-21,-19,19,21} (constant array for "diagonal" type lookup6IAIUVGYLU?IEP3WGKBTP74APAU4CZVFFFAFIPAXTT"snake" lookup"diagonal" lookup8HN?4GCKS4IDRHNNAJDNS9EC3XCODBVQOYJERGSKAH=WR("snake",cs)=WR("diagonal",cd)10EX1Z?PNI3OWCCN2UREGJ38211EONMEVKABEPFLQNZ?EW31812KAN?AIBKLG24?N?ZBNQE20=WR("advance",cd)13SMO4YFBBRE?DNE1ADJM41606714CFX43TY1ZJNIWUZ3OWHM22415TYD2YZSUDVVF4OZEKA?S3001617=SEQUENCE(rw,20)=WR("difficult",cs)181234567891011121314151617181920151192122232425262728293031323334353637383940204142434445464748495051525354555657585960=WR("winner",cs)2161626364656667686970717273747576777879801152281828384858687888990919293949596979899100273231011021031041051061071081091101111121131141151161171181191202412112212312412512612712812913013113213313413513613713813914025141142143144145146147148149150151152153154155156157158159160Note: I left the results in "sequence numbers" representation on purpose,26161162163164165166167168169170171172173174175176177178179180to be much easier to visualize the results.27181182183184185186187188189190191192193194195196197198199200To get them to "range" representation we can use PR(a)  (print results)28201202203204205206207208209210211212213214215216217218219220PR(a)=LAMBDA(a,LET(x,QUOTIENT(a-1,20)+1,y,MOD(a-1,20)+1,ADDRESS(x,y,4)))292212222232242252262272282292302312322332342352362372382392403024124224324424524624724824925025125225325425525625725825926031261262263264265266267268269270271272273274275276277278279280C1B532281282283284285286287288289290291292293294295296297298299300R133T1G434Important Note: Covered here only snake and diagonal search, for being the more complicated ones.T835Anyhow, to cover the rest of them the only thing we have to do is defining new constant arraysD1236cs (constant snake) {-1,1,20,-20}T1537cd (constant diagonal) {-21,-19,19,21}38For example, for horizontal lookup we have to defineK839ch {-1,1} and for vertical cv: {-20,20}40O641M144243WM snake diagonalCell FormulasRangeFormulaW9,W20,W17,A17,AC12,AC9W9=FORMULATEXT(W10)W10:W15W10=WR("snake",cs)AC10AC10=WR("diagonal",cd)AC13AC13=WR("advance",cd)A18:T32A18=SEQUENCE(rw,20)W18W18=WR("difficult",cs)W21:W22W21=WR("winner",cs)W31:W36W31=PR(W10#)AC31AC31=PR(AC10)AC33,W38AC33=PR(AC13)W40:W41W40=PR(W21#)Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15AC13, AC10, W21, W18, W10


----------



## MrExcel (Oct 27, 2021)

This is beautiful. You are successfully dealing with the wildcards, a concept which I abandoned. 

The big question: do you think this is 30 minutes of dev time or less?  I did end up solving the Diagonals with my VBA, but it was outside the 30 minute time limit.


----------



## Xlambda (Oct 28, 2021)

Thanks a lot !!?
When I first saw the challenge, I had a different approach. I have already in my lambda library a function, AKEEP(array,string,[d]) that replaces all the chars of any array <> than the "string" chars with a delimiter "d" or not ("" null string)
I simulated a rand array 1000 x 20 of letters and run the function AKEEP for different words. The function replaced with null strings all the letters not found in word argument.
Then I realized that without a real sample I cannot test it effectively.
Tactics strategy time on this case was 1 sec since I already had the function. The problem was to have powerful enough eye muscles for 30 min.
So, I really cannot tell how many words I would have been able to find using this method, without a real sample. Depends a lot of the letter (numbers) clusters distribution.
Then I have abandoned AKEEP method to a more "scientific" one, because I thought the challenge is so cool that deserves one, off time challenge. ✌


----------



## Xlambda (Oct 29, 2021)

WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD11234567891011121314151617181920Concept. Part 122122232425262728293031323334353637383940Finding constant array values for different types of lookups:3414243444546474849505152535455565758596046162636465666768697071727374757677787980Random 3x3 area of sequence array5818283848586878889909192939495969798991001251261276101102103104105106107108109110111112113114115116117118119120145146147712112212312412512612712812913013113213313413513613713813914016516616781411421431441451461471481491501511521531541551561571581591609161162163164165166167168169170171172173174175176177178179180If we substract the center value, we get relative values10181182183184185186187188189190191192193194195196197198199200for any 3x3 area of the sequence11201202203204205206207208209210211212213214215216217218219220=V5:X7-W612221222223224225226227228229230231232233234235236237238239240-21-20-1913241242243244245246247248249250251252253254255256257258259260-101142612622632642652662672682692702712722732742752762772782792801920211528128228328428528628728828929029129229329429529629729829930016For snake: cs={-20,-1,1.20} or {-1,1,20,-20}17Note: order is not important18since values extracted using these constants19are unique and sorted with UNIQUE(SMALL….20-2021-1122202324For diagonal: cd={-21,-19,19,21}25-21-192627192128cnst arrayCell FormulasRangeFormulaA1:T15A1=SEQUENCE(15,20)V11V11=FORMULATEXT(V12)V12:X14V12=V5:X7-W6Dynamic array formulas.


----------



## Xlambda (Oct 29, 2021)

WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Concept. Part 2. Functions2- functionality for word "winner" , snake search , constant array cs={-1,1,20,-20} 3step 1. WL(l)=LAMBDA(l,LET(x,IF(ar=l,1),y,IF(ar="?",1),z,IF(x+y,1),v,IF(z,SEQUENCE(rw,20)),SMALL(v,SEQUENCE(COUNT(v)))))4extracts only the matching sequence values for ar="letter" or ar="?"5the SMALL has the role of "flatten" the matching values6We start backwards with the last letter of "winner", "r"78=WL("r")step 2.  WA(a,c)=LAMBDA(a,c,LET(x,a-c,y,IF(x>0,x),UNIQUE(SMALL(y,SEQUENCE(COUNT(y))))))923extracts the sequence unique values of WL("r")-cs, all sequence values corresponding to "cs"10411146=A9#-cs=WA(A9#,cs)step 3. among these sequence values we have to filter the values that match next letter "e"129324223433using WB(a,b)=LAMBDA(a,b,FILTER(a,IFNA(XMATCH(a,b,,2),0)))131104240216121141434745266622=WB(H12#,WL("e"))step 4. repeat from step 2 and so on until we run out of letters151529492731132445=WA(K15#,cs)16175111109901302617425=WB(N16#,WL("n"))17185144142123163401984446181971531511321724220546154=WA(P17#,cs)=WB(R19#,WL("n"))19217176174155195432176518526155202241861841652054521815419445271212331981961772174725017321747222352182161972376117523866=WA(T19#,cs)=WB(U23#,WL("i"))2324922522320424466178251134135135242512342322132537318515315425125299236234215255901941551562722625024822926992197165175=WA(W23#,cs)2725225023127194198174251115last step, for last letter "w"28300298279319109199184270134=WB(X27#,WL("w"))29111204186272136115301132061932911552733112321619523132130217197250All these steps are embedded 33132218205252in the recursive function WR34142219214271Since we started from last to first35144225216273these represent also the final result36151230218292371532372313815523823739163249239401652512504117227025242174258431762714417745184461864719548196491975019851204522055321354215552165621757218582235922560229612316223263234642366523766244672486825069252702537125572269732717427975298763007731978functionsCell FormulasRangeFormulaA8,Z28,X26,U22,W22,R18,T18,P16,N15,K14,H11,C11A8=FORMULATEXT(A9)A9:A25A9=WL("r")C12:F28C12=A9#-csH12:H77H12=WA(A9#,cs)K15:K21K15=WB(H12#,WL("e"))N16:N41N16=WA(K15#,cs)P17:P23P17=WB(N16#,WL("n"))R19:R43R19=WA(P17#,cs)T19:T20T19=WB(R19#,WL("n"))U23:U30,X27:X36U23=WA(T19#,cs)W23:W25W23=WB(U23#,WL("i"))Z29:Z30Z29=WB(X27#,WL("w"))Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15A9, K15, P17, T19, W23, Z29


----------



## Xlambda (Oct 30, 2021)

WORD MAZE.xlsxABCDEFGHIJ1Concept. Part 3. The recursive function2 =LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),
                                             z,IF(AND(r=0),WA(WL(x),c),WA(r,c)),
                                             IF(n=1,r,WR(w,c,WB(z,WL(y)),n-1))))3w: word4c: constant array, our case is cs5[r]: carries the result6[i ]: carries the iteration nr.78first iteration WR("winner",cs)9i is omitted<=>i=0 => n=LEN("winner")=610x=MID(w,6,1)="r"11y=MID(w,6-1,1)="e"12r is omitted<=>r=0 =>z=WA(WL("r"),cs)13n=6 (<>1) so so function calls itself WR(w,cs,WB(z,WL("e")),6-1) => new r=r1=WB(z,WL("e")) new i=i1=6-1=5142nd iteration i=i1=5 =>n=515x=MID(w,5,1)="e" (x is relevant only for first iteration)16y=MID(w,5-1,1)="n"17r is now r=r1 =>z=WA(r1,cs)18n=5 (<>1) so function calls itself again WR(w,cs,WB(z,WL("n")),5-1) => new r=r2=WB(z,WL("n")) => new i=i2=5-1=4193rd iteration i=i2=4 =>n=420y=MID(w,4-1,1)="n" (2nd "n")21z=WA(r2,cs)22n=4 (<>1) so function calls itself again WR(w,cs,WB(z,WL("n")),4-1) => new r=r3=WB(z,WL("n")) => new i=i3=4-1=3234th iteration i=i3=3 =>n=324y=MID(w,3-1,1)="i"25z=WA(r3,cs)26n=3 (<>1) so function calls itself again WR(w,cs,WB(z,WL("i")),3-1) => new r=r4=WB(z,WL("i")) => new i=i4=3-1=2275th iteration i=i4=2 =>n=228y=MID(w,2-1,1)="w"29z=WA(r4,cs)30n=2 (<>1) so function calls itself again WR(w,cs,WB(z,WL("w")),2-1) => new r=r5=WB(z,WL("w")) => new i=i5=2-1=1316th iteration i=i5=1 =>n=132x,y,znot relevand because exit recursion loop is met IF(n=1,r,….)33n=1, exit conditon, => WR will return r, last value of r, r=r5 (calculated in previous iteration)34Done !!Sheet3


----------



## Xlambda (Oct 30, 2021)

WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP1AKEEPfunction approach, the eye muscle solution2=ar=AKEEP(A3#,"DIFFICULT"&"?")3Z1SGPE2THEDIZBANTSNS TDIT4SP?AKIWK1FHVZG2IEKAX?IFI5?ITQE?LASIXQJNSX4TJK?IT?LIT6LENJFXA4TIKNSBX1DJCPLFTIDC7GDL33DMVLTGK?CU3OQOODLDLT?CU8IAIUVGYLU?IEP3WGKBTPIIULU?IT94APAU4CZVFFFAFIPAXTTUCFFFFITT10HN?4GCKS4IDRHNNAJDNS?CIDD11EC3XCODBVQOYJERGSKAHCCD12EX1Z?PNI3OWCCN2UREGJ?ICCU13EONMEVKABEPFLQNZ?EW3FL?14KAN?AIBKLG24?N?ZBNQE?IL??15SMO4YFBBRE?DNE1ADJM4F?DD16CFX43TY1ZJNIWUZ3OWHMCFTIU17TYD2YZSUDVVF4OZEKA?STDUDF?1819answer:K102021Note: AKEEP was designed case sensitive22AKEEPCell FormulasRangeFormulaA2,V2A2=FORMULATEXT(A3)A3:T17A3=arV3:AO17V3=AKEEP(A3#,"DIFFICULT"&"?")Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15A3


----------



## Xlambda (Oct 30, 2021)

WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWX1If we don't have AKEEP, we can write a simple lambda "formula"2w=difficult3We need to calculate IF(ar="d",ar,"")& IF(ar="i",ar,"")& IF(ar="f",ar,"")&….... IF(ar="?",ar,"")4We can do this "recursively" , using REDUCE, for every letter of"w" plus for "?"5=REDUCE("",SEQUENCE(LEN(B2)+1),LAMBDA(v,i,v&IF(ar=MID(B2&"?",i,1),ar,"")))6 TDIIT7?IIFFII8?IIT?LIIT9LFFTIIDC10DLDLT?CU11IIIIULU?IIT12UCFFFFFFFFIITT13?CIIDD14CCD15?IICCU16FFL?17?IIL??18FF?DD19CFFTIIU20TDUDFF?2122We noticed that if there are double letters ("f" and "i") we get 2 chars, we can amend this final result with LEFT(array,1)23=LEFT(REDUCE("",SEQUENCE(LEN(B2)+1),LAMBDA(v,i,v&IF(ar=MID(B2&"?",i,1),ar,""))),1)24 TDIT25?IFI26?IT?LIT27LFTIDC28DLDLT?CU29IIULU?IT30UCFFFFITT31?CIDD32CCD33?ICCU34FL?35?IL??36F?DD37CFTIU38TDUDF?39AKEEP rplcCell FormulasRangeFormulaA5,A23A5=FORMULATEXT(A6)A6:T20A6=REDUCE("",SEQUENCE(LEN(B2)+1),LAMBDA(v,i,v&IF(ar=MID(B2&"?",i,1),ar,"")))A24:T38A24=LEFT(REDUCE("",SEQUENCE(LEN(B2)+1),LAMBDA(v,i,v&IF(ar=MID(B2&"?",i,1),ar,""))),1)Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15A6, A24


----------



## Xlambda (Oct 31, 2021)

*Horizontal /Vertical lookup using same set of formulas, require only distinct set of constant arrays, ch and cv*.
*Trick: To work with different "ar" values in different spreadsheets, and not changing anything, the syntax in name manager for "ar" should be =!A1:T15 (only with "!" in front)*
*Functions versatility:*
*-2 words on same row/column, same/opposite orientation
-"?" mark can be anywhere, middle, end, beginning of a word*
*-words can share same letters or even "?", no matter the orientation*
WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1SNAKSNAKEJSNA?EUVFECDefined names21TDDPUF3MFNUFPDWDGIDch/cv constant array values for horiz/vert lookup3WN4EH3DZNPAB2IXBRJHSch={-1,1}4ICKCCZT3LYKHQLLOLQ1Rcv={-20,20}5NKMR4VWINNE?FRE?NIWM6NJXMTDVNRR2THCOQRMTKhoriz. Lookup7EECPNICSEJTMG1IVRX?U=WR("winner",ch)=WR("snake",ch)=WR("difficult",ch)8?YD?FFIC?L?KYNWYL3NT8751439EZHJOFURNBUIS2FHI1A1991110NHFNFIUHILC4E1FLVAK327229911NK3BMCH2WXIWMG3YTCES12INJMM?IBRLFDLZMTODKC13WIHRLLOVY2FS1NKDHBAPvert.lookup14S1MKWTRENNI?HTOPTQNB=WR("winner",cv)=WR("snake",cv)=WR("difficult",cv)15UCPSCVNYPGDIT1EKAN?U41111061620913929117=SEQUENCE(15,20)241299181234567891011121314151617181920192122232425262728293031323334353637383940204142434445464748495051525354555657585960horiz. Lookup216162636465666768697071727374757677787980=PR(W8#)=PR(AA8#)=PR(AE8)2281828384858687888990919293949596979899100G5E1C823101102103104105106107108109110111112113114115116117118119120S5K124121122123124125126127128129130131132133134135136137138139140L14S15251411421431441451461471481491501511521531541551561571581591602616116216316416516616716816917017117217317417517617717817918027181182183184185186187188189190191192193194195196197198199200vert.lookup28201202203204205206207208209210211212213214215216217218219220=PR(W15#)=PR(AA15#)=PR(AE15#)29221222223224225226227228229230231232233234235236237238239240A3K1F630241242243244245246247248249250251252253254255256257258259260I11S7K1531261262263264265266267268269270271272273274275276277278279280A13S153228128228328428528628728828929029129229329429529629729829930033ch cvCell FormulasRangeFormulaW7,AA7,AE7,AE28,AA28,W28,AE21,AA21,W21,A17,W14,AA14,AE14W7=FORMULATEXT(W8)W8:W10W8=WR("winner",ch)AA8:AA10AA8=WR("snake",ch)AE8AE8=WR("difficult",ch)W15:W17W15=WR("winner",cv)AA15:AA17AA15=WR("snake",cv)AE15:AE16AE15=WR("difficult",cv)A18:T32A18=SEQUENCE(15,20)W22:W24,AE29:AE30,AA29:AA31,W29:W31,AA22:AA24W22=PR(W8#)AE22AE22=PR(AE8)Dynamic array formulas.


----------



## Xlambda (Nov 2, 2021)

Something that I wanted to mention before, regarding how important is *binary search* in XMATCH or XLOOKUP functions when we work with large amount of data, if the lookup array is sorted.
I have used it in WB function =LAMBDA(a,b,FILTER(a,IFNA(*XMATCH(a,b,,2)*,0))) and in other functions I wrote before.
Here is a very simple experiment to test time calculation diffrences between these 2 methods on same data set, first to last vs. binary ascending order.( match mode - exact)
For a 700 000 rows vertical array - calc time - *6.8 sec* - exact match, *first to last.*
For a 700 000 rows vertical array - calc time -* 0.9 sec *- exact match, *binary search ascending order*
WORD MAZE.xlsxABCDEFGHIJKLM1Binary search modedata set in D3: =SORTBY(SEQUENCE(700000),RANDARRAY(700000))2=SEQUENCE(700000)=XMATCH(D3:D700002,B3#)=XMATCH(D3:D700002,B3#,,2)31186502186502first to last186502binary4267571967571967571953607066607066607066641374921374921374927597469974699746986385721385721385721974045574045574045571084057304057304057301191357741357741357741210619625619625619625131121892921892921892914123451103451103451101513264918264918264918161457889578895788917156273446273446273441816662936662936662936191758047580475804720183362883362883362882119597464597464597464222018144518144518144523215904795904795904792422433279433279433279252346325746325746325726244401004401004401002725211604211604211604282653267553267553267529275404615404615404613028574045574045down to5740453129577899577899700000 rws.57789932309988299882↓↓↓↓998823331681977681977681977Sheet6Cell FormulasRangeFormulaB2,J2,F2B2=FORMULATEXT(B3)B3:B700002B3=SEQUENCE(700000)F3:F700002F3=XMATCH(D3:D700002,B3#)J3:J700002J3=XMATCH(D3:D700002,B3#,,2)Dynamic array formulas.


----------



## Xlambda (Oct 27, 2021)

*WR: * *W*ord maze *R*esults challenge. Please do see latest (26-Oct-21) YT video of MrExcel Solving Word Search With Excel (FMWC) - 2439
This is a compact set of functions that can solve all lookup scenarios.
Defined names: *ar*, sample array ( *A1:T15* )  ; *rw*, nr. rows of sample array (*15*) (width is fixed to 20) ; *cs*, constant array values for "snake" lookup *{-1,1, 20,-20} *; *cd*, constant array values for "diagonal" lookup *{-21,-19,19,21}*

*WL(l)=LAMBDA(l,LET(x,IF(ar=l,1),y,IF(ar="?",1),z,IF(x+y,1),v,IF(z,SEQUENCE(rw,20)),SMALL(v,SEQUENCE(COUNT(v)))))*
*WA(a,c)=LAMBDA(a,c,LET(x,a-c,y,IF(x>0,x),UNIQUE(SMALL(y,SEQUENCE(COUNT(y ))))))
WB(a,b)=LAMBDA(a,b,FILTER(a,IFNA(XMATCH(a,b,,2),0)))*
*WR(w,c,[r],[i ])=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y )),n-1))))*
Last one *WR is !! recursive !!* Define the names, then the functions and we are good to go. (Gaps in [i ] are to keep the text no to turn italics)
*Will come back with the "concept" explanation asap.*

```
=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,
     IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y)),n-1))
    )
)
```
WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Z1SGPE2THEDIZBANTSNSdefined names2SP?AKIWK1FHVZG2IEKAXar: A1:T15 (sample array)3?ITQE?LASIXQJNSX4TJKrw: 15 (width is always 20)4LENJFXA4TIKNSBX1DJCPcs: {-1,1,20,-20} (constant array for "snake" type lookup5GDL33DMVLTGK?CU3OQOOcd: {-21,-19,19,21} (constant array for "diagonal" type lookup6IAIUVGYLU?IEP3WGKBTP74APAU4CZVFFFAFIPAXTT"snake" lookup"diagonal" lookup8HN?4GCKS4IDRHNNAJDNS9EC3XCODBVQOYJERGSKAH=WR("snake",cs)=WR("diagonal",cd)10EX1Z?PNI3OWCCN2UREGJ38211EONMEVKABEPFLQNZ?EW31812KAN?AIBKLG24?N?ZBNQE20=WR("advance",cd)13SMO4YFBBRE?DNE1ADJM41606714CFX43TY1ZJNIWUZ3OWHM22415TYD2YZSUDVVF4OZEKA?S3001617=SEQUENCE(rw,20)=WR("difficult",cs)181234567891011121314151617181920151192122232425262728293031323334353637383940204142434445464748495051525354555657585960=WR("winner",cs)2161626364656667686970717273747576777879801152281828384858687888990919293949596979899100273231011021031041051061071081091101111121131141151161171181191202412112212312412512612712812913013113213313413513613713813914025141142143144145146147148149150151152153154155156157158159160Note: I left the results in "sequence numbers" representation on purpose,26161162163164165166167168169170171172173174175176177178179180to be much easier to visualize the results.27181182183184185186187188189190191192193194195196197198199200To get them to "range" representation we can use PR(a)  (print results)28201202203204205206207208209210211212213214215216217218219220PR(a)=LAMBDA(a,LET(x,QUOTIENT(a-1,20)+1,y,MOD(a-1,20)+1,ADDRESS(x,y,4)))292212222232242252262272282292302312322332342352362372382392403024124224324424524624724824925025125225325425525625725825926031261262263264265266267268269270271272273274275276277278279280C1B532281282283284285286287288289290291292293294295296297298299300R133T1G434Important Note: Covered here only snake and diagonal search, for being the more complicated ones.T835Anyhow, to cover the rest of them the only thing we have to do is defining new constant arraysD1236cs (constant snake) {-1,1,20,-20}T1537cd (constant diagonal) {-21,-19,19,21}38For example, for horizontal lookup we have to defineK839ch {-1,1} and for vertical cv: {-20,20}40O641M144243WM snake diagonalCell FormulasRangeFormulaW9,W20,W17,A17,AC12,AC9W9=FORMULATEXT(W10)W10:W15W10=WR("snake",cs)AC10AC10=WR("diagonal",cd)AC13AC13=WR("advance",cd)A18:T32A18=SEQUENCE(rw,20)W18W18=WR("difficult",cs)W21:W22W21=WR("winner",cs)W31:W36W31=PR(W10#)AC31AC31=PR(AC10)AC33,W38AC33=PR(AC13)W40:W41W40=PR(W21#)Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15AC13, AC10, W21, W18, W10


----------



## Xlambda (Nov 5, 2021)

*One of the approaches for solving the diagonal lookup was to "level" the diagonals in rows format. 
In case that extracting diagonals could be useful for other tasks also, I thought it deserves a look.*
Book1.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1Extracting diagonals horizontally.2There are 2 "inclinations" of diagonals, let's call them left and right.3Left one has first element top left corner of an array4Right one has first element, top right corner of an array5612332172344328345543945665410leftright11for an array r x c  nr. of diagonals d, left or right will be d=r+c-1, so for 4x3 d=4+3-1=6 diagonals12Concept left diagonal13To get diagonals by row let's lower each column14row indexes for an array 5x4with this column pattern:pattern row indexes for diagonal array 8x415=QUOTIENT(SEQUENCE(5,4)-1,4)+10123=SEQUENCE(8)-(SEQUENCE(,4)-1)1611111pattern formula:10-1-217222221=SEQUENCE(,4)-1210-118333332101233210194444432143212055555432543221543654322d=5+4-1=854765423587652425we have to exclude values<=0 26and vales >5  initial nr. of rowssample array 5x4Note: clms index unchanged27=(S16#>0)*(S16#<=5)*S16#=SEQUENCE(5,4)=IF(A28#,INDEX($I$28#,A28#,SEQUENCE(,4)),"")281000123412921005678523032109101112963314321131415161310743254321718192017141183305431815123400541916350005203637Concept right diagonal.38the only thing we have to change is the column pattern from 0,1,2,3 to 3,2,1,0pattern row indexes39=SORT(I15:L15,,-1,1)=SEQUENCE(8)-H40#403210-2-10141-101242=(S40#>0)*(S40#<=5)*S40#=IF(A43#,INDEX($I$28#,A43#,SEQUENCE(,4)),"")0123430001 41234440012382345450123271234564612341611164567472345510152056784834509141949450013185050001751Sheet2Cell FormulasRangeFormulaA15,H42,A42,S39,H39,O27,I27,A27,N17,S15A15=FORMULATEXT(A16)A16:D20A16=QUOTIENT(SEQUENCE(5,4)-1,4)+1S16:V23S16=SEQUENCE(8)-(SEQUENCE(,4)-1)N18:Q18N18=SEQUENCE(,4)-1A28:D35A28=(S16#>0)*(S16#<=5)*S16#I28:L32I28=SEQUENCE(5,4)O28:R35O28=IF(A28#,INDEX($I$28#,A28#,SEQUENCE(,4)),"")H40:K40H40=SORT(I15:L15,,-1,1)S40:V47S40=SEQUENCE(8)-H40#A43:D50A43=(S40#>0)*(S40#<=5)*S40#H43:K50H43=IF(A43#,INDEX($I$28#,A43#,SEQUENCE(,4)),"")Dynamic array formulas.


----------



## Xlambda (Nov 5, 2021)

The function *ADG* *A*rray *D*ia*G*onal *ADG(a,[lr])
a: array
[lr}: left/right argument; 0 or omitted "left" extraction, 1 or <>0 , "right" extraction*

```
=LAMBDA(a,[lr],LET(r,ROWS(a),c,COLUMNS(a),sc,SEQUENCE(,c),d,r+c-1,s,SEQUENCE(d),
    pl,sc-1,pr,SORT(pl,,-1,1),x,IF(lr,s-pr,s-pl),y,(x>=0)*(x<=r)*x,
    IF(y,INDEX(IF(a="","",a),y,sc),"")
    )
)
```
Book1.xlsxABCDEFGHIJKLMNOPQRST1Function ADG(a,[lr]) Array Diagonal2a: array3[lr}: left/right argument; 0 or omitted "left" extraction, 1 or <>0 , "right" extraction45lr,omittedlr,16a=ADG(A7#)=ADG(A7#,1)712341 48567852389910111296327121013141516131074161116111718192017141185101520121815129141913191613181420171516lr,1lr,117a=ADG(A18#)=ADG(A18#,1)a=ADG(I18#)=ADG(I18#,1)181111231 3192222220333312122lr,123a=ADG(A24:E31)=ADG(A24:E31,1)24acfj5a 525bei410bcj1026dh3915deff41527g2814oghijci9o281713ns12345ae314s29612mrv678910bh8nv3011lquy1112131415d213ry31kptxzklmnog7muz32pqrs112qx33tuv6lt34xy11p35zk3637Note: The function can hanlde blankslr,138a=ADG(A39:E46)=ADG(A39:E46,1)39acf5a 540bei410bc1041d3915deff41542g2814ogici9o431713ns12345ae314s44612mv678910b8nv4511lquy1112131415d213y46ktxzklmnog7muz47qs112qx48tuv6lt49xy1150zk51Sheet3Cell FormulasRangeFormulaG6,M38,G38,M23,G23,Q17,M17,C17,F17,L6G6=FORMULATEXT(G7)A7:D11A7=SEQUENCE(5,4)G7:J14G7=ADG(A7#)L7:O14L7=ADG(A7#,1)A18:A20A18=SEQUENCE(3)C18:C20C18=ADG(A18#)F18:F20F18=ADG(A18#,1)I18:K18I18=SEQUENCE(,3)M18:O20M18=ADG(I18#)Q18:S20Q18=ADG(I18#,1)G24:K35,G39:K50G24=ADG(A24:E31)M24:Q35,M39:Q50M24=ADG(A24:E31,1)Dynamic array formulas.


----------



## Xlambda (Nov 6, 2021)

Book1.xlsxABCDEFGHIJKLMNOPQRST1If we want the extraction array to be aligned to left, excluding blanks,other functions2or we want to extract only "full" diagonals, we already have functions  for that.AFUSBYROW3By "full" arrays I mean, diagonals with maximum nr. of elements.AHCLEAN4width of ADG=MIN(ROWS(a),COLUMNS(a))=MIN(8,5)=5=>max elem=width ADG56a=ADG(A7:E14)=ADG(A7:E14,1)7acfj5a 58bei410bcj109dh3915deff41510g2814oghijci9o111713ns12345ae314s12612mrv678910bh8nv1311lquy1112131415d213ry14kptxzklmnog7muz15pqrs112qx16tuv6lt17xy11p18zk1920=AFUSBYROW(G7#)=AFUSBYROW(M7#)21align diagonal array. to the lefta522bcj1023deff41524ghijci9o2512345ae314s26678910bh8nv271112131415d213ry28klmnog7muz29pqrs112qx30tuv6lt31xy11p32zk3334=AHCLEAN(G7#)=AHCLEAN(M7#)35extracting "full" diagonals12345ae314s36678910bh8nv371112131415d213ry38klmnog7muz3940AHCLEAN applied to the arrays before alignment, or after alignment, since is41designed to filter rows by their nr. of blanks "n", will return same arrays42when we filter only for "full" rows43n,omittedn,omitted44=AHCLEAN(G21#)=AHCLEAN(M21#)45with "n" argument 12345ae314s46of AHCLEAN we can 678910bh8nv47vary what diagonals to1112131415d213ry48extractklmnog7muz4950n,251=AHCLEAN(G21#,COLUMNS(G21#)-3)=AHCLEAN(M21#,2)52extracting diag. withdeff41553at least 3 elementsghijci9o5412345ae314s55max diag elements=678910bh8nv56=width ADG=clms(ADG)=51112131415d213ry57klmnog7muz58n argument in AHCLEANpqrs112qx59n=5-3=2tuv6lt60Sheet4Cell FormulasRangeFormulaG6,G51,M51,M44,G44,M34,G34,M20,G20,M6G6=FORMULATEXT(G7)G7:K18G7=ADG(A7:E14)M7:Q18M7=ADG(A7:E14,1)G21:K32,M21:Q32G21=AFUSBYROW(G7#)G35:K38,M35:Q38G35=AHCLEAN(G7#)G45:K48,M45:Q48G45=AHCLEAN(G21#)G52:K59G52=AHCLEAN(G21#,COLUMNS(G21#)-3)M52:Q59M52=AHCLEAN(M21#,2)Dynamic array formulas.


----------



## Xlambda (Jan 23, 2022)

Another amazing challenge with words, today's (23-Jan-22) MrExcel YT: WORDLE Helper In Excel - Solve Wordle Faster! - Episode 2462 (has link to download the workbook to get entire Word array:  WordleHelperFromMrExcel.xlsx )
Came out with a function:
*WORDLE(wa,[b ],[g],[gi],[y],[yi])*
*wa: words array range*
*[b ]: "black" letters string (ex: "audo")
[g]: "green" letters string (ex: "ice")
[gi]: "green" indexes, numbers, each digit (1-5)  len(gi)=len(g) (ex: 245)
[y]: "yellow" letters (ex: "ien")*
*[yi]: "yellow" indexes, numbers, each digit (1-5)  len(yi)=len( y) (ex: 441)*

```
=LAMBDA(wa,[b],[g],[gi],[y],[yi],
    LET(s,SEQUENCE(,5),w,MID(wa,s,1),
       k,IF(ISOMITTED(b),1,BYROW(IFERROR(SEARCH(w,b),0),LAMBDA(x,SUM(x)=0))),
       l,IF(ISOMITTED(g),1,REDUCE(1,SEQUENCE(LEN(g)),LAMBDA(v,i,v*BYROW(IFERROR(SEARCH(w,MID(g,i,1)),0)*s=--MID(gi,i,1),LAMBDA(x,SUM(--x)))))),
       m,IF(ISOMITTED(y),1,REDUCE(1,SEQUENCE(LEN(y)),LAMBDA(v,i,v*BYROW(IFERROR(SEARCH(w,MID(y,i,1))*s<>--MID(yi,i,1),0),LAMBDA(x,SUM(--x)))))),
      FILTER(wa,k*l*m)
    )
)
```
WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQR1Wordsingle cell2aahedAUDIO=WORDLE(A2:A8939,"audotrs","ice",245,"ien",441)3aaliiTIRESmince4aarghNIECEwincedifferent cells, successive steps5abacaWINCEyince=WORDLE(A2:A8939,"audo",,,"i",4)6abacibeige7abackbeigy=WORDLE(K6#,"trs","i",2,"e",4)8abaftbeingbible9abakabennibikie=WORDLE(M8#,,"ice",245,"n",1)10abampbibbsbilgemince11abasebiblebingewince12abashbicepcivieyince13abatebicesfiche14abayabiersfille15abbasbiffsgighe16abbesbiffygimme17abbeybiggyhinge18abbotbightkibbe19abeambiglyliege20abelebikerlieve21abetsbikesmiche22abhorbikiemille23abidebilbymince24abledbilesminke25ablerbilgeniche26ablesbilgyniece27abmhobilksnieve28abodebillsnixie29abohmbillypiece30aboilbinerpixie31abomabinesviewy32aboonbingewince33abortbinitwinze34aboutbintsyince35abovebirch36abrisbirksSheet3Cell FormulasRangeFormulaI2,O9,M7,K5I2=FORMULATEXT(I3)I3:I5I3=WORDLE(A2:A8939,"audotrs","ice",245,"ien",441)K6:K968K6=WORDLE(A2:A8939,"audo",,,"i",4)M8:M34M8=WORDLE(K6#,"trs","i",2,"e",4)O10:O12O10=WORDLE(M8#,,"ice",245,"n",1)Dynamic array formulas.


----------



## Xlambda (Jan 23, 2022)

Went to the web site: Wordle - A daily word game
*Super fun to play!!!!!* Took 20s to finish!!! Is this cheating?? For me, it was important to check if *WORDLE* *function works*. And it does!! ✌️?(Screen capture attached)
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNO1Word2aahedHINGE=WORDLE(A2:A8939,"hngealbk","ir",32,"ipc",214)3aaliiALIBIcrimp4aarghPRICKcrisp5abacaCRISPsuccessive entering of arguments (same cell I3)6abaciCRIMPfirst word hinge7aback1. =WORDLE(A2:A8939,"hnge",,,"i",2)8abaftchose from list alibi9abaka2. =WORDLE(A2:A8939,"hngealb","i",3,"i",2)10abampchose from list *****11abase3. =WORDLE(A2:A8939,"hngealbk","ir",32,"ipc",214)12abash2 results only13abatechose crisp14abayaonly one left, crimp, was the answer15abbas16abbes17abbey18abbot19abeam20abele21abets22abhor23abide24abled25abler26ables27abmho28abode29abohm30aboil31abomaSheet4Cell FormulasRangeFormulaI2I2=FORMULATEXT(I3)I3:I4I3=WORDLE(A2:A8939,"hngealbk","ir",32,"ipc",214)Dynamic array formulas.


----------



## Xlambda (Jan 25, 2022)

WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRS1WordSPOILER ALERT !!!!! This will reveal today's wordle.2aahed¡¡¡ Scroll down only if you want to see the result !!!3aaliiUsed different "tactics" for this one: If we find a letter on its position, do not choose next word4aarghfrom filtered list. The list will reveal only the words with that letter in that position.5abacaIt will be a waste of that space to choose a word from the list, so I chose a word with a different letter6abaciin that position, and the tactics paid off.7abackCalled WORDLE after introducing the first 2 words, and got a unique result. After 2 tries only.8abaftSo, if you ask me now, yes, I think using the function is cheating. At least was fun to find out.?✌️9abakaWe can play the game without the function and use it only to see how we could have performed better.10abamp11abase12abash13abate14abaya15abbas16abbes17abbey18abbot19abeam20abele21abets22abhor23abide24abled25abler26ables27abmho28abode29abohm30aboil31aboma32aboon33abort34about35above36abris37abuse38abuts39abuzz40abyes41abysm42abyss43acari44acerb45acetaAMOUR46achedBEANS=WORDLE(A2:A8939,"moben","r",5,"aaus",1345)47achesSUGARsugar48achooWORDLE 25Jan22Cell FormulasRangeFormulaI46I46=FORMULATEXT(I47)I47I47=WORDLE(A2:A8939,"moben","r",5,"aaus",1345)


----------



## Xlambda (Jan 25, 2022)

Tired of waiting another 24h for the next WORDLE game?
Let's build *our own WORDLE game board* in a few simple steps with Conditional Formatting.
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUV1Word12345function waiting for "arguments"2aahedspinnDV list=WORDLE(A2:A8939)3aaliioread1no wordaahed4aargh2no wordaalii5abaca3no wordaargh6abaci4no wordabaca7aback5no wordabaci8abaft6no wordaback9abakaabaft10abampSetupabaka11abaseFormatting D3:abampPlaying the game12abash-letters color white (invisible)abaseD3, "y", ENTER13abateFornula D3:abashD3, "n", ENTER (to block the selection for consistent results)14abaya =IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))abateChoose first word from DV in M315abbasabaya(latest excel update does search on dropdown list)16abbesFormatting F3:J8abbasFill out 1st word (CF will show the matches)17abbey-letters white color, bold, centered, inner grid lines white colorabbesFill out 2nd word and so on18abbotabbey19abeamConditional Formatting formulas F3:J8 (fill colors)abbotUse the WORDLE function or not 20abeleblack CFabeamfilling out the arguments, according with color codes21abets =ISERR(SEARCH(F3,$D$3))abele22abhoryellow CFabetsPlay again?23abide =LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))abhorDelete F3:J8, and do another "spin"24abledgreen CFabide25abler =AND(F3<>"",SEARCH(F3,$D$3)=F$1)abled26ablesabler27abmhoFormula K3, drag and drop K3:K8ables28abode =IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")abmho29abohmabode30aboilData Validation M3abohm31aboma =$A$2:$A$8939aboil32aboonaboma33abortaboonSETUP My WORDLECell FormulasRangeFormulaO2O2=FORMULATEXT(O3)D3D3=IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))O3:O8940O3=WORDLE(A2:A8939)K3:K8K3=IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueF3:J8Expression=AND(F3<>"",SEARCH(F3,$D$3)=F$1)textNOF3:J8Expression=LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))textNOF3:J8Expression=ISERR(SEARCH(F3,$D$3))textNOCells with Data ValidationCellAllowCriteriaM3List=$A$2:$A$8939


----------



## Xlambda (Jan 25, 2022)

Playing...
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRS1Word12345function waiting for "arguments"2aahedspinnDV list=WORDLE(A2:A8939,"mxergtfl","ina",234,"an",34)3aaliipinas1mixer mixerkinas4aargh2giant pinas5abaca3final vinas6abaci4pinas 7aback5no wordFun note: ?8abaft6no wordI presume that pinas means:9abakaPiNAS: Raspberry Pi Based Network Attached Storage10abampor11abasepineapple (piña in spanish)12abash13abate14abayaMy WORDLECell FormulasRangeFormulaO2O2=FORMULATEXT(O3)D3D3=IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))O3:O5O3=WORDLE(A2:A8939,"mxergtfl","ina",234,"an",34)K3:K8K3=IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueF3:J8Expression=AND(F3<>"",SEARCH(F3,$D$3)=F$1)textNOF3:J8Expression=LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))textNOF3:J8Expression=ISERR(SEARCH(F3,$D$3))textNOCells with Data ValidationCellAllowCriteriaM3List=$A$2:$A$8939


----------



## Xlambda (Jan 25, 2022)

A little bit about *tactics*.
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRST1Word12345function waiting for "arguments"2aahedspinnDV list=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)3aaliiaalii1abort abortaalii4aargh2chile 5abaca3fusil 6abaci4algid 7aback5aalii ←←!!! Something wrong with CF !!!8abaft6no word(CF SEARCH relative to G2 cell finds 1st occurrence of "a") 9abaka10abampTactics11abaseChoose as 1st word a word that has no duplicate letters12abash1abort13abateWhen we have a "green", exclude that letter on purpose14abaya(since we already know where "a" is will use its15abbasspace for other letter)16abbes1st argument:17abbey(…"borta") and chose from results "chile"18abbot2chile19abeamstill keep "a" out20abele2nd argument:21abets(..."bortache",,,"il",34) and chose from results "fusil"22abhor3fusil23abidenow introduce all arguments correctly24abled3rd argument:25abler(..."bortchfus","ai",14,"ill",345) => only 4 words possible26ables27abmho=WORDLE(A2:A8939,"bortchfus","ai",14,"ill",345)28abodeaalii29abohmalgidchose "algid"30aboilalgin31abomaalkie32aboon33abort4algid34about4th argument:35above(..."bortchfusgd","ai",14,"illl",3452)36abris37abuse=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)38abutsaalii39abuzzfinal result!!!40abyesMy WORDLECell FormulasRangeFormulaO2,D37,D27O2=FORMULATEXT(O3)D3D3=IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))O3O3=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)K3:K8K3=IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")D28:D31D28=WORDLE(A2:A8939,"bortchfus","ai",14,"ill",345)D38D38=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueF3:J8Expression=ISERR(SEARCH(F3,$D$3))textNOF3:J8Expression=AND(F3<>"",SEARCH(F3,$D$3)=F$1)textNOF3:J8Expression=LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))textNOCells with Data ValidationCellAllowCriteriaM3List=$A$2:$A$8939


----------



## Xlambda (Jan 25, 2022)

Modified CF formula for *"green CF"* *=F3=MID($D$3,F$1,1)* , should be top condition, "yello CF" second.
Setup page:
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUV1Word12345function waiting for "arguments"2aahedspinnDV list=WORDLE(A2:A8939)3aaliioread1no wordaahed4aargh2no wordaalii5abaca3no wordaargh6abaci4no wordabaca7aback5no wordabaci8abaft6no wordaback9abakaabaft10abampSetupabaka11abaseFormatting D3:abampPlaying the game12abash-letters color white (invisible)abaseD3, "y", ENTER13abateFornula D3:abashD3, "n", ENTER (to block the selection for consistent results)14abaya =IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))abateChoose first word from DV in M315abbasabaya(latest excel update does search on dropdown list)16abbesFormatting F3:J8abbasFill out 1st word (CF will show the matches)17abbey-letters white color, bold, centered, inner grid lines white colorabbesFill out 2nd word and so on18abbotabbey19abeamConditional Formatting formulas F3:J8 (fill colors)abbotUse the WORDLE function or not 20abeleblack CFabeamfilling out the arguments, according with color codes21abets =ISERR(SEARCH(F3,$D$3))abele22abhoryellow CFabetsPlay again?23abide =LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))abhorDelete F3:J8, and do another "spin"24abledgreen CFtop in the CF listabide25abler =F3=MID($D$3,F$1,1)abled26ablesabler27abmhoFormula K3, drag and drop K3:K8ables28abode =IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")abmho29abohmabode30aboilData Validation M3abohm31aboma =$A$2:$A$8939aboil32aboonabomaSETUP My WORDLECell FormulasRangeFormulaO2O2=FORMULATEXT(O3)D3D3=IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))O3:O8940O3=WORDLE(A2:A8939)K3:K8K3=IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueF3:J8Expression=F3=MID($D$3,F$1,1)textNOF3:J8Expression=LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))textNOF3:J8Expression=ISERR(SEARCH(F3,$D$3))textNOCells with Data ValidationCellAllowCriteriaM3List=$A$2:$A$8939


----------



## Xlambda (Oct 27, 2021)

*WR: * *W*ord maze *R*esults challenge. Please do see latest (26-Oct-21) YT video of MrExcel Solving Word Search With Excel (FMWC) - 2439
This is a compact set of functions that can solve all lookup scenarios.
Defined names: *ar*, sample array ( *A1:T15* )  ; *rw*, nr. rows of sample array (*15*) (width is fixed to 20) ; *cs*, constant array values for "snake" lookup *{-1,1, 20,-20} *; *cd*, constant array values for "diagonal" lookup *{-21,-19,19,21}*

*WL(l)=LAMBDA(l,LET(x,IF(ar=l,1),y,IF(ar="?",1),z,IF(x+y,1),v,IF(z,SEQUENCE(rw,20)),SMALL(v,SEQUENCE(COUNT(v)))))*
*WA(a,c)=LAMBDA(a,c,LET(x,a-c,y,IF(x>0,x),UNIQUE(SMALL(y,SEQUENCE(COUNT(y ))))))
WB(a,b)=LAMBDA(a,b,FILTER(a,IFNA(XMATCH(a,b,,2),0)))*
*WR(w,c,[r],[i ])=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y )),n-1))))*
Last one *WR is !! recursive !!* Define the names, then the functions and we are good to go. (Gaps in [i ] are to keep the text no to turn italics)
*Will come back with the "concept" explanation asap.*

```
=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,
     IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y)),n-1))
    )
)
```
WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Z1SGPE2THEDIZBANTSNSdefined names2SP?AKIWK1FHVZG2IEKAXar: A1:T15 (sample array)3?ITQE?LASIXQJNSX4TJKrw: 15 (width is always 20)4LENJFXA4TIKNSBX1DJCPcs: {-1,1,20,-20} (constant array for "snake" type lookup5GDL33DMVLTGK?CU3OQOOcd: {-21,-19,19,21} (constant array for "diagonal" type lookup6IAIUVGYLU?IEP3WGKBTP74APAU4CZVFFFAFIPAXTT"snake" lookup"diagonal" lookup8HN?4GCKS4IDRHNNAJDNS9EC3XCODBVQOYJERGSKAH=WR("snake",cs)=WR("diagonal",cd)10EX1Z?PNI3OWCCN2UREGJ38211EONMEVKABEPFLQNZ?EW31812KAN?AIBKLG24?N?ZBNQE20=WR("advance",cd)13SMO4YFBBRE?DNE1ADJM41606714CFX43TY1ZJNIWUZ3OWHM22415TYD2YZSUDVVF4OZEKA?S3001617=SEQUENCE(rw,20)=WR("difficult",cs)181234567891011121314151617181920151192122232425262728293031323334353637383940204142434445464748495051525354555657585960=WR("winner",cs)2161626364656667686970717273747576777879801152281828384858687888990919293949596979899100273231011021031041051061071081091101111121131141151161171181191202412112212312412512612712812913013113213313413513613713813914025141142143144145146147148149150151152153154155156157158159160Note: I left the results in "sequence numbers" representation on purpose,26161162163164165166167168169170171172173174175176177178179180to be much easier to visualize the results.27181182183184185186187188189190191192193194195196197198199200To get them to "range" representation we can use PR(a)  (print results)28201202203204205206207208209210211212213214215216217218219220PR(a)=LAMBDA(a,LET(x,QUOTIENT(a-1,20)+1,y,MOD(a-1,20)+1,ADDRESS(x,y,4)))292212222232242252262272282292302312322332342352362372382392403024124224324424524624724824925025125225325425525625725825926031261262263264265266267268269270271272273274275276277278279280C1B532281282283284285286287288289290291292293294295296297298299300R133T1G434Important Note: Covered here only snake and diagonal search, for being the more complicated ones.T835Anyhow, to cover the rest of them the only thing we have to do is defining new constant arraysD1236cs (constant snake) {-1,1,20,-20}T1537cd (constant diagonal) {-21,-19,19,21}38For example, for horizontal lookup we have to defineK839ch {-1,1} and for vertical cv: {-20,20}40O641M144243WM snake diagonalCell FormulasRangeFormulaW9,W20,W17,A17,AC12,AC9W9=FORMULATEXT(W10)W10:W15W10=WR("snake",cs)AC10AC10=WR("diagonal",cd)AC13AC13=WR("advance",cd)A18:T32A18=SEQUENCE(rw,20)W18W18=WR("difficult",cs)W21:W22W21=WR("winner",cs)W31:W36W31=PR(W10#)AC31AC31=PR(AC10)AC33,W38AC33=PR(AC13)W40:W41W40=PR(W21#)Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15AC13, AC10, W21, W18, W10


----------



## Xlambda (Jan 25, 2022)

With the new CF formula, everything is ok!
To double check, I've also made D3 "visible". (black "ink")
Attached, screen capture.
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRST1Word12345function waiting for "arguments"2aahedspinnDV list=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)3aaliiaalii1abort abortaalii4aargh2chile 5abaca3fusil 6abaci4algid 7aback5aalii 8abaft6no word9abaka10abampTactics11abaseChoose as 1st word a word that has no duplicate letters12abash1abort13abateWhen we have a "green", exclude that letter on purpose14abaya(since we already know where "a" is will use its15abbasspace for other letter)16abbes1st argument:17abbey(…"borta") and chose from results "chile"18abbot2chile19abeamstill keep "a" out20abele2nd argument:21abets(..."bortache",,,"il",34) and chose from results "fusil"22abhor3fusil23abidenow introduce all arguments correctly24abled3rd argument:25abler(..."bortchfus","ai",14,"ill",345) => only 4 words possible26ables27abmho=WORDLE(A2:A8939,"bortchfus","ai",14,"ill",345)28abodeaalii29abohmalgidchose "algid"30aboilalgin31abomaalkie32aboon33abort4algid34about4th argument:35above(..."bortchfusgd","ai",14,"illl",3452)36abris37abuse=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)38abutsaalii39abuzzfinal result!!!40abyesMy WORDLECell FormulasRangeFormulaO2,D37,D27O2=FORMULATEXT(O3)D3D3=IF(D2="n",D3,INDEX(A2:A8939,RANDBETWEEN(1,8938)))O3O3=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)K3:K8K3=IF(ISNA(XMATCH(TEXTJOIN("",,F3:J3),$A$2:$A$8939)),"no word","")D28:D31D28=WORDLE(A2:A8939,"bortchfus","ai",14,"ill",345)D38D38=WORDLE(A2:A8939,"bortchfusgd","ai",14,"illl",3452)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueF3:J8Expression=F3=MID($D$3,F$1,1)textNOF3:J8Expression=LET(x,SEARCH(F3,$D$3),AND(F3<>"",ISNUMBER(x),x<>F$1))textNOF3:J8Expression=ISERR(SEARCH(F3,$D$3))textNOCells with Data ValidationCellAllowCriteriaM3List=$A$2:$A$8939


----------



## Xlambda (Jan 25, 2022)

This will be the last "disclosure" of a real WORDLE game.
Chose to share this one because I have used MrExcel tactics regarding the best word to start with.
Wordle Best Starting Words is AROSE not ADIEU - Episode 2463
On the other hand, based on same brilliant ideas, there is a tone of good material for designing formulas with the new helper functions. 
Screen capture with statistic only, can be opened, does not show letters matrix.
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLM1WordSPOILER ALERT !!!!! This will reveal today's wordle.2aahed¡¡¡ Scroll down only if you want to see the result !!!3aaliiUsed MrExcel tactics for 1st word, that is able to eliminate4aarghlot of words, or, match more letters.5abaca6abaci7aback8abaft9abaka10abamp11abase12abash13abate14abaya15abbas16abbes17abbey18abbot19abeam20abele21abets22abhor23abide24abled25abler26ables27abmho28abode29abohm30aboil31aboma32aboon33abort34about35above36abris37abuse38abuts39abuzz40abyes41abysm42abyss43acari44acerb=WORDLE(A2:A8939,"rosebitmp","ha",23,"aahc",1251)45acetaAROSEwhack46achedBAITH47achesCHAMP48achooWHACK49acidsWORDLE 26Jan22Cell FormulasRangeFormulaI44I44=FORMULATEXT(I45)I45I45=WORDLE(A2:A8939,"rosebitmp","ha",23,"aahc",1251)


----------



## Xlambda (Jan 26, 2022)

To play indefinitely, no one/day restriction, no spoiler alert needed.
Wordle Game - Play Online
Wordle Game - Play WORDLE Unlimited


----------



## Xlambda (Jan 26, 2022)

Checked *both sites*. Here are some examples.
Screen captures inserted. (proof that games were genuine).
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUVW1Word2aahedex.1 wordlegame.org=WORDLE(A2:A8939,"areinmkh","oscu",2534,"osc",341)3aaliiAROSEfocus4aarghCOINSlocus5abacaMOCKS6abaciHOCKS7abackFOCUS8abaft9abakaex.2  wordlegame.orgex.3 wordlegame.orgex.4 wordle-play.com10abampEXCELEXCELEXCEL11abaseADIOSADOBEALIEN12abashBURSTBADGE13abateSKILL14abaya15abbasfor fun, started with "excel""excel" again"excel again"16abbes1st argument kept the green "l" out=WORDLE(A2:A8939,"xclo","e",5,"adb",124)(..."xc","e",4,"el",15) , chose "alien"17abbey(..."excl") chose from results "adios"badgesuper lucky guess18abbot2nd argument, kept the green "i" out alsobarde19abeam(..."excladoi",,,"s",5) chose "burst"20abele3rd argument , followed correct patternsNotes: 21abets(..."excadoburt","li",53,"ss",54)There is a different CF approach between the 2 sites22abhorFor ex.4 "e" is both yellow and green but there is only one "e"23abide=WORDLE(A2:A8939,"excadoburt","li",53,"ss",54)For ex.3 "e" is both yellow and black=> is only one "e" for sure24abledshillIf ex.4 would have happened with ex.3's CF, would have meant25ablerskillchose "skill" , lucky guessthat are 2 "e"'s 26ablesspill27abmhoswillEven if we start with a word with dups, could be helpful28abode29abohmHumble Conclusion:30aboilWhatever rules we follow, solving wordle games with the31abomahelp of WORDLE function is, in my opinion, fun, but too easy.32aboonDomestic use, for fun, is ok, engaged in a competition, wouldn’t be fair.33abortwordle orgCell FormulasRangeFormulaI2,C23I2=FORMULATEXT(I3)I3:I4I3=WORDLE(A2:A8939,"areinmkh","oscu",2534,"osc",341)K16K16=FORMULATEXT(L17)L17:L18L17=WORDLE(A2:A8939,"xclo","e",5,"adb",124)C24:C27C24=WORDLE(A2:A8939,"excadoburt","li",53,"ss",54)Dynamic array formulas.


----------



## Xlambda (Jan 28, 2022)

Some *lambda formulas "workout"*.
*Letters distribution*, 2 methods. Duplicate letters counted as one.
*1st method*. No UNIQUE, no MID functions.
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQR1WordLetters distribution all words (no dups) 1st method, no UNIQUE, no MID needed2aahedDuplicate letters are counted as one3aaliisingle cell formula P144aargh=CHAR(SEQUENCE(26,,97)) =LET(a,A2:A8939,l,CHAR(SEQUENCE(26,,97)),s,SEQUENCE(8938),
    r,REDUCE(0,s,LAMBDA(v,i,
      v+ISNUMBER(SEARCH(l,INDEX(a,i))))),SORT(IF({1,0},l,r),2,-1))5abaca↓only for 1st word in list6abaci↓SEARCH delivers for "a" first position only7aback↓=--(ISNUMBER(SEARCH(C8#,A2)))8abafta19abakab0summing all search arrays for each word in list10abampc08938 iterations for all words in list with REDUCE11abased1=REDUCE(0,SEQUENCE(8938),LAMBDA(v,i,v+ISNUMBER(SEARCH(C8#,INDEX(A2:A8939,i)))))12abashe1362013abatef01023joiningsorting descending ordersingle cell14abayag01412=IF({1,0},C8#,G12#)=SORT(I15#,2,-1)P1415abbash11617a3620s4124s412416abbesi03993b1023e3993e399317abbeyj0708c1412a3620a362018abbotk01052d1617r2753r275319abeaml01188e3993o2632o263220abelem02516f708i2516i251621abetsn0184g1052l2231l223122abhoro0924h1188t2139t213923abidep02231i2516n1922n192224abledq01270j184u1657u165725ablerr01922k924d1617d161726abless02632l2231c1412c141227abmhot01310m1270y1372y137228abodeu079n1922p1310p131029abohmv02753o2632m1270m127030aboilw04124p1310h1188h118831abomax02139q79g1052g105232aboony01657r2753b1023b102333abortz0465s4124k924k92434about689t2139f708f70835above209u1657w689w68936abris1372v465v465v46537abuse227w689z227z22738abutsx209x209x20939abuzzy1372j184j18440abyesz227q79q7941abysmletters distribution 1Cell FormulasRangeFormulaC4C4=FORMULATEXT(C8)E7,I14,L14,G11E7=FORMULATEXT(E8)C8:C33C8=CHAR(SEQUENCE(26,,97))E8:E33E8=--(ISNUMBER(SEARCH(C8#,A2)))G12:G37G12=REDUCE(0,SEQUENCE(8938),LAMBDA(v,i,v+ISNUMBER(SEARCH(C8#,INDEX(A2:A8939,i)))))I15:J40I15=IF({1,0},C8#,G12#)L15:M40L15=SORT(I15#,2,-1)P15:Q40P15=LET(a,A2:A8939,l,CHAR(SEQUENCE(26,,97)),s,SEQUENCE(8938),r,REDUCE(0,s,LAMBDA(v,i,v+ISNUMBER(SEARCH(l,INDEX(a,i))))),SORT(IF({1,0},l,r),2,-1))Dynamic array formulas.


----------



## Xlambda (Jan 28, 2022)

*Letters distribution.
2nd method*. using UNIQUE and MID, single cell formula
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUVW1WordLetters distribution all words (no dups) 2nd method, UNIQUE and MIDformula O92aahedDuplicate letters are counted as one =LET(a,A2:A8939,l,CHAR(SEQUENCE(26)+96),s,SEQUENCE(,5),
      u,MID(BYROW(MID(a,s,1),LAMBDA(x,CONCAT(UNIQUE(x,1)))),s,1),
      d,BYROW(l,LAMBDA(x,SUM(--(x=u)))),
      SORT(IF({1,0},l,d),2,-1))3aaliiunique letters, each word4aargh=BYROW(MID(A2:A8939,SEQUENCE(,5),1),LAMBDA(x,CONCAT(UNIQUE(x,1))))5abacaahed=CHAR(SEQUENCE(26)+96)6abaciali=MID(C5#,SEQUENCE(,5),1)=BYROW(K7#,LAMBDA(x,SUM(--(x=E7#))))7abackarghaheda3620single cell8abaftabcalib1023O99abakaabciarghc1412s412410abampabckabcd1617e399311abaseabftabcie3993a362012abashabkabckf708r275313abateabmpabftg1052o263214abayaabseabkh1188i251615abbasabshabmpi2516l223116abbesabteabsej184t213917abbeyabyabshk924n192218abbotabsabtel2231u165719abeamabesabym1270d161720abeleabeyabsn1922c141221abetsabotabeso2632y137222abhorabemabeyp1310p131023abideabelabotq79m127024abledabetsabemr2753h118825ablerabhorabels4124g105226ablesabideabetst2139b102327abmhoabledabhoru1657k92428abodeablerabidev465f70829abohmablesabledw689w68930aboilabmhoablerx209v46531abomaabodeablesy1372z22732aboonabohmabmhoz227x20933abortaboilabodej18434aboutabomabohmq7935aboveabonaboil36abrisabortabomletters distribution 2Cell FormulasRangeFormulaC4,M6,E6C4=FORMULATEXT(C5)C5:C8942C5=BYROW(MID(A2:A8939,SEQUENCE(,5),1),LAMBDA(x,CONCAT(UNIQUE(x,1))))K5K5=FORMULATEXT(K7)E7:I8944E7=MID(C5#,SEQUENCE(,5),1)K7:K32K7=CHAR(SEQUENCE(26)+96)M7:M32M7=BYROW(K7#,LAMBDA(x,SUM(--(x=E7#))))O9:P34O9=LET(a,A2:A8939,l,CHAR(SEQUENCE(26)+96),s,SEQUENCE(,5),u,MID(BYROW(MID(a,s,1),LAMBDA(x,CONCAT(UNIQUE(x,1)))),s,1),d,BYROW(l,LAMBDA(x,SUM(--(x=u)))),SORT(IF({1,0},l,d),2,-1))Dynamic array formulas.


----------



## Xlambda (Jan 28, 2022)

Challenging task. *Extract all existing anagrams of Words list, in descending order, 2D array.*
*Part 1.*
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRS1Worddefined name, word list A2:A8939=wlNote: index nr. for words2aahedD6:=BYROW(MID(wl,SEQUENCE(,5),1),LAMBDA(x,CONCAT(SORT(x,,,1))))step3.with no anagrams in list3aaliistep1. sort letters of each word in ascending orderstep 2. unique values, exactly onceidentifying index nr. =NOT(O6#)*SEQUENCE(8938)4aarghall anagrams will be duplicatesall the rest will be anagramswords that have anagrams5abaca(expand,sort,join, for each row)=UNIQUE(D6#,,1)=XMATCH(D6#,I6#)=ISNA(M6#)=O6#*SEQUENCE(8938)6abaciaadehbaaiilu#N/ATRUEx17abackaaiilNote:aaghrnr. words1FALSE08abaftaaghrb,u,xaaabcappear only once2FALSE09abakaaaabcvariabele namesaabci=ROWS(I6#)3FALSE010abampaabciin final formulaaabck49284FALSE011abaseaabckaabftout of5FALSE012abashaabftaaabk89386FALSE013abateaaabkaabmptotal words7FALSE014abayaaabmpaabes8FALSE015abbasaabesaabhs9FALSE016abbesaabhsaabet10FALSE017abbeyaabetaaaby11FALSE018abbotaaabyabbey12FALSE019abeamaabbsabbot#N/ATRUE1420abeleabbesabeel#N/ATRUE1521abetsabbeyabhor13FALSE022abhorabbotabdei14FALSE023abideaabemabilo#N/ATRUE1824abledabeelaabmo15FALSE025ablerabestabnoo#N/ATRUE2026ablesabhorabotu16FALSE027abmhoabdeiabeov17FALSE028abodeabdelabuzz#N/ATRUE2329abohmabelrabesy#N/ATRUE2430aboilabelsabmsy#N/ATRUE2531abomaabhmoaacir#N/ATRUE2632aboonabdeoaacet#N/ATRUE2733abortabhmoacdeh#N/ATRUE2834aboutabiloachoo18FALSE035aboveaabmoacdiy19FALSE036abrisabnooacgin20FALSE037abuseabortaciin#N/ATRUE3238abutsabotuaceek21FALSE039abuzzabeovaccim22FALSE040abyesabirsaccko#N/ATRUE3541abysmabesuacdlo#N/ATRUE3642abyssabstuacetu#N/ATRUE3743acariabuzzaadeg23FALSE044acerbabesyaadpt24FALSE045acetaabmsyaaddx25FALSE046achedabssyaddde#N/ATRUE4147achesaaciradeiu26FALSE048achooabceradios#N/ATRUE4349acidsaacetadimt27FALSE050acidyacdehadimx28FALSE051acingacehsabdoo#N/ATRUE46Extracting anagrams 1Cell FormulasRangeFormulaD2D2=FORMULATEXT(D6)I5,K9,M5,O5,Q5I5=FORMULATEXT(I6)D6:D8943D6=BYROW(MID(wl,SEQUENCE(,5),1),LAMBDA(x,CONCAT(SORT(x,,,1))))I6:I4933I6=UNIQUE(D6#,,1)M6:M8943M6=XMATCH(D6#,I6#)O6:O8943O6=ISNA(M6#)Q6:Q8943Q6=O6#*SEQUENCE(8938)K10K10=ROWS(I6#)K12K12=ROWS(D6#)Dynamic array formulas.Named RangesNameRefers ToCellswl='Extracting anagrams 1'!$A$2:$A$8939D6


----------



## Xlambda (Jan 28, 2022)

WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB1WordPart2.extracting horizontally, rows index numbers of matching anagrams2aahedfor a single row (first row). we have to write a formula that can spill horizontally3aaliiaadehbaaiiluTRUEx=TRANSPOSE(FILTER(SEQUENCE(ROWS(wl)),D3=D3#))4aarghaaiilaaghrFALSE1141 =>words of row 1 and row 141 are anagrams5abacaaaghraaabcFALSEstep4. 6abaciaaabcaabciFALSEwe need to do same calculations for all 8938 rows.7abackaabciaabckFALSEsince BYROW can not spill horizontally, will use REDUCE with an accumulator "v" that8abaftaabckaabftFALSEbuilds the array row by row, appending the array "v" of (1,i-1) rows to current i row 9abakaaabftaaabkFALSE10abampaaabkaabmpFALSE=REDUCE(0,SEQUENCE(8938),LAMBDA(v,i,IF(SEQUENCE(i)=i,LET(x,IF(INDEX(D3#,i)=D3#,SEQUENCE(8938),""),TRANSPOSE(FILTER(x,x<>""))),v)))11abaseaabmpaabesFALSEd1141#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A12abashaabesaabhsFALSE22#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Arows of this color, that have same value13abateaabhsaabetFALSE33#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Ahorizontally, are like this because of the "appending" iteration14abayaaabetaaabyFALSE44#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aprocess, and corespond to the rows that have no anagrams.15abbasaaabyabbeyFALSE55#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/AAll these rows will be filtered out anyhow.16abbesaabbsabbotTRUE66#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A17abbeyabbesabeelTRUE77#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A18abbotabbeyabhorFALSE88#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A19abeamabbotabdeiFALSE99#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A20abeleaabemabiloTRUE1010#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A21abetsabeelaabmoFALSE1111#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A22abhorabestabnooTRUE1212#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A23abideabhorabotuFALSE1313#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A24abledabdeiabeovFALSE14516#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A25ablerabdelabuzzTRUE15518#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A26ablesabelrabesyTRUE1616#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A27abmhoabelsabmsyTRUE1717#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A28abodeabhmoaacirTRUE18244#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A29abohmabdeoaacetTRUE1919#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A30aboilabhmoacdehTRUE206006046336346987612#N/A#N/A#N/A#N/A#N/A31abomaabiloachooFALSE21212121212121#N/A#N/A#N/A#N/A#N/A32aboonaabmoacdiyFALSE22222222222222#N/A#N/A#N/A#N/A#N/A33abortabnooacginFALSE23546785#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A34aboutabortaciinTRUE24547793802#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A35aboveabotuaceekFALSE255487946532#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A36abrisabeovaccimFALSE2628#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A37abuseabirsacckoTRUE2783#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A38abutsabesuacdloTRUE2628#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A39abuzzabstuacetuTRUE29292929292929#N/A#N/A#N/A#N/A#N/A40abyesabuzzaadegFALSE30303030303030#N/A#N/A#N/A#N/A#N/A41abysmabesyaadptFALSE31313131313131#N/A#N/A#N/A#N/A#N/A42abyssabmsyaaddxFALSE328517617#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A43acariabssyadddeTRUE33333333333333#N/A#N/A#N/A#N/A#N/A44acerbaaciradeiuFALSE34343434343434#N/A#N/A#N/A#N/A#N/A45acetaabceradiosTRUE356531#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A46achedaacetadimtFALSE36635#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A47achesacdehadimxFALSE37761980918094#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A48achooacehsabdooTRUE38383838383838#N/A#N/A#N/A#N/A#N/A49acidsachooadoptFALSE39393939393939#N/A#N/A#N/A#N/A#N/A50acidyacdisadnowTRUE40404040404040#N/A#N/A#N/A#N/A#N/A51acingacdiyadeozFALSE41599#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A52aciniacginadltuFALSE42424242424242#N/A#N/A#N/A#N/A#N/A53ackeeaciinacdnuFALSE439581137#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/AExtracting anagrams 2Cell FormulasRangeFormulaD3:D8940D3=BYROW(MID(wl,SEQUENCE(,5),1),LAMBDA(x,CONCAT(SORT(x,,,1))))F3:F4930F3=UNIQUE(D3#,,1)H3:H8940H3=ISNA(XMATCH(D3#,F3#))J3,J10J3=FORMULATEXT(J4)J4:K4J4=TRANSPOSE(FILTER(SEQUENCE(ROWS(wl)),D3=D3#))J11:U8948J11=REDUCE(0,SEQUENCE(8938),LAMBDA(v,i,IF(SEQUENCE(i)=i,LET(x,IF(INDEX(D3#,i)=D3#,SEQUENCE(8938),""),TRANSPOSE(FILTER(x,x<>""))),v)))Dynamic array formulas.Named RangesNameRefers ToCells'Extracting anagrams 2'!wl='Extracting anagrams 2'!$A$2:$A$8939J4, D3


----------



## Xlambda (Jan 28, 2022)

WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1WordPart3.step5. extracting the anagram words: index(wl,unique(filter(d,x)))2aahed3aalii=IFERROR(INDEX(wl,UNIQUE(FILTER(J4#,H4#))),"")4aarghaadehbaaiiluTRUEx1141#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Adaahedaheade5abacaaaiilaaghrFALSE22#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabbasbabas6abaciaaghraaabcFALSE33#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabbesbabes7abackaaabcaabciFALSE44#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabeamameba8abaftaabciaabckFALSE55#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabetsbastebatesbeastbeatsbetastabes9abakaaabckaabftFALSE66#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabledbaledblade10abampaabftaaabkFALSE77#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aablerbalerblareblear11abaseaaabkaabmpFALSE88#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aablesbalesblasesable12abashaabmpaabesFALSE99#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabmhoabohm13abateaabesaabhsFALSE1010#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabodeadobe14abayaaabhsaabetFALSE1111#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabortboarttabor15abbasaabetaaabyFALSE1212#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabrissabir16abbesaaabyabbeyFALSE1313#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabusebeaus17abbeyaabbsabbotTRUE14516#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabutstabustsubatubas18abbotabbesabeelTRUE15518#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aabyssbassy19abeamabbeyabhorFALSE1616#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacerbbracecaber20abeleabbotabdeiFALSE1717#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacheschase21abetsaabemabiloTRUE18244#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacidsasdiccadiscaids22abhorabeelaabmoFALSE1919#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacmescamesmaces23abideabestabnooTRUE206006046336346987612#N/A#N/A#N/A#N/A#N/Aacnedcaneddance24abledabhorabotuFALSE21212121212121#N/A#N/A#N/A#N/A#N/Aacnescanesscena25ablerabdeiabeovFALSE22222222222222#N/A#N/A#N/A#N/A#N/Aacornnarcoracon26ablesabdelabuzzTRUE23546785#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacredarcedcadrecaredcedarraced27abmhoabelrabesyTRUE24547793802#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacrescarescarseescarracesscareserac28abodeabelsabmsyTRUE255487946532#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aacridcairddaric29abohmabhmoaacirTRUE2628#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aactedcadet30aboilabdeoaacetTRUE2783#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aactinantic31abomaabhmoacdehTRUE2628#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aactortaroc32aboonabiloachooFALSE29292929292929#N/A#N/A#N/A#N/A#N/Aacylsclaysscaly33abortaabmoacdiyFALSE30303030303030#N/A#N/A#N/A#N/A#N/Aadderdareddreadreadd34aboutabnooacginFALSE31313131313131#N/A#N/A#N/A#N/A#N/Aaddlededalladed35aboveabortaciinTRUE328517617#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aadeemedema36abrisabotuaceekFALSE33333333333333#N/A#N/A#N/A#N/A#N/Aadeptpatedtaped37abuseabeovaccimFALSE34343434343434#N/A#N/A#N/A#N/A#N/Aaditsditasstaidtsadi38abutsabirsacckoTRUE356531#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aadmandaman39abuzzabesuacdloTRUE36635#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aadmenamendmanedmenadnamed40abyesabstuacetuTRUE37761980918094#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aadoreoaredoread41abysmabuzzaadegFALSE38383838383838#N/A#N/A#N/A#N/A#N/Aadornandroradon42abyssabesyaadptFALSE39393939393939#N/A#N/A#N/A#N/A#N/Aadustdauts43acariabmsyaaddxFALSE40404040404040#N/A#N/A#N/A#N/A#N/Aadzeddazed44acerbabssyadddeTRUE41599#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aadzesdazes45acetaaaciradeiuFALSE42424242424242#N/A#N/A#N/A#N/A#N/Aaedeseased46achedabceradiosTRUE439581137#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aafireferia47achesaacetadimtFALSE44444444444444#N/A#N/A#N/A#N/A#N/Aagarsragas48achooacdehadimxFALSE45454545454545#N/A#N/A#N/A#N/A#N/Aagersgearsragessagersarge49acidsacehsabdooTRUE461315#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/Aaggereggargager50acidyachooadoptFALSE47474747474747#N/A#N/A#N/A#N/A#N/AagismsigmaExtracting anagrams 3Cell FormulasRangeFormulaW3W3=FORMULATEXT(W4)D4:D8941D4=BYROW(MID(wl,SEQUENCE(,5),1),LAMBDA(x,CONCAT(SORT(x,,,1))))F4:F4931F4=UNIQUE(D4#,,1)H4:H8941H4=ISNA(XMATCH(D4#,F4#))J4:U8941J4=REDUCE(0,SEQUENCE(8938),LAMBDA(v,i,IF(SEQUENCE(i)=i,LET(x,IF(INDEX(D4#,i)=D4#,SEQUENCE(8938),""),TRANSPOSE(FILTER(x,x<>""))),v)))W4:AH1570W4=IFERROR(INDEX(wl,UNIQUE(FILTER(J4#,H4#))),"")Dynamic array formulas.Named RangesNameRefers ToCells'Extracting anagrams 3'!wl='Extracting anagrams 3'!$A$2:$A$8939W4, D4


----------



## Xlambda (Jan 28, 2022)

WORDLE function WordleHelperFromMrExcel.xlsxVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX1Part4.step6. array "d" in descending order of nr.anagrams/row2step5. extracting the anagram words: index(wl,unique(filter(d,x)))=BYROW(W4#,LAMBDA(x,COUNTA(FILTER(x,x<>""))))3=IFERROR(INDEX(wl,UNIQUE(FILTER(J4#,H4#))),"")↓=INDEX(W4#,SORTBY(SEQUENCE(ROWS(W4#)),AJ4#,-1),SEQUENCE(,COLUMNS(W4#)))4daahedaheade2apersapresasperparesparsepearsprasepresarapesreapssparespearfinal5abbasbabas2leastsetalslatestalestealstelataelstalestealsteslaresult6abbesbabes2arlesearlslareslaserlearsralesrealsseral7abeamameba2lapseleapspalespealspleassalepsepalspale8abetsbastebatesbeastbeatsbetastabes7perispierspriespriseripesspeirspierspire9abledbaledblade3abetsbastebatesbeastbeatsbetastabes10ablerbalerblareblear4acrescarescarseescarracesscareserac11ablesbalesblasesable4amensmanesmansemeansmensanamesnemas12abmhoabohm2arilslairslarisliarslirasrailsrials13abodeadobe2aspennapesneapspanespeanssneapspean14abortboarttabor3caretcartecatercratereactrectatrace15abrissabir2deistdietsditeseditssitedstiedtides16abusebeaus2diolsidolslidosloidssloidsoldisolid17abutstabustsubatubas4doersdoserredosresodrodesrosedsored18abyssbassy2emitsitemsmetismitessmitestimetimes19acerbbracecaber3esterreestresetsteersteretersetrees20acheschase2lavessalveselvaslavevalesvalseveals21acidsasdiccadiscaids4leaptleptapaletpetalplatepleattepal22acmescamesmaces3pastepatespeatsseptaspatetapestepas23acnedcaneddance3peersperespersepreespresespeerspree24acnescanesscena3resawsawersewarswareswearwareswears25acornnarcoracon3acredarcedcadrecaredcedarraced26acredarcedcadrecaredcedarraced6airtsastirsitarstairstriatarsi27acrescarescarseescarracesscareserac7albasbaalsbalasbalsabasalsabal28acridcairddaric3alertalterartellaterrateltaler29actedcadet2ardebbardebaredbeardbreaddebar30actinantic2asheddeashhadesheadssadheshade31actortaroc2asterratesresatstaretarestears32acylsclaysscaly3baresbaserbearsbraessabersabre33adderdareddreadreadd4cruetcuretcutereructrecuttruce34addlededalladed3daterderatratedtaredtradetread35adeemedema2deersdreesredesreedssedersered36adeptpatedtaped3deilsdelisidlesisledsidleslide37aditsditasstaidtsadi4gatergrategreatretagtargeterga38admandaman2haleshealsleashselahshalesheal39admenamendmanedmenadnamed5heroshoershorsehosershoershore40adoreoaredoread3insetneistnitessentisteintines41adornandroradon3leetssleetsteelsteleteelsteles42adustdauts2manosmasonmoansmonasnomassoman43adzeddazed2matesmeatssatemsteamtamesteams44adzesdazes2notesonsetsetonstenostonetones45aedeseased2partspratsspratstraptarpstraps46afireferia2pilespliesslipespeilspielspile47agarsragas2poresposerprosereposropesspore48agersgearsragessagersarge5ratosroastrotassortatarostoras49aggereggargager3reinsresinrinserisenserinsiren50agismsigma2riotsrotistirostorsitriostroisExtracting anagrams 3Cell FormulasRangeFormulaAJ2AJ2=FORMULATEXT(AJ4)W3,AL3W3=FORMULATEXT(W4)W4:AH1570W4=IFERROR(INDEX(wl,UNIQUE(FILTER(J4#,H4#))),"")AJ4:AJ1570AJ4=BYROW(W4#,LAMBDA(x,COUNTA(FILTER(x,x<>""))))AL4:AW1570AL4=INDEX(W4#,SORTBY(SEQUENCE(ROWS(W4#)),AJ4#,-1),SEQUENCE(,COLUMNS(W4#)))Dynamic array formulas.Named RangesNameRefers ToCells'Extracting anagrams 3'!wl='Extracting anagrams 3'!$A$2:$A$8939W4


----------



## Xlambda (Oct 27, 2021)

*WR: * *W*ord maze *R*esults challenge. Please do see latest (26-Oct-21) YT video of MrExcel Solving Word Search With Excel (FMWC) - 2439
This is a compact set of functions that can solve all lookup scenarios.
Defined names: *ar*, sample array ( *A1:T15* )  ; *rw*, nr. rows of sample array (*15*) (width is fixed to 20) ; *cs*, constant array values for "snake" lookup *{-1,1, 20,-20} *; *cd*, constant array values for "diagonal" lookup *{-21,-19,19,21}*

*WL(l)=LAMBDA(l,LET(x,IF(ar=l,1),y,IF(ar="?",1),z,IF(x+y,1),v,IF(z,SEQUENCE(rw,20)),SMALL(v,SEQUENCE(COUNT(v)))))*
*WA(a,c)=LAMBDA(a,c,LET(x,a-c,y,IF(x>0,x),UNIQUE(SMALL(y,SEQUENCE(COUNT(y ))))))
WB(a,b)=LAMBDA(a,b,FILTER(a,IFNA(XMATCH(a,b,,2),0)))*
*WR(w,c,[r],[i ])=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y )),n-1))))*
Last one *WR is !! recursive !!* Define the names, then the functions and we are good to go. (Gaps in [i ] are to keep the text no to turn italics)
*Will come back with the "concept" explanation asap.*

```
=LAMBDA(w,c,[r],[i ],LET(n,IF(i,i,LEN(w)),x,MID(w,n,1),y,MID(w,n-1,1),z,
     IF(AND(r=0),WA(WL(x),c),WA(r,c)),IF(n=1,r,WR(w,c,WB(z,WL(y)),n-1))
    )
)
```
WORD MAZE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK1Z1SGPE2THEDIZBANTSNSdefined names2SP?AKIWK1FHVZG2IEKAXar: A1:T15 (sample array)3?ITQE?LASIXQJNSX4TJKrw: 15 (width is always 20)4LENJFXA4TIKNSBX1DJCPcs: {-1,1,20,-20} (constant array for "snake" type lookup5GDL33DMVLTGK?CU3OQOOcd: {-21,-19,19,21} (constant array for "diagonal" type lookup6IAIUVGYLU?IEP3WGKBTP74APAU4CZVFFFAFIPAXTT"snake" lookup"diagonal" lookup8HN?4GCKS4IDRHNNAJDNS9EC3XCODBVQOYJERGSKAH=WR("snake",cs)=WR("diagonal",cd)10EX1Z?PNI3OWCCN2UREGJ38211EONMEVKABEPFLQNZ?EW31812KAN?AIBKLG24?N?ZBNQE20=WR("advance",cd)13SMO4YFBBRE?DNE1ADJM41606714CFX43TY1ZJNIWUZ3OWHM22415TYD2YZSUDVVF4OZEKA?S3001617=SEQUENCE(rw,20)=WR("difficult",cs)181234567891011121314151617181920151192122232425262728293031323334353637383940204142434445464748495051525354555657585960=WR("winner",cs)2161626364656667686970717273747576777879801152281828384858687888990919293949596979899100273231011021031041051061071081091101111121131141151161171181191202412112212312412512612712812913013113213313413513613713813914025141142143144145146147148149150151152153154155156157158159160Note: I left the results in "sequence numbers" representation on purpose,26161162163164165166167168169170171172173174175176177178179180to be much easier to visualize the results.27181182183184185186187188189190191192193194195196197198199200To get them to "range" representation we can use PR(a)  (print results)28201202203204205206207208209210211212213214215216217218219220PR(a)=LAMBDA(a,LET(x,QUOTIENT(a-1,20)+1,y,MOD(a-1,20)+1,ADDRESS(x,y,4)))292212222232242252262272282292302312322332342352362372382392403024124224324424524624724824925025125225325425525625725825926031261262263264265266267268269270271272273274275276277278279280C1B532281282283284285286287288289290291292293294295296297298299300R133T1G434Important Note: Covered here only snake and diagonal search, for being the more complicated ones.T835Anyhow, to cover the rest of them the only thing we have to do is defining new constant arraysD1236cs (constant snake) {-1,1,20,-20}T1537cd (constant diagonal) {-21,-19,19,21}38For example, for horizontal lookup we have to defineK839ch {-1,1} and for vertical cv: {-20,20}40O641M144243WM snake diagonalCell FormulasRangeFormulaW9,W20,W17,A17,AC12,AC9W9=FORMULATEXT(W10)W10:W15W10=WR("snake",cs)AC10AC10=WR("diagonal",cd)AC13AC13=WR("advance",cd)A18:T32A18=SEQUENCE(rw,20)W18W18=WR("difficult",cs)W21:W22W21=WR("winner",cs)W31:W36W31=PR(W10#)AC31AC31=PR(AC10)AC33,W38AC33=PR(AC13)W40:W41W40=PR(W21#)Dynamic array formulas.Named RangesNameRefers ToCellsar='WM snake diagonal'!$A$1:$T$15AC13, AC10, W21, W18, W10


----------



## Xlambda (Jan 30, 2022)

WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQR1WordPart5. step 7.extracting "unique" anagrams2aahedunique anagrams(1st occurrence of words that have anagrams + words that have no anagrams)3aalii=UNIQUE(D4#)=XMATCH(F4#,D4#)=INDEX(wl,I4#)4aarghaadehbaadeh1aahedq5abacaaaiilaaiil2aaliinr.unique anagrams6abaciaaghraaghr3aargh=ROWS(M4#)7abackaaabcaaabc4abaca64958abaftaabciaabci5abaciout of 9abakaaabckaabck6aback893810abampaabftaabft7abaft11abaseaaabkaaabk8abaka12abashaabmpaabmp9abamp13abateaabesaabes10abase14abayaaabhsaabhs11abash15abbasaabetaabet12abate16abbesaaabyaaaby13abaya17abbeyaabbsaabbs14abbas18abbotabbesabbes15abbes19abeamabbeyabbey16abbey20abeleabbotabbot17abbot21abetsaabemaabem18abeam22abhorabeelabeel19abele23abideabestabest20abets24abledabhorabhor21abhor25ablerabdeiabdei22abide26ablesabdelabdel23abled27abmhoabelrabelr24abler28abodeabelsabels25ables29abohmabhmoabhmo26abmho30aboilabdeoabdeo27abode31abomaabhmoabilo29aboil32aboonabiloaabmo30aboma33abortaabmoabnoo31aboon34aboutabnooabort32abort35aboveabortabotu33about36abrisabotuabeov34above37abuseabeovabirs35abris38abutsabirsabesu36abuse39abuzzabesuabstu37abuts40abyesabstuabuzz38abuzz41abysmabuzzabesy39abyes42abyssabesyabmsy40abysm43acariabmsyabssy41abyss44acerbabssyaacir42acari45acetaaacirabcer43acerb46achedabceraacet44aceta47achesaacetacdeh45ached48achooacdehacehs46aches49acidsacehsachoo47achoo50acidyachooacdis48acidsExtracting anagrams 4Cell FormulasRangeFormulaF3,I3,M3,O6F3=FORMULATEXT(F4)D4:D8941D4=BYROW(MID(wl,SEQUENCE(,5),1),LAMBDA(x,CONCAT(SORT(x,,,1))))F4:F6498F4=UNIQUE(D4#)I4:I6498I4=XMATCH(F4#,D4#)M4:M6498M4=INDEX(wl,I4#)O7O7=ROWS(M4#)Dynamic array formulas.Named RangesNameRefers ToCells'Extracting anagrams 4'!wl='Extracting anagrams 4'!$A$2:$A$8939M4, D4


----------



## Xlambda (Jan 30, 2022)

*ANAGRAM function*
*ANAGRAM(a,[aa])*
*a*: array, vertical 1D (any strings or numbers, can have variable lengths, not only 5) ; for 2D input array "a", we can use AFLAT(a)
*[aa]*: anagram argument
*-omitted or 0 or any nr.<>(1,2), extracts only anagrams horizontally, descending occurrence nr.
-if 1, returns only "words" that do not have anagrams
-if 2, 1st occurrence of words with anagrams+words with no anagrams*
*Simplified structure* of the function following all the steps and their variable letters representation:
*=let(b,,u,,x,,q,,switch(aa,1,filter(a,not(x)),2,q,let(d,,e,,f,,final result)))*

```
=LAMBDA(a,[aa],
    LET(r,SEQUENCE(ROWS(a)),c,SEQUENCE(,MAX(LEN(a))),
          b,BYROW(MID(a,c,1),LAMBDA(x,CONCAT(SORT(x,,,1)))),
          u,UNIQUE(b,,1),x,ISNA(XMATCH(b,u)),q,INDEX(a,XMATCH(UNIQUE(b),b)),
       SWITCH(aa,1,FILTER(a,NOT(x)),2,q,
          LET(d,REDUCE(0,r,LAMBDA(v,i,IF(SEQUENCE(i)=i,LET(y,IF(INDEX(b,i)=b,r,""),TRANSPOSE(FILTER(y,y<>""))),v))),
                e,IFERROR(INDEX(a,UNIQUE(FILTER(d,x))),""),
                f,BYROW(e,LAMBDA(y,COUNTA(FILTER(y,y<>"")))),
               INDEX(e,SORTBY(SEQUENCE(ROWS(e)),f,-1),SEQUENCE(,COLUMNS(e)))))
    )
)
```
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMN1sampleany order2abcdaa, omittedbadcaa, omitted3dacb=ANAGRAM(A2:A12,7)1234=ANAGRAM(H2:H12)4adcbabcddacbadcbbadczyxbadcabcddacbadcb5badcxyzzyxyxzabcdzyxyxzxyz6klm1234532154dacb32154123457xyz321548zyxaa,1klmaa,19yxz=ANAGRAM(A2:A12,1)yxz=ANAGRAM(H2:H12,1)1012345klmxyz12341132154123412345klm121234adcb13aa,2aa,214=ANAGRAM(A2:A12,2)=ANAGRAM(H2:H12,2)15abcdbadc16klm123417xyzzyx181234532154191234klm20ANAGRAM functionCell FormulasRangeFormulaC3,J14,C14,J9,C9,J3C3=FORMULATEXT(C4)C4:F6C4=ANAGRAM(A2:A12,7)J4:M6J4=ANAGRAM(H2:H12)C10:C11,J10:J11C10=ANAGRAM(A2:A12,1)C15:C19,J15:J19C15=ANAGRAM(A2:A12,2)Dynamic array formulas.


----------



## Xlambda (Jan 30, 2022)

*ANAGRAM* results for our *Word* list.
Out of *8938* words we have:
*6495 "unique" anagrams *(1st occurrence of anagrams+words with no anagrams)    *=ROWS(ANAGRAM(wl,2))*
*4928 words that have no anagrams    =ROWS(ANAGRAM(wl,1))
1567 words that have at least 1 anagram    =ROWS(ANAGRAM(wl))
4010 total anagrams  *(nr. of non empty cells of ANAGRAM(wl,2) )
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRS1Word=ANAGRAM(A2:A8939,2)6495rowscheck values=COUNTIF(G4#,"?*")2aahed↓=ANAGRAM(A2:A8939,1)4928rows6495-4928=156740104010+4928=89383aalii↓↓=ANAGRAM(A2:A8939)1567rows4aarghaahedaaliiapersapresasperparesparsepearsprasepresarapesreapssparespear5abacaaaliiaarghleastsetalslatestalestealstelataelstalestealstesla6abaciaarghabacaarlesearlslareslaserlearsralesrealsseral7abackabacaabacilapseleapspalespealspleassalepsepalspale8abaftabaciabackperispierspriespriseripesspeirspierspire9abakaabackabaftabetsbastebatesbeastbeatsbetastabes10abampabaftabakaacrescarescarseescarracesscareserac11abaseabakaabampamensmanesmansemeansmensanamesnemas12abashabampabasearilslairslarisliarslirasrailsrials13abateabaseabashaspennapesneapspanespeanssneapspean14abayaabashabatecaretcartecatercratereactrectatrace15abbasabateabayadeistdietsditeseditssitedstiedtides16abbesabayaabbeydiolsidolslidosloidssloidsoldisolid17abbeyabbasabbotdoersdoserredosresodrodesrosedsored18abbotabbesabeleemitsitemsmetismitessmitestimetimes19abeamabbeyabhoresterreestresetsteersteretersetrees20abeleabbotabidelavessalveselvaslavevalesvalseveals21abetsabeamaboilleaptleptapaletpetalplatepleattepal22abhorabeleabomapastepatespeatsseptaspatetapestepas23abideabetsaboonpeersperespersepreespresespeerspree24abledabhoraboutresawsawersewarswareswearwareswears25ablerabideaboveacredarcedcadrecaredcedarraced26ablesabledabuzzairtsastirsitarstairstriatarsi27abmhoablerabyesalbasbaalsbalasbalsabasalsabal28abodeablesabysmalertalterartellaterrateltaler29abohmabmhoacariardebbardebaredbeardbreaddebar30aboilabodeacetaasheddeashhadesheadssadheshade31abomaaboilachedasterratesresatstaretarestears32aboonabomaachoobaresbaserbearsbraessabersabre33abortaboonacidycruetcuretcutereructrecuttruce34aboutabortacingdaterderatratedtaredtradetread35aboveaboutacinideersdreesredesreedssedersered36abrisaboveackeedeilsdelisidlesisledsidleslide37abuseabrisacmicgatergrategreatretagtargeterga38abutsabuseacockhaleshealsleashselahshalesheal39abuzzabutsacoldheroshoershorsehosershoershore40abyesabuzzacuteinsetneistnitessentisteintines41abysmabyesadageleetssleetsteelsteleteelsteles42abyssabysmadaptmanosmasonmoansmonasnomassoman43acariabyssaddaxmatesmeatssatemsteamtamesteams44acerbacariaddednotesonsetsetonstenostonetones45acetaacerbadieupartspratsspratstraptarpstraps46achedacetaadiospilespliesslipespeilspielspile47achesachedadmitporesposerprosereposropesspore48achooachesadmixratosroastrotassortatarostoras49acidsachooadoboreinsresinrinserisenserinsiren50acidyacidsadoptriotsrotistirostorsitriostroisANAGRAM WordCell FormulasRangeFormulaC1C1=FORMULATEXT(C4)F1F1=ROWS(C4#)O1,G3O1=FORMULATEXT(O2)E2E2=FORMULATEXT(E4)H2H2=ROWS(E4#)O2O2=COUNTIF(G4#,"?*")J3J3=ROWS(G4#)C4:C6498C4=ANAGRAM(A2:A8939,2)E4:E4931E4=ANAGRAM(A2:A8939,1)G4:R1570G4=ANAGRAM(A2:A8939)Dynamic array formulas.


----------



## Xlambda (Jan 30, 2022)

*Comparing letters distribution words vs "unique" anagrams.*
*LND* function, *L*etters *N*umbers *D*istribution (dups not counted). Also added *number(digits) functionality*. Calls AFLAT
*LND(ar,[ln])
ar: any array
[ln]: letters numbers argument, 0 or omitted letters ; 1 or <>0 digits*

```
=LAMBDA(ar,[ln],
    LET(a,AFLAT(ar),l,IF(ln,SEQUENCE(10)-1,CHAR(SEQUENCE(26,,97))),s,SEQUENCE(ROWS(a)),
      r,REDUCE(0,s,LAMBDA(v,i,v+ISNUMBER(SEARCH(l,INDEX(a,i))))),
     SORT(IF({1,0},l,r),2,-1)
   )
)
```
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMNOPQRSTUV1WordLetters distribution all words vs "unique" anagramsFunctionality LND, array with letters and digits.2aahed"unique" anagramsln,omittedln,13aalii=ANAGRAM(A2:A8939,2)=LND(A2:A8939)=LND(C4#)sample=LND(N4:O11)=LND(N4:O11,1)4aarghaaheds4124s2636ab23cdpq23y4265abacaaaliie3993e25982223aaabrs12a3556abaciaarghrankinga3620a2435c001destuv432d3347abackabacadifferencesr2753o1958fg4455578dfap3448abaftabacio2632i1849hijxy545x3749abakaabacki2516r1746mn7788y456yb28410abampabaftl2231l1581op78y5678xyzc21311abaseabakat2139t1412opxyz910wkm62f26312abashabampn1922n1386m20213abateabaseu1657u1352o29114abayaabashd1617y1200s215abbasabatec1412d1157z216abbesabayay1372c1084e117abbeyabbasp1310m955g118abbotabbesm1270h939h119abeamabbeyh1188p923i120abeleabbotg1052g819j121abetsabeamb1023b788k122abhorabelek924k747n123abideabetsf708f597q124abledabhorw689w538r125ablerabidev465v364t126ablesabledz227z211u127abmhoablerx209x182v128abodeablesj184j178w129abohmabmhoq79q70l030aboilabode31abomaaboil32aboonaboma33abortaboon34aboutabort35aboveabout36abrisabove37abuseabris38abutsabuse39abuzzabuts40abyesabuzz41abysmabyes42abyssabysm43acariabyss44acerbacari45acetaacerb46achedaceta47achesached48achooaches49acidsachoo50acidyacidsletter distribution 3Cell FormulasRangeFormulaC3,Q3,T3,H3,K3C3=FORMULATEXT(C4)C4:C6498C4=ANAGRAM(A2:A8939,2)H4:I29H4=LND(A2:A8939)K4:L29K4=LND(C4#)Q4:R29Q4=LND(N4:O11)T4:U13T4=LND(N4:O11,1)Dynamic array formulas.


----------



## Xlambda (Jan 30, 2022)

Because playing WORDLE using the function is too simple, *for fun*, I have tried a *totally different approach*, ignoring any tactics or rules, *to form kind of sentences that make sense*, but still to solve the wordle. 
Used wordle.org site that has no playing restrictions.
Here are some captures of the funniest ones:


...and the coolest one that deserves framing: ✌️?


----------



## Xlambda (Feb 4, 2022)

"Wordle Is Joining The New York Times Games"


----------



## Xlambda (Feb 6, 2022)

Modified the function to be able to *input arguments also as cell ranges*. Makes it easier to input values if we play a lot of games
Only 3 modifications:
IF(ISOMITTED(x),…to IF(OR(ISOMITTED(x),x="")…for all "x" b,g,y
Starting words for all games, my "winning" pair: "arise" and "donut"
*Screen captures of all games attached.*

```
=LAMBDA(wa,[b],[g],[gi],[y],[yi],
    LET(s,SEQUENCE(,5),w,MID(wa,s,1),
        k,IF(OR(ISOMITTED(b),b=""),1,BYROW(IFERROR(SEARCH(w,b),0),LAMBDA(x,SUM(x)=0))),
        l,IF(OR(ISOMITTED(g),g=""),1,REDUCE(1,SEQUENCE(LEN(g)),LAMBDA(v,i,v*BYROW(IFERROR(SEARCH(w,MID(g,i,1)),0)*s=--MID(gi,i,1),LAMBDA(x,SUM(--x)))))),
       m,IF(OR(ISOMITTED(y),y=""),1,REDUCE(1,SEQUENCE(LEN(y)),LAMBDA(v,i,v*BYROW(IFERROR(SEARCH(w,MID(y,i,1))*s<>--MID(yi,i,1),0),LAMBDA(x,SUM(--x)))))),
       FILTER(wa,k*l*m)
    )
)
```
WORDLE function WordleHelperFromMrExcel.xlsxABCDEFGHIJKLMN1yesterday's wordle 5-Feb-222https://www.powerlanguage.co.uk/wordle/34indexes=WORDLE(wl,B5,B6,C6,B7,C7)5blackrisednuabbotARISE6greenat15afootDONUT7yellowo2allotALOFT8aloft9various games10https://wordlegame.org/11=WORDLE(wl,B12,B13,C13,B14,C14)12sdonutramieARISE13e5DONUT14ari123RAMIE15=WORDLE(wl,B16,B17,C17,B18,C18)16arisdotneumeARISE17e5uncleDONUT18nu34UNCLE1920=WORDLE(wl,B21,B22,C22,B23,C23)21arisdutmboneyARISE22oney2345coneyDONUT23e5honeyMONEY24HONEY2526=WORDLE(wl,B27,B28,C28,B29,C29)27aidnutcshoreARISE28esor5134sporeDONUT29rso242sworeSCORE30SHORE3132=WORDLE(wl,B33,B34,C34,B35,C35)33sedoutbrainARISE34r2grainDONUT35ain133BRAIN3637=WORDLE(wl,B38,B39,C39,B40,C40)38rsedutpianoARISE39DONUT40aion1323PIANO41Wordle archiveCell FormulasRangeFormulaE4,E37,E32,E26,E20,E15,E11E4=FORMULATEXT(E5)E5:E8,E38,E33:E34,E27:E29,E21:E23,E16:E17,E12E5=WORDLE(wl,B5,B6,C6,B7,C7)Dynamic array formulas.Named RangesNameRefers ToCellswl='Extracting anagrams 1'!$A$2:$A$8939E5, E12, E16, E21, E27, E33, E38


----------

