Home » Check Valid Word Square

Check Valid Word Square

Word Square – A word square is a n x n matrix where row (i) = column (i). Hence row 1 = column 1, row 2 = column 2 and so on. Populate Yes, if it is a valid Word Square. If invalid, populate No.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 658
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Check Valid Word Square with Power Query

Power Query solution 1 for Check Valid Word Square, proposed by Zoran Milokanović: 
let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]), 
  _      = {"No", "Yes"}{Byte.From(Source = List.Zip(Source))}
in
  _
Power Query solution 2 for Check Valid Word Square, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = if Table.ToColumns(Source) = Table.ToRows(Source) then "Yes" else "No"
in
  Result
Power Query solution 3 for Check Valid Word Square, proposed by Ramiro Ayala Chávez: 
let
  S   = Excel.CurrentWorkbook(){[Name = "Table7"]}[Content], 
  T   = Table.Transpose(S), 
  Sol = Table.FromValue(if S = T then "Yes" else "No", [DefaultColumnName = "Answer Expected"])
in
  Sol
Power Query solution 4 for Check Valid Word Square, proposed by Peter Krkos: 
let
  Pos = {0} & List.PositionOf(Source[Column1], null, Occurrence.All), 
  Zip = List.Zip({Pos, List.Skip(Pos) & {Table.RowCount(Source)}}), 
  TF = List.Transform(
    Zip, 
    (x) => [
      c1 = Table.SelectRows(Table.Range(Source, x{0}, x{1} - x{0}), each [Column1] <> null), 
      c2 = Table.RemoveColumns(
        c1, 
        Table.SelectRows(
          Table.TransformColumnTypes(
            Table.Profile(c1), 
            {{"Count", Int64.Type}, {"NullCount", Int64.Type}}
          ), 
          each [Count] = [NullCount]
        )[Column]
      ), 
      rows = Table.ToRows(c2), 
      cols = Table.ToColumns(c2), 
      c3 = List.AllTrue(List.Transform(List.Positions(rows), (x) => rows{x} = cols{x}))
    ]
  ), 
  Tbl = Table.Combine(
    List.Transform(
      List.Zip({List.Transform(TF, each Table.ToColumns([c1])), List.Transform(TF, each {{[c3]}})}), 
      each [
        a = Table.FromColumns(List.Combine(_)), 
        b = Table.RenameColumns(a, {{List.Last(Table.ColumnNames(a)), "Answer"}}), 
        c = Table.InsertRows(
          b, 
          Table.RowCount(b), 
          {
            Record.TransformFields(
              b{0}, 
              List.Transform(Table.ColumnNames(b), (x) => {x, each null})
            )
          }
        )
      ][c]
    )
  )
in
  Tbl
Power Query solution 5 for Check Valid Word Square, proposed by Peter Krkos: 
One Square check:
= List.ReplaceMatchingItems({Table.ToRows(Source) = Table.ToColumns(Source)}, {{true, "Yes"}, {false, "No"}}){0}

Solving the challenge of Check Valid Word Square with Excel

Excel solution 1 for Check Valid Word Square, proposed by Bo Rydobon 🇹🇭: 
=LET(x,B20:G25,IF(AND(TRANSPOSE(x)=x),"Yes","No"))


=LET(z,B1:I42,MAP(SEQUENCE(ROWS(z)),LAMBDA(i,IF(INDEX(z,i,1)=0,LET(n,COUNTA(INDEX(z,i+1,)),x,TAKE(DROP(z,i),n,n),IF(AND(TRANSPOSE(x)=x),"Yes","No")),""))))
Excel solution 2 for Check Valid Word Square, proposed by Rick Rothstein: 
=IF(AND(B20:G25=TRANSPOSE(B20:G25)),"Yes","No")
Excel solution 3 for Check Valid Word Square, proposed by Kris Jaganah: 
=IF(MIN(N(TRANSPOSE(B2:C3)=B2:C3)),"Yes","No")
Excel solution 4 for Check Valid Word Square, proposed by Julian Poeltl: 
=LET(S,B2:C3,IF(PRODUCT(--(S=TRANSPOSE(S))),"Yes","No"))
Excel solution 5 for Check Valid Word Square, proposed by Timothée BLIOT: 
=IF(PRODUCT(--(TRANSPOSE(B2:C3)=B2:C3)),"Yes","No")
Excel solution 6 for Check Valid Word Square, proposed by Hussein SATOUR: 
=IF(PRODUCT((TOCOL(M)=TOCOL(M,,1))*1),"Yes","No")
Excel solution 7 for Check Valid Word Square, proposed by Oscar Mendez Roca Farell: 
=MAP(
    B2:B42,
    LAMBDA(
        b,
        LET(
            n,
            ROW(
                b
            )-COUNTA(
                B2:b
            )+1,
            m,
             TAKE(
                 b:I42,
                 n,
                 n
             ),
            IF(
                COUNTA(
                    m
                )=n^2,
                IF(
                    AND(
                        m=TRANSPOSE(
                    m
                )
                    ),
                     "Yes",
                    "No"
                ),
                ""
            )
        )
    )
)
Excel solution 8 for Check Valid Word Square, proposed by Sunny Baggu: 
=LET(
 rng, B20:G25,
 IF(AND(TRANSPOSE(rng) = rng), "Yes", "No")
)
Excel solution 9 for Check Valid Word Square, proposed by Sunny Baggu: 
=LET(
 rng, B14:F18,
 IF(
 AND(TOCOL(rng) = TOCOL(rng, , 1)),
 "Yes",
 "No"
 )
)
Excel solution 10 for Check Valid Word Square, proposed by Sunny Baggu: 
=LET(
 rng, B20:G25,
 n, ROWS(rng),
 _a, SEQUENCE(n),
 _b, SEQUENCE(, n),
 IF(
 AND(INDEX(rng, _a, _b) = INDEX(rng, _b, _a)),
 "Yes",
 "No"
 )
)
Excel solution 11 for Check Valid Word Square, proposed by Anshu Bantra: 
=LAMBDA(data_,
 LET(
 row_, ROWS(data_),
 col_, COLUMNS(data_),
 rows_, BYROW(data_, CONCAT),
 cols_, TOCOL(BYCOL(data_, CONCAT)),
 IF(AND(row_ = col_, rows_ = cols_), "Yes", "No")
 )
)
Excel solution 12 for Check Valid Word Square, proposed by Md. Zohurul Islam: 
=LET(
a,IFNA(VSTACK("",B2:I42),0),
b,DROP(TAKE(a,,1),1),
c,VSTACK(1,SCAN(1,IF(b=0,1,0),SUM)),
w,DROP(REDUCE("",UNIQUE(c),LAMBDA(x,y,LET(
 p,DROP(FILTER(a,c=y),1),
 q,FILTER(p,TAKE(p,1)<>0),
 r,ABS(q=TRANSPOSE(q)),
 s,IF(SUM(r)=COUNT(r),"Yes","No"),
 t,MAP(SEQUENCE(ROWS(r)+1),LAMBDA(x,IF(x=1,s,""))),
 u,VSTACK(x,t),u))),1),
w)
Excel solution 13 for Check Valid Word Square, proposed by Pieter de B.: 
=LET(a,B35:I42,IF(AND(a=TRANSPOSE(a)),"Yes","No"))

Or:
=LET(start,B20,square,DROP(TAKE(B:I,XMATCH(1,(B:B="")*(ROW(B:B)>ROW()))),ROW()-1),IF(AND(TOCOL(square,1)=TOCOL(square,1,1)),"Yes","No"))
Excel solution 14 for Check Valid Word Square, proposed by ferhat CK: 
=IF(AND(MAP(SEQUENCE(ROWS(B9:E12)),LAMBDA(y,CONCAT(CHOOSECOLS(B9:E12,y))=CONCAT(CHOOSEROWS(B9:E12,y))))),"Yes","No")
Excel solution 15 for Check Valid Word Square, proposed by Seokho MOON: 
=IF(SUM(--(B35:I42<>TRANSPOSE(B35:I42)))=0, "Yes", "No")
Excel solution 16 for Check Valid Word Square, proposed by Erdit Qendro: 
=LET(
    areaT,
    B2:I42,
    cn,
    BYROW(
        areaT,
        COUNTA
    ),
    
    bl,
    HSTACK(
        TOROW(
            IFS(
                cn=0,
                ROW(
                    areaT
                )
            ),
            3
        ),
        ROWS(
                    areaT
                )
    ),
    
    xx,
    DROP(
        REDUCE(
            "",
            TAKE(
                areaT,
                ,
                1
            ),
            LAMBDA(
                a,
                v,
                
                VSTACK(
                    a,
                    IF(
                        OFFSET(
                            v,
                            -1,
                            0
                        )<>"",
                        "",
                        
                        LET(
                            rx,
                            ROW(
                                v
                            ),
                            ax,
                            XLOOKUP(
                                rx,
                                bl,
                                bl,
                                ,
                                1
                            )-rx,
                            
                            are,
                            OFFSET(
                                v,
                                ,
                                ,
                                ax,
                                ax
                            ),
                            
                            IF(
                                AND(
                                    TOCOL(
                                        are,
                                        3,
                                        0
                                    )=TOCOL(
                                        are,
                                        3,
                                        1
                                    )
                                ),
                                "Yes",
                                "No"
                            )
                            
                        )
                    )
                )
            )
        ),
        1
    ),
    xx
)
Excel solution 17 for Check Valid Word Square, proposed by Jeff Blakley: 
=IF(SUM(--(B2:C3<>TRANSPOSE(B2:C3)))=0, "Yes", "No")

Solving the challenge of Check Valid Word Square with Python

Python solution 1 for Check Valid Word Square, proposed by Luan Rodrigues: 
import pandas as pd
import numpy as np
df = pd.read_excel(file,usecols='B:d',skiprows=4,nrows=3)
df = np.where(np.all(df.T.values == df.values),'Yes','No')
print(df)

Solving the challenge of Check Valid Word Square with Python in Excel

Python in Excel solution 1 for Check Valid Word Square, proposed by Alejandro Campos: 
def is_valid_word_square(m):
 return "Yes" if all(m[i][j] == m[j][i] for i in range(
 len(m)) for j in range(len(m))) else "No"
matrix = xl("B9:E12")
is_valid_word_square(matrix)

&&&

Leave a Reply