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
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: 17
  • Wgorg 6feb22.png
    Wgorg 6feb22.png
    154.7 KB · Views: 16
Check out this challenge:
Create a LAMBDA to extract all unique prime numbers that can be found in an input grid (where you can read up, down, left, right, or any diagonal direction to find a number).
Chose this thread because main concept to solve it resides in extracting diagonals of arrays, covered here : WR
The difference between solving and designing is modular thinking that creates concepts that can be reused in other circumstances. Like code subroutines.
3 concepts for 3 "subroutines":

1. extracting all lines, rows, clms, diagonals (rws and clms easy, diag mainly)
2. combining all adjacent chars of a string/array of strings, both directions
3. checking if a nr. is prime

1. extracting diagonals . Concept (again)
based on a very simple formula of pattern manipulation. for an array of r x c dim. seq(r+c-1)-seq(,c)+1 solves diagonal extraction
(groupby can not extract, only concatenate values, pivotby can extract but is too much of a function for a simple formula task)
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
11. Concept. Extractind diagonals
2left to right top to bottom, - clms index distrib unchanged
3diagonals are: -rws index pattern looks like this
4a=4r x 5c
51234511
66789106221
711121314151173321
816171819201612844321
91713954321
10181410432
11191543
12204
131.2.
143.form to calc. rws index patternNote: Bcs INDEX(a,0,x)=INDEX(a,1,x)
15seq(r+c-1)-seq(,c)+1will replace 0 with -1, to trigger an err for 0's
16=IFERROR(INDEX(B5#,W17#,SEQUENCE(,5)),"")=SEQUENCE(4+5-1)-SEQUENCE(,5)+1=IF(P17#,P17#,-1)
17110-1-2-31-1-1-2-3
1862210-1-221-1-1-2
1911733210-1321-1-1
20161284432104321-1
211713955432154321
221814106543265432
2319157654376543
24208765487654
25
26to get other direction diagonals, (left to right, bottom top) we only have to flip vertically the initial array and apply same formula
27
284.5.
29=CHOOSEROWS(B5#,-SEQUENCE(4))=IFERROR(INDEX(B30#,W17#,SEQUENCE(,5)),"")
30161718192016
3111121314151117
3267891061218
3312345171319
34281420
353915
36410
375
38
39Note: Could have been left align, but for most scenarios that involve diagonals, also clms need to stay consistent
40(was very useful for world puzzles, and I use it also for the algorithms that solve sudoku)
41
Sheet1
Cell Formulas
RangeFormula
B5:F8B5=SEQUENCE(4,5)
G16G16=FORMULATEXT(H17)
P16,W16,B29,H29P16=FORMULATEXT(P17)
H17:L24H17=IFERROR(INDEX(B5#,W17#,SEQUENCE(,5)),"")
P17:T24P17=SEQUENCE(4+5-1)-SEQUENCE(,5)+1
W17:AA24W17=IF(P17#,P17#,-1)
B30:F33B30=CHOOSEROWS(B5#,-SEQUENCE(4))
H30:L37H30=IFERROR(INDEX(B30#,W17#,SEQUENCE(,5)),"")
Dynamic array formulas.


1. extracting diagonals. The function:
DG(a) DiaGonals function a: any array
Excel Formula: 
=LAMBDA(a,
    LET(
        r, ROWS(a),
        c, COLUMNS(a),
        q, SEQUENCE(r),
        s, SEQUENCE(, c),
        x, SEQUENCE(r + c - 1) - s + 1,
        y, IF(x, x, -1),
        IFERROR(VSTACK(INDEX(a, y, s), INDEX(CHOOSEROWS(a, -q), y, s)), "")
    )
)
Book1
ABCDEFGHIJKLMNOPQRS
1
21234512312345
3678910456678910
411121314157891112131415
51011121617181920
6=DG(B2#)1314152122232425
71
862=DG(I2#)=DG(N2#)
9117311
1012844262
1113957531173
1214101086161284
13151311921171395
1411141222181410
1561215231915
161713132420
172814101425
1839157111521
1941048121622
205159111723
21266121824
22317131925
23281420
243915
25Checking function's robustness with vectors:410
265
2711231
282
293=DG(E27#)=DG(J27)
30411
3121
32=DG(B27#)3
3311=UNIQUE(J30#)
34221
3533
364
374=UNIQUE(E30#)
3831
3922
4013
41
42=UNIQUE(B33#)
431
442
453
464
47
Sheet2
Cell Formulas
RangeFormula
B2:F4B2=SEQUENCE(3,5)
I2:K6I2=SEQUENCE(5,3)
N2:R6N2=SEQUENCE(5,5)
B6,B42,E37,J33,B32,J29,E29,N8,I8B6=FORMULATEXT(B7)
B7:F20B7=DG(B2#)
I9:K22,N9:R26I9=DG(I2#)
B27:B30B27=SEQUENCE(4)
E27:G27E27=SEQUENCE(,3)
E30:G35E30=DG(E27#)
J30:J31J30=DG(J27)
B33:B40B33=DG(B27#)
J34J34=UNIQUE(J30#)
E38:G40E38=UNIQUE(E30#)
B43:B46B43=UNIQUE(B33#)
Dynamic array formulas.


2. combining all adjacent chars of a string/array of strings, both directions. Concept and functions
pattern manipulation trick here for a string tx : mid(tx, seq( ,n ),seq( n )) where n=len(tx)
functions
CHRC(t) Char Combinations,
Excel Formula: 
=LAMBDA(t,
    LET(
        n, LEN(t),
        q, SEQUENCE(n),
        s, SEQUENCE(, n),
        v, CONCAT(MID(t, n - s + 1, 1)),
        UNIQUE(SORT(TOCOL(VSTACK(MID(t, s, q), MID(v, s, q)))))
    )
)
ACHRC(a) Array of Chars Combinations
Excel Formula: 
=LAMBDA(a,UNIQUE(SORT(DROP(REDUCE("",a,LAMBDA(v,i,VSTACK(v,CHRC(i)))),1))))
Book1
ABCDEFGHIJKLMNOPQ
12. Concept.All adjacent combinations of characters of a string.(both directions)
2
3len=5ab1234
4abcde567xyx
5
6=MID(B15,SEQUENCE(,5),SEQUENCE(5))=UNIQUE(SORT(TOCOL(VSTACK(B18#,B7#))))=ACHRC(O3:P4)
7edcbaa1
8eddccbbaaab12
9edcdcbcbabaaabc123
10edcbdcbacbabaaabcd1234
11edcbadcbacbabaaabcde2
12b21
13reverse stringba23
14=CONCAT(MID(B4,5-SEQUENCE(,5)+1,1))bc234
15edcbabcd3
16bcde32
17=MID(B4,SEQUENCE(,5),SEQUENCE(5))c321
18abcdecb34
19abbccddeecba4
20abcbcdcdedeecd43
21abcdbcdecdedeecde432
22abcdebcdecdedeed4321
23dc5
24dcb56
25dcba567
26de6
27e65
28ed67
29edc7
30edcb76
31edcba765
32a
33ab
34b
35ba
36x
37xy
38xyx
39y
40yx
41
Sheet3
Cell Formulas
RangeFormula
B6,H6,O6,B17,B14B6=FORMULATEXT(B7)
B7:F11B7=MID(B15,SEQUENCE(,5),SEQUENCE(5))
H7:H31H7=UNIQUE(SORT(TOCOL(VSTACK(B18#,B7#))))
O7:O40O7=ACHRC(O3:P4)
B15B15=CONCAT(MID(B4,5-SEQUENCE(,5)+1,1))
B18:F22B18=MID(B4,SEQUENCE(,5),SEQUENCE(5))
Dynamic array formulas.


3. checking if a nr. is prime. Concept and ISP(a) Is Prime function
Prime numbers are defined as positive integers greater than 1 that have no positive divisors other than 1 and themselves.
To check whether a number "n" is Prime or not, we need to only check if n is divisible by numbers<=SQRT( n )
If a nr. "n" is not prime, there are at least 2 divisors a*b=n, if a=b => a=sqrt( n ) => n not prime. If a<>b one of them should be < sqrt( n ) , therefore If a number n has at least one divisor <=sqrt( n ) definitely is not prime.
If d is a divisor of n => mod(n,d)=0
ISP(a) IS Prime function a: any array of numbers
Excel Formula: 
=LAMBDA(a,
    LET(
        p, LAMBDA(x, AND(MOD(x, SEQUENCE(FLOOR(SQRT(x), 1) - 1) + 1))),
        m, IF(a < 2, 0, SWITCH(a, 2, 1, 3, 1, MAP(a, p))),
        IF(m, TRUE)
    )
)

Book1
ABCDEFGHIJKLMNOPQRS
13. Concept. ISP function ( Is Prime function)
2
3=FLOOR(SQRT(B6),1)
4↓↓↓=SEQUENCE(D6-1)+1
5↓↓↓↓↓↓=MOD(B6,F6#)=AND(H6#)=RANDARRAY(5,5,100000000,999999999,1)
651721FALSE978915463625305937387121511278951036914817684
730299589471904595550458146362202862617376072833
843660451690923709610589690026783864507250388521
951317819665480563307872600185729193194742653472
1063928484755102397698188403383875230324831080166
1172
12=ISP(N6#)
13=FLOOR(SQRT(B14),1)FALSEFALSEFALSEFALSEFALSE
1453↓↓↓=SEQUENCE(D16-1)+1FALSEFALSEFALSETRUEFALSE
15↓↓↓↓↓↓=MOD(B14,F16#)=AND(H16#)FALSEFALSEFALSEFALSETRUE
16721TRUEFALSEFALSEFALSEFALSEFALSE
1732FALSEFALSEFALSEFALSEFALSE
1841
1953
2065=FILTER(TOCOL(N6#),TOCOL(N13#),"")
2174202862617
22250388521
23
Sheet4
Cell Formulas
RangeFormula
D3,D13D3=FORMULATEXT(D6)
F4,F14F4=FORMULATEXT(F6)
H5,K5,N20,H15,K15,N12,N5H5=FORMULATEXT(H6)
D6D6=FLOOR(SQRT(B6),1)
F6:F11,F16:F21F6=SEQUENCE(D6-1)+1
H6:H11H6=MOD(B6,F6#)
K6,K16K6=AND(H6#)
N6:R10N6=RANDARRAY(5,5,100000000,999999999,1)
N13:R17N13=ISP(N6#)
D16D16=FLOOR(SQRT(B14),1)
H16:H21H16=MOD(B14,F16#)
N21:N22N21=FILTER(TOCOL(N6#),TOCOL(N13#),"")
Dynamic array formulas.
 
Final requested lambda
PRMC(a,n) Prime Numbers Combinations
a: array of digits or numbers
[n]: if omitted list of prime numbers, if 1 total nr. of prime nr.
Excel Formula: 
=LAMBDA(a, [n],
    LET(
        b, SORT(--ACHRC(BYROW(IFNA(VSTACK(a, TRANSPOSE(a), DG(a)), ""), CONCAT))),
        f, FILTER(b, ISP(b)),
        IF(n, ROWS(f), f)
    )
)
Conclusion: seq(r+c-1)-seq(,c)+1 is another proof that mastering patterns can solve complex tasks as we've seen already so many times. Basically only simple arithmetic calculations, fast and efficient.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Final requested lambda PRMC(a) Prime Numbers Combinations
2extract all digits combinations
3=UNIQUE(SORT(--ACHRC(BYROW(J6#,CONCAT))))
4extracting all lines(r,c,diag)↓↓↓↓extract prime numbers
5=VSTACK(DG(B6:H12),B6:H12,TRANSPOSE(B6:H12))↓↓↓↓=FILTER(R6#,ISP(R6#))=PRMC(B6:H12)
631373393122
799233339123=ROWS(U6#)3=PRMC(B6:H12,1)
86977894693352815281
976159197927477
1077342117673351111
11994793997173361313
123371999393583971717
1334499381919
147721492323
151919112929
16931123131
1799133737
189144141
193154343
2093164747
21797175353
227741185959
2366379196161
24991499216767
253975239237171
26127919247373
2733811257979
287399278383
29334298989
3033319797
31932113113
32313733933127127
33992333334137137
34697789435139139
35761591936149149
36773421137167167
37994793938173173
38337199939179179
39396779341191191
40199679342193193
41327134743197197
42737547144199199
43338929945211211
44339113947233233
45934919949239239
4651257257
4752271271
4853277277
Sheet5
Cell Formulas
RangeFormula
R3R3=FORMULATEXT(R6)
J5,AE7,X7,AB5,U5J5=FORMULATEXT(J6)
J6:P45J6=VSTACK(DG(B6:H12),B6:H12,TRANSPOSE(B6:H12))
R6:R654R6=UNIQUE(SORT(--ACHRC(BYROW(J6#,CONCAT))))
U6:U286U6=FILTER(R6#,ISP(R6#))
AB6:AB286AB6=PRMC(B6:H12)
X8X8=ROWS(U6#)
AE8AE8=PRMC(B6:H12,1)
Dynamic array formulas.


Having a lambda, having fun part, researching stuff, playing with it.
1. random digits array 7x7 ( like in our case , no 0's only 1 to 9) how many prime numbers will produce compared with our case, more or less 281?
2. the concept is based on adjacent combinations and not all possible combinations that can be to many for excel to handle, so different order of same digits will have close or very different outcomes regarding prime numbers?

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1Fun Task 1Fun Task 2
2Only 9 digits, no dups, how order will affect prime numbers
3=MAP(SEQUENCE(30),LAMBDA(x,PRMC(RANDARRAY(7,7,1,9,1),1)))
497=PRMC(W5:W13)=PRMC(AB5:AB13)=PRMC(AG5:AG13)
586=AVERAGE(C4#)324232
68996.8232343
797951565
8120Average nr. Of primes produced by random digits( uniform distribution expected not to vary much)478757
972lot lower than the 281723937219
1090529753143
11110Hit F9 to compare random digits distrib frequencies to our initial array647373989
1273=FREQUENCY(F13:L19,SEQUENCE(9))89475798521
1310931373396157496897643
14789923333274923971987
15946977894124756813796521
16104761591938657492342125643
1765773421116536521987
1894994793921897389125643
1911933719998=PRMC(W20:W28)
2082192
211191473
22111065
23131rand digits 1 to 9 more leveled frequency wise27
24100=RANDARRAY(7,7,1,9,1)=FREQUENCY(F25#,SEQUENCE(9))513
2512458522845431
2610914131328867
2710463322868379
2810598872635183
29989463384497
3082851628574831
3170143726625483
32858254831
33872625483
3407625483
3597625483
36
37
Sheet6
Cell Formulas
RangeFormula
C3,O24,F24,Y19,O12,F5,AI4,AD4,Y4C3=FORMULATEXT(C4)
C4:C33C4=MAP(SEQUENCE(30),LAMBDA(x,PRMC(RANDARRAY(7,7,1,9,1),1)))
Y5:Y16,Y20:Y35,AI5:AI18,AD5:AD18Y5=PRMC(W5:W13)
F6F6=AVERAGE(C4#)
O13:O22O13=FREQUENCY(F13:L19,SEQUENCE(9))
F25:L31F25=RANDARRAY(7,7,1,9,1)
O25:O34O25=FREQUENCY(F25#,SEQUENCE(9))
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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