WR

WR(w,c,[r],[i])
w
word
c
constant arrays (in our example cs for snake lookup, cd for diagonal lookup)
[r]
always omitted, carries the final result
[i]
always omitted, carries nr. iterations, depending of LEN(w)
Set of functions and study for solving the Word Maze Excel Challenge
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
832
Office Version
  1. 365
Platform
  1. Windows
WR: Word maze Results 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.
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1Z1SGPE2THEDIZBANTSNSdefined names
2SP?AKIWK1FHVZG2IEKAXar: A1:T15 (sample array)
3?ITQE?LASIXQJNSX4TJKrw: 15 (width is always 20)
4LENJFXA4TIKNSBX1DJCPcs: {-1,1,20,-20} (constant array for "snake" type lookup
5GDL33DMVLTGK?CU3OQOOcd: {-21,-19,19,21} (constant array for "diagonal" type lookup
6IAIUVGYLU?IEP3WGKBTP
74APAU4CZVFFFAFIPAXTT"snake" lookup"diagonal" lookup
8HN?4GCKS4IDRHNNAJDNS
9EC3XCODBVQOYJERGSKAH=WR("snake",cs)=WR("diagonal",cd)
10EX1Z?PNI3OWCCN2UREGJ382
11EONMEVKABEPFLQNZ?EW318
12KAN?AIBKLG24?N?ZBNQE20=WR("advance",cd)
13SMO4YFBBRE?DNE1ADJM416067
14CFX43TY1ZJNIWUZ3OWHM224
15TYD2YZSUDVVF4OZEKA?S300
16
17=SEQUENCE(rw,20)=WR("difficult",cs)
181234567891011121314151617181920151
192122232425262728293031323334353637383940
204142434445464748495051525354555657585960=WR("winner",cs)
216162636465666768697071727374757677787980115
2281828384858687888990919293949596979899100273
23101102103104105106107108109110111112113114115116117118119120
24121122123124125126127128129130131132133134135136137138139140
25141142143144145146147148149150151152153154155156157158159160Note: 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)))
29221222223224225226227228229230231232233234235236237238239240
30241242243244245246247248249250251252253254255256257258259260
31261262263264265266267268269270271272273274275276277278279280C1B5
32281282283284285286287288289290291292293294295296297298299300R1
33T1G4
34Important Note: Covered here only snake and diagonal search, for being the more complicated ones.T8
35Anyhow, to cover the rest of them the only thing we have to do is defining new constant arraysD12
36cs (constant snake) {-1,1,20,-20}T15
37cd (constant diagonal) {-21,-19,19,21}
38For example, for horizontal lookup we have to defineK8
39ch {-1,1} and for vertical cv: {-20,20}
40O6
41M14
42
43
WM snake diagonal
Cell Formulas
RangeFormula
W9,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 Ranges
NameRefers ToCells
ar='WM snake diagonal'!$A$1:$T$15AC13, AC10, W21, W18, W10
 
Upvote 0
WORDLE function WordleHelperFromMrExcel.xlsx
ABCDEFGHIJKLMNOPQR
1WordPart5. step 7.extracting "unique" anagrams
2aahedunique anagrams(1st occurrence of words that have anagrams + words that have no anagrams)
3aalii=UNIQUE(D4#)=XMATCH(F4#,D4#)=INDEX(wl,I4#)
4aarghaadehbaadeh1aahedq
5abacaaaiilaaiil2aaliinr.unique anagrams
6abaciaaghraaghr3aargh=ROWS(M4#)
7abackaaabcaaabc4abaca6495
8abaftaabciaabci5abaciout of
9abakaaabckaabck6aback8938
10abampaabftaabft7abaft
11abaseaaabkaaabk8abaka
12abashaabmpaabmp9abamp
13abateaabesaabes10abase
14abayaaabhsaabhs11abash
15abbasaabetaabet12abate
16abbesaaabyaaaby13abaya
17abbeyaabbsaabbs14abbas
18abbotabbesabbes15abbes
19abeamabbeyabbey16abbey
20abeleabbotabbot17abbot
21abetsaabemaabem18abeam
22abhorabeelabeel19abele
23abideabestabest20abets
24abledabhorabhor21abhor
25ablerabdeiabdei22abide
26ablesabdelabdel23abled
27abmhoabelrabelr24abler
28abodeabelsabels25ables
29abohmabhmoabhmo26abmho
30aboilabdeoabdeo27abode
31abomaabhmoabilo29aboil
32aboonabiloaabmo30aboma
33abortaabmoabnoo31aboon
34aboutabnooabort32abort
35aboveabortabotu33about
36abrisabotuabeov34above
37abuseabeovabirs35abris
38abutsabirsabesu36abuse
39abuzzabesuabstu37abuts
40abyesabstuabuzz38abuzz
41abysmabuzzabesy39abyes
42abyssabesyabmsy40abysm
43acariabmsyabssy41abyss
44acerbabssyaacir42acari
45acetaaacirabcer43acerb
46achedabceraacet44aceta
47achesaacetacdeh45ached
48achooacdehacehs46aches
49acidsacehsachoo47achoo
50acidyachooacdis48acids
Extracting anagrams 4
Cell Formulas
RangeFormula
F3,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 Ranges
NameRefers ToCells
'Extracting anagrams 4'!wl='Extracting anagrams 4'!$A$2:$A$8939M4, D4
 
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)))
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMN
1sampleany order
2abcdaa, omittedbadcaa, omitted
3dacb=ANAGRAM(A2:A12,7)1234=ANAGRAM(H2:H12)
4adcbabcddacbadcbbadczyxbadcabcddacbadcb
5badcxyzzyxyxzabcdzyxyxzxyz
6klm1234532154dacb3215412345
7xyz32154
8zyxaa,1klmaa,1
9yxz=ANAGRAM(A2:A12,1)yxz=ANAGRAM(H2:H12,1)
1012345klmxyz1234
1132154123412345klm
121234adcb
13aa,2aa,2
14=ANAGRAM(A2:A12,2)=ANAGRAM(H2:H12,2)
15abcdbadc
16klm1234
17xyzzyx
181234532154
191234klm
20
ANAGRAM function
Cell Formulas
RangeFormula
C3,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.
 
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.xlsx
ABCDEFGHIJKLMNOPQRS
1Word=ANAGRAM(A2:A8939,2)6495rowscheck values=COUNTIF(G4#,"?*")
2aahed↓=ANAGRAM(A2:A8939,1)4928rows6495-4928=156740104010+4928=8938
3aalii↓↓=ANAGRAM(A2:A8939)1567rows
4aarghaahedaaliiapersapresasperparesparsepearsprasepresarapesreapssparespear
5abacaaaliiaarghleastsetalslatestalestealstelataelstalestealstesla
6abaciaarghabacaarlesearlslareslaserlearsralesrealsseral
7abackabacaabacilapseleapspalespealspleassalepsepalspale
8abaftabaciabackperispierspriespriseripesspeirspierspire
9abakaabackabaftabetsbastebatesbeastbeatsbetastabes
10abampabaftabakaacrescarescarseescarracesscareserac
11abaseabakaabampamensmanesmansemeansmensanamesnemas
12abashabampabasearilslairslarisliarslirasrailsrials
13abateabaseabashaspennapesneapspanespeanssneapspean
14abayaabashabatecaretcartecatercratereactrectatrace
15abbasabateabayadeistdietsditeseditssitedstiedtides
16abbesabayaabbeydiolsidolslidosloidssloidsoldisolid
17abbeyabbasabbotdoersdoserredosresodrodesrosedsored
18abbotabbesabeleemitsitemsmetismitessmitestimetimes
19abeamabbeyabhoresterreestresetsteersteretersetrees
20abeleabbotabidelavessalveselvaslavevalesvalseveals
21abetsabeamaboilleaptleptapaletpetalplatepleattepal
22abhorabeleabomapastepatespeatsseptaspatetapestepas
23abideabetsaboonpeersperespersepreespresespeerspree
24abledabhoraboutresawsawersewarswareswearwareswears
25ablerabideaboveacredarcedcadrecaredcedarraced
26ablesabledabuzzairtsastirsitarstairstriatarsi
27abmhoablerabyesalbasbaalsbalasbalsabasalsabal
28abodeablesabysmalertalterartellaterrateltaler
29abohmabmhoacariardebbardebaredbeardbreaddebar
30aboilabodeacetaasheddeashhadesheadssadheshade
31abomaaboilachedasterratesresatstaretarestears
32aboonabomaachoobaresbaserbearsbraessabersabre
33abortaboonacidycruetcuretcutereructrecuttruce
34aboutabortacingdaterderatratedtaredtradetread
35aboveaboutacinideersdreesredesreedssedersered
36abrisaboveackeedeilsdelisidlesisledsidleslide
37abuseabrisacmicgatergrategreatretagtargeterga
38abutsabuseacockhaleshealsleashselahshalesheal
39abuzzabutsacoldheroshoershorsehosershoershore
40abyesabuzzacuteinsetneistnitessentisteintines
41abysmabyesadageleetssleetsteelsteleteelsteles
42abyssabysmadaptmanosmasonmoansmonasnomassoman
43acariabyssaddaxmatesmeatssatemsteamtamesteams
44acerbacariaddednotesonsetsetonstenostonetones
45acetaacerbadieupartspratsspratstraptarpstraps
46achedacetaadiospilespliesslipespeilspielspile
47achesachedadmitporesposerprosereposropesspore
48achooachesadmixratosroastrotassortatarostoras
49acidsachooadoboreinsresinrinserisenserinsiren
50acidyacidsadoptriotsrotistirostorsitriostrois
ANAGRAM Word
Cell Formulas
RangeFormula
C1C1=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.
 
Comparing letters distribution words vs "unique" anagrams.
LND function, Letters Numbers Distribution (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
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1WordLetters distribution all words vs "unique" anagramsFunctionality LND, array with letters and digits.
2aahed"unique" anagramsln,omittedln,1
3aalii=ANAGRAM(A2:A8939,2)=LND(A2:A8939)=LND(C4#)sample=LND(N4:O11)=LND(N4:O11,1)
4aarghaaheds4124s2636ab23cdpq23y426
5abacaaaliie3993e25982223aaabrs12a355
6abaciaarghrankinga3620a2435c001destuv432d334
7abackabacadifferencesr2753o1958fg4455578dfap344
8abaftabacio2632i1849hijxy545x374
9abakaabacki2516r1746mn7788y456yb284
10abampabaftl2231l1581op78y5678xyzc213
11abaseabakat2139t1412opxyz910wkm62f263
12abashabampn1922n1386m202
13abateabaseu1657u1352o291
14abayaabashd1617y1200s2
15abbasabatec1412d1157z2
16abbesabayay1372c1084e1
17abbeyabbasp1310m955g1
18abbotabbesm1270h939h1
19abeamabbeyh1188p923i1
20abeleabbotg1052g819j1
21abetsabeamb1023b788k1
22abhorabelek924k747n1
23abideabetsf708f597q1
24abledabhorw689w538r1
25ablerabidev465v364t1
26ablesabledz227z211u1
27abmhoablerx209x182v1
28abodeablesj184j178w1
29abohmabmhoq79q70l0
30aboilabode
31abomaaboil
32aboonaboma
33abortaboon
34aboutabort
35aboveabout
36abrisabove
37abuseabris
38abutsabuse
39abuzzabuts
40abyesabuzz
41abysmabyes
42abyssabysm
43acariabyss
44acerbacari
45acetaacerb
46achedaceta
47achesached
48achooaches
49acidsachoo
50acidyacids
letter distribution 3
Cell Formulas
RangeFormula
C3,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.
 
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:
excel chess.pngPicture2.pngPicture3.pngPicture4.pngPicture5.png
...and the coolest one that deserves framing: ✌️?
Picture6.png
 
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.
Excel Formula: 
=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.xlsx
ABCDEFGHIJKLMN
1yesterday's wordle 5-Feb-22
2https://www.powerlanguage.co.uk/wordle/
3
4indexes=WORDLE(wl,B5,B6,C6,B7,C7)
5blackrisednuabbotARISE
6greenat15afootDONUT
7yellowo2allotALOFT
8aloft
9various games
10https://wordlegame.org/
11=WORDLE(wl,B12,B13,C13,B14,C14)
12sdonutramieARISE
13e5DONUT
14ari123RAMIE
15=WORDLE(wl,B16,B17,C17,B18,C18)
16arisdotneumeARISE
17e5uncleDONUT
18nu34UNCLE
19
20=WORDLE(wl,B21,B22,C22,B23,C23)
21arisdutmboneyARISE
22oney2345coneyDONUT
23e5honeyMONEY
24HONEY
25
26=WORDLE(wl,B27,B28,C28,B29,C29)
27aidnutcshoreARISE
28esor5134sporeDONUT
29rso242sworeSCORE
30SHORE
31
32=WORDLE(wl,B33,B34,C34,B35,C35)
33sedoutbrainARISE
34r2grainDONUT
35ain133BRAIN
36
37=WORDLE(wl,B38,B39,C39,B40,C40)
38rsedutpianoARISE
39DONUT
40aion1323PIANO
41
Wordle archive
Cell Formulas
RangeFormula
E4,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 Ranges
NameRefers ToCells
wl='Extracting anagrams 1'!$A$2:$A$8939E5, E12, E16, E21, E27, E33, E38
 

Attachments

  • Wordle 5feb22.png
    Wordle 5feb22.png
    57.2 KB · Views: 11
  • Wgorg 6feb22.png
    Wgorg 6feb22.png
    154.7 KB · Views: 10
You must log in or register to reply here.

Forum statistics

Threads
1,215,168
Messages
6,123,402
Members
449,098
Latest member
ArturS75

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top