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
860
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
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.
 
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. ✌
 
WORD MAZE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
11234567891011121314151617181920Concept. Part 1
22122232425262728293031323334353637383940Finding constant array values for different types of lookups:
34142434445464748495051525354555657585960
46162636465666768697071727374757677787980Random 3x3 area of sequence array
581828384858687888990919293949596979899100125126127
6101102103104105106107108109110111112113114115116117118119120145146147
7121122123124125126127128129130131132133134135136137138139140165166167
8141142143144145146147148149150151152153154155156157158159160
9161162163164165166167168169170171172173174175176177178179180If we substract the center value, we get relative values
10181182183184185186187188189190191192193194195196197198199200for any 3x3 area of the sequence
11201202203204205206207208209210211212213214215216217218219220=V5:X7-W6
12221222223224225226227228229230231232233234235236237238239240-21-20-19
13241242243244245246247248249250251252253254255256257258259260-101
14261262263264265266267268269270271272273274275276277278279280192021
15281282283284285286287288289290291292293294295296297298299300
16For snake: cs={-20,-1,1.20} or {-1,1,20,-20}
17Note: order is not important
18since values extracted using these constants
19are unique and sorted with UNIQUE(SMALL….
20-20
21-11
2220
23
24For diagonal: cd={-21,-19,19,21}
25-21-19
26
271921
28
cnst array
Cell Formulas
RangeFormula
A1:T15A1=SEQUENCE(15,20)
V11V11=FORMULATEXT(V12)
V12:X14V12=V5:X7-W6
Dynamic array formulas.
 
WORD MAZE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Concept. Part 2. Functions
2- 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 values
6We start backwards with the last letter of "winner", "r"
7
8=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"
1041
1146=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)))
131104240216121
141434745266622=WB(H12#,WL("e"))step 4. repeat from step 2 and so on until we run out of letters
151529492731132445=WA(K15#,cs)
16175111109901302617425=WB(N16#,WL("n"))
17185144142123163401984446
181971531511321724220546154=WA(P17#,cs)=WB(R19#,WL("n"))
19217176174155195432176518526155
202241861841652054521815419445271
212331981961772174725017321747
222352182161972376117523866=WA(T19#,cs)=WB(U23#,WL("i"))
2324922522320424466178251134135135
2425123423221325373185153154251
2529923623421525590194155156272
2625024822926992197165175=WA(W23#,cs)
2725225023127194198174251115last step, for last letter "w"
28300298279319109199184270134=WB(X27#,WL("w"))
29111204186272136115
30113206193291155273
31123216195231
32130217197250All these steps are embedded
33132218205252in the recursive function WR
34142219214271Since we started from last to first
35144225216273these represent also the final result
36151230218292
37153237231
38155238237
39163249239
40165251250
41172270252
42174258
43176271
44177
45184
46186
47195
48196
49197
50198
51204
52205
53213
54215
55216
56217
57218
58223
59225
60229
61231
62232
63234
64236
65237
66244
67248
68250
69252
70253
71255
72269
73271
74279
75298
76300
77319
78
functions
Cell Formulas
RangeFormula
A8,Z28,X26,U22,W22,R18,T18,P16,N15,K14,H11,C11A8=FORMULATEXT(A9)
A9:A25A9=WL("r")
C12:F28C12=A9#-cs
H12: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 Ranges
NameRefers ToCells
ar='WM snake diagonal'!$A$1:$T$15A9, K15, P17, T19, W23, Z29
 
WORD MAZE.xlsx
ABCDEFGHIJ
1Concept. Part 3. The recursive function
2 =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: word
4c: constant array, our case is cs
5[r]: carries the result
6[i ]: carries the iteration nr.
7
8first iteration WR("winner",cs)
9i is omitted<=>i=0 => n=LEN("winner")=6
10x=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=5
142nd iteration i=i1=5 =>n=5
15x=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=4
193rd iteration i=i2=4 =>n=4
20y=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=3
234th iteration i=i3=3 =>n=3
24y=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=2
275th iteration i=i4=2 =>n=2
28y=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=1
316th iteration i=i5=1 =>n=1
32x,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
 
WORD MAZE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1AKEEPfunction approach, the eye muscle solution
2=ar=AKEEP(A3#,"DIFFICULT"&"?")
3Z1SGPE2THEDIZBANTSNS TDIT
4SP?AKIWK1FHVZG2IEKAX?IFI
5?ITQE?LASIXQJNSX4TJK?IT?LIT
6LENJFXA4TIKNSBX1DJCPLFTIDC
7GDL33DMVLTGK?CU3OQOODLDLT?CU
8IAIUVGYLU?IEP3WGKBTPIIULU?IT
94APAU4CZVFFFAFIPAXTTUCFFFFITT
10HN?4GCKS4IDRHNNAJDNS?CIDD
11EC3XCODBVQOYJERGSKAHCCD
12EX1Z?PNI3OWCCN2UREGJ?ICCU
13EONMEVKABEPFLQNZ?EW3FL?
14KAN?AIBKLG24?N?ZBNQE?IL??
15SMO4YFBBRE?DNE1ADJM4F?DD
16CFX43TY1ZJNIWUZ3OWHMCFTIU
17TYD2YZSUDVVF4OZEKA?STDUDF?
18
19answer:K10
20
21Note: AKEEP was designed case sensitive
22
AKEEP
Cell Formulas
RangeFormula
A2,V2A2=FORMULATEXT(A3)
A3:T17A3=ar
V3:AO17V3=AKEEP(A3#,"DIFFICULT"&"?")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ar='WM snake diagonal'!$A$1:$T$15A3
 
WORD MAZE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1If we don't have AKEEP, we can write a simple lambda "formula"
2w=difficult
3We 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 TDIIT
7?IIFFII
8?IIT?LIIT
9LFFTIIDC
10DLDLT?CU
11IIIIULU?IIT
12UCFFFFFFFFIITT
13?CIIDD
14CCD
15?IICCU
16FFL?
17?IIL??
18FF?DD
19CFFTIIU
20TDUDFF?
21
22We 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 TDIT
25?IFI
26?IT?LIT
27LFTIDC
28DLDLT?CU
29IIULU?IT
30UCFFFFITT
31?CIDD
32CCD
33?ICCU
34FL?
35?IL??
36F?DD
37CFTIU
38TDUDF?
39
AKEEP rplc
Cell Formulas
RangeFormula
A5,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 Ranges
NameRefers ToCells
ar='WM snake diagonal'!$A$1:$T$15A6, A24
 
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.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1SNAKSNAKEJSNA?EUVFECDefined names
21TDDPUF3MFNUFPDWDGIDch/cv constant array values for horiz/vert lookup
3WN4EH3DZNPAB2IXBRJHSch={-1,1}
4ICKCCZT3LYKHQLLOLQ1Rcv={-20,20}
5NKMR4VWINNE?FRE?NIWM
6NJXMTDVNRR2THCOQRMTKhoriz. Lookup
7EECPNICSEJTMG1IVRX?U=WR("winner",ch)=WR("snake",ch)=WR("difficult",ch)
8?YD?FFIC?L?KYNWYL3NT875143
9EZHJOFURNBUIS2FHI1A19911
10NHFNFIUHILC4E1FLVAK3272299
11NK3BMCH2WXIWMG3YTCES
12INJMM?IBRLFDLZMTODKC
13WIHRLLOVY2FS1NKDHBAPvert.lookup
14S1MKWTRENNI?HTOPTQNB=WR("winner",cv)=WR("snake",cv)=WR("difficult",cv)
15UCPSCVNYPGDIT1EKAN?U4111106
16209139291
17=SEQUENCE(15,20)241299
181234567891011121314151617181920
192122232425262728293031323334353637383940
204142434445464748495051525354555657585960horiz. Lookup
216162636465666768697071727374757677787980=PR(W8#)=PR(AA8#)=PR(AE8)
2281828384858687888990919293949596979899100G5E1C8
23101102103104105106107108109110111112113114115116117118119120S5K1
24121122123124125126127128129130131132133134135136137138139140L14S15
25141142143144145146147148149150151152153154155156157158159160
26161162163164165166167168169170171172173174175176177178179180
27181182183184185186187188189190191192193194195196197198199200vert.lookup
28201202203204205206207208209210211212213214215216217218219220=PR(W15#)=PR(AA15#)=PR(AE15#)
29221222223224225226227228229230231232233234235236237238239240A3K1F6
30241242243244245246247248249250251252253254255256257258259260I11S7K15
31261262263264265266267268269270271272273274275276277278279280A13S15
32281282283284285286287288289290291292293294295296297298299300
33
ch cv
Cell Formulas
RangeFormula
W7,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.
 
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.xlsx
ABCDEFGHIJKLM
1Binary search modedata set in D3: =SORTBY(SEQUENCE(700000),RANDARRAY(700000))
2=SEQUENCE(700000)=XMATCH(D3:D700002,B3#)=XMATCH(D3:D700002,B3#,,2)
31186502186502first to last186502binary
42675719675719675719
53607066607066607066
64137492137492137492
75974699746997469
86385721385721385721
97404557404557404557
108405730405730405730
119135774135774135774
1210619625619625619625
1311218929218929218929
1412345110345110345110
1513264918264918264918
1614578895788957889
1715627344627344627344
1816662936662936662936
1917580475804758047
2018336288336288336288
2119597464597464597464
2220181445181445181445
2321590479590479590479
2422433279433279433279
2523463257463257463257
2624440100440100440100
2725211604211604211604
2826532675532675532675
2927540461540461540461
3028574045574045down to574045
3129577899577899700000 rws.577899
32309988299882↓↓↓↓99882
3331681977681977681977
Sheet6
Cell Formulas
RangeFormula
B2,J2,F2B2=FORMULATEXT(B3)
B3:B700002B3=SEQUENCE(700000)
F3:F700002F3=XMATCH(D3:D700002,B3#)
J3:J700002J3=XMATCH(D3:D700002,B3#,,2)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,526
Messages
6,172,834
Members
452,483
Latest member
Johnstone

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