Find Row/Col Consecutive Numbers

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  p = List.Combine(Table.ToRows(Source)), 
  q = List.Combine(Table.ToColumns(Source)), 
  s = List.Sort(
    List.Distinct(
      List.RemoveNulls(
        List.Transform(
          {0 .. List.Count(p) - 1}, 
          each try
            if (p{_} - p{_ + 1}) = 0 then p{_} else if (q{_} - q{_ + 1}) = 0 then q{_} else null
          otherwise
            null
        )
      )
    )
  )
in
  s

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Lists = Table.ToRows(Source) & Table.ToColumns(Source), 
  Select = List.TransformMany(
    Lists, 
    (x) =>
      [
        RN = List.RemoveNulls(x), 
        RF = List.RemoveFirstN(RN, 1), 
        RL = List.RemoveLastN(RN, 1), 
        Z  = List.Zip({RF, RL})
      ][Z], 
    (x, y) => if List.IsDistinct(y) then null else y{0}
  ), 
  Return = List.Distinct(List.RemoveNulls(Select))
in
  Return

let
  Source = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  Cols = Table.ToColumns(Source), 
  Rows = Table.ToRows(Source), 
  Sol = List.Sort(
    List.Distinct(
      List.Combine(
        List.Transform(
          Cols & Rows, 
          each 
            let
              a = _, 
              b = List.Distinct(List.RemoveNulls(a)), 
              c = List.Select(b, each List.Count(List.PositionOf(a, _, 2)) > 1)
            in
              c
        )
      )
    )
  )
in
  Sol

let
  Fonte = Tabela1, 
  res = 
    let
      a = List.RemoveNulls(List.Combine(Table.ToRows(Fonte) & Table.ToColumns(Fonte))), 
      b = Table.FromColumns({a}), 
      c = Table.Group(b, {"Column1"}, {{"tab", each Table.RowCount(_)}}, 0), 
      d = List.Distinct(Table.SelectRows(c, each [tab] <> 1)[Column1])
    in
      List.Sort(d)
in
  res

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = List.RemoveNulls(List.Combine(Table.ToRows(S))), 
  b = List.RemoveNulls(List.Combine(Table.ToColumns(S))), 
  c = a & {null} & b, 
  d = List.Generate(
    () => [i = 0, j = 1], 
    each [i] < List.Count(c) - 1, 
    each [i = [i] + 1, j = [j] + 1], 
    each List.Range(c, [i], 2)
  ), 
  e = List.Sort(List.Distinct(List.Combine(List.Select(d, each _{0} = _{1})))), 
  Sol = Table.FromColumns({e}, {"Answer Expected"})
in
  Sol

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Col = Table.FromColumns({Table.ToColumns(Source)}, {"Col"}), 
  Row = Table.FromColumns({Table.ToRows(Source)}, {"Row"}), 
  MF = (List) =>
    let
      F1 = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
      F2 = Table.AddIndexColumn(F1, "I", 0, 1, Int64.Type), 
      F3 = Table.AddColumn(
        F2, 
        "T", 
        each if (try F2[Column1]{[I] - 1} otherwise null) = [Column1] then "T" else "F"
      ), 
      F4 = Table.SelectRows(F3, each ([T] = "T") and ([Column1] <> null)), 
      F5 = Table.SelectColumns(F4, {"Column1"}), 
      F6 = Table.Distinct(F5), 
      F7 = Table.RenameColumns(F6, {{"Column1", "Answer"}})
    in
      F7, 
  RA = Table.AddColumn(Row, "Answer", each MF([Row])), 
  CA = Table.AddColumn(Col, "Answer", each MF([Col])), 
  A = Table.Combine({RA, CA}, {"Answer"}), 
  B = Table.ExpandTableColumn(A, "Answer", {"Answer"}, {"Answer"}), 
  C = Table.SelectRows(B, each ([Answer] <> null)), 
  D = Table.Distinct(C), 
  E = Table.Sort(D, {{"Answer", Order.Ascending}})
in
  E

let
  Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content], 
  from = Table.TransformColumnTypes(
    Source, 
    {
      {"Column1", Int64.Type}, 
      {"Column2", Int64.Type}, 
      {"Column3", Int64.Type}, 
      {"Column4", Int64.Type}, 
      {"Column5", Int64.Type}, 
      {"Column6", Int64.Type}, 
      {"Column7", Int64.Type}, 
      {"Column8", Int64.Type}, 
      {"Column9", Int64.Type}, 
      {"Column10", Int64.Type}, 
      {"Column11", Int64.Type}, 
      {"Column12", Int64.Type}
    }
  ), 
  f = (x) =>
    [
      a = List.Combine({Table.ToColumns(x), Table.ToRows(x)}), 
      b = List.Select(
        List.Transform(a, (x) => List.Modes(List.RemoveNulls(x))), 
        (p) => List.Count(p) = 1
      ), 
      c = List.Sort(List.Distinct(List.Combine(b)))
    ][c], 
  to = f(from)
in
  to

let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  sol = [
    col = List.Combine(Table.ToColumns(Source)), 
    row = List.Combine(Table.ToRows(Source)), 
    a = List.RemoveNulls(List.Combine({col, row})), 
    b = List.RemoveNulls(
      List.Generate(
        () => [x = null, y = 0], 
        each [y] < List.Count(a), 
        each [y = [y] + 1, x = try if a{[y] + 1} <> a{[y]} then null else a{[y] + 1} otherwise null], 
        each [x]
      )
    ), 
    c = List.Sort(List.Distinct(b))
  ][c]
in
  sol

let
  Source = ..., 
  //Transform a table into a nested table with one column, containg each row or column values 
  TransformTableFN = (Tbl as table, Type as text) =>
    let
      Types = [row = Table.ToRows, column = Table.ToColumns], 
      ChosenFunction = Record.Field(Types, Text.Lower(Type)), 
      TransformTable = ChosenFunction(Tbl), 
      TransformList = List.Transform(
        TransformTable, 
        each Table.FromColumns({List.RemoveItems(_, {""})}, {"Answer Expected"})
      )
    //get all rows and columns as a table with one column 
    inTable.Combine(TransformList), 
  Append = Table.Combine(List.Transform({"row", "column"}, each TransformTableFN(Source, _))), 
  //Lolcal GroupKind ensures, that only consecutive values are counted together 
  LocalGrouping = Table.Group(
    Append, 
    {"Answer Expected"}, 
    {{"Count", each Table.RowCount(_), Int64.Type}}, 
    GroupKind.Local
  ), 
  Filter = Table.SelectRows(LocalGrouping, each [Count] > 1)[[Answer Expected]], 
  RemoveDuplicates = Table.Distinct(Filter), 
  //Sort Values as numbers 
  Sort = Table.Sort(RemoveDuplicates, {{each Number.From([Answer Expected]), Order.Ascending}})
in
  Sort

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  fx = (lts) =>
    List.Transform(
      lts, 
      (x) =>
        List.Select(
          List.Combine(
            List.Select(
              List.Zip(
                {
                  x, 
                  List.Transform(
                    {0 .. List.Count(x) - 1}, 
                    each try x{_} = x{_ + 1} and x{_} <> null otherwise false
                  )
                }
              ), 
              each _{1}
            )
          ), 
          each _ is number
        )
    ), 
  reslut = List.Sort(
    List.Distinct(List.Combine(fx(Table.ToRows(Source)) & fx(Table.ToColumns(Source))))
  )
in
  reslut

=LET(z,B2:M10,SORT(UNIQUE(TOCOL(IFS(IF(z<"",(DROP(z,1)=z)+(DROP(z,,1)=z)),z),3))))

=LET(b,
    B2:N10,
    t,
    VSTACK(
        TOCOL(
            b
        ),
        TOCOL(
            b,
            ,
            1
        )
    ),
    n,
    (t=DROP(
        t,
        1
    ))*t,
    SORT(
        UNIQUE(
            TOCOL(
                IF(
                    n,
                    n,
                    1/0
                ),
                3
            )
        )
    ))

=LET(b,B2:N10,t,VSTACK(TOCOL(b),TOCOL(b,,1)),n,(t=DROP(t,1))*t,SORT(UNIQUE(TOCOL(n*(n/n),3))))

=LET(a,B2:L10,d,B2:M9,t,TOCOL(VSTACK(a*(a=DROP(a,,1)),d*(d=DROP(d,1))),3),SORT(UNIQUE(FILTER(t,t>0))))

=LET(i,
    B2:M10,
    UNIQUE(SORT(TOCOL(IFS(i*((i=C2:N10)+(i=B3:M11)),
    i),
    2))))

=LET(d,
    B2:M10,
    SORT(UNIQUE(TOCOL(
d/((d=DROP(
    d,
    1
))+(d=DROP(
    d,
    ,
    1
)))^0*d^0,
    2))))

=LET(a,B2:M10,SORT(UNIQUE(TOCOL(a/(((((OFFSET(a,0,1)-a)=0)*(a<>""))+(((OFFSET(a,1,0)-a)=0)*(a<>"")))>0),3))))

=LET(
    F,
    B2:M10,
    S,
    SEQUENCE(
        99
    ),
    BR,
    BYROW(
        F,
        LAMBDA(
            A,
            TEXTJOIN(
                ",",
                ,
                A
            )
        )
    ),
    R,
    LAMBDA(
        B,
        MAP(
            B,
            LAMBDA(
                A,
                FILTER(
                    S,
                    ISNUMBER(
                        SEARCH(
                            ","&S&","&S,
                            A
                        )
                    )
                )
            )
        )
    ),
    BC,
    BYCOL(
        F,
        LAMBDA(
            A,
            TEXTJOIN(
                ",",
                ,
                A
            )
        )
    ),
    SORT(
        UNIQUE(
            TOCOL(
                HSTACK(
                    R(
                        BR
                    ),
                    R(
                        BC
                    )
                ),
                3
            )
        )
    )
)

=LET(A,
    B2:M10,
    B,
    TOCOL(
        A
    ),
    C,
    TOCOL(
        A,
        ,
        1
    ),
    
F,
    LAMBDA(n,
    --(n=DROP(
        n,
        1
    ))*n),
    G,
    LAMBDA(
        m,
        TOCOL(
            m/m*m,
            3
        )
    ),
    
UNIQUE(
    SORT(
        VSTACK(
            G(
                F(
                    B
                )
            ),
            G(
                F(
                    C
                )
            )
        )
    )
))

=LET(r, SCAN(,TOCOL(B2:N10),LAMBDA(a, x, IF(a=x, x&" ",x))), s, IF(ISTEXT(r),--TRIM(r),""), t, SCAN(,TOCOL(B2:M11,,TRUE),LAMBDA(b, y, IF(b=y, y&" ",y))), u, IF(ISTEXT(t),--TRIM(t),""), w, VSTACK(s, u),SORT(UNIQUE(FILTER(w, ISNUMBER(w)))))

=LET(
 a, IF(B2:M10 = "", x, B2:M10),
 SORT(
 UNIQUE(
 VSTACK(
 TOCOL(IF(a - DROP(a, , 1) = 0, a, x), 3),
 TOCOL(IF(a - DROP(a, 1) = 0, a, x), 3)
 )
 )
 )
)

=LET(
 a, IF(B2:M10 = "", x, B2:M10),
 b, TOCOL(a, 3),
 _a, TOCOL(a, 3, 1),
 l, LAMBDA(k, TOCOL(IF(DROP(k, 1) - k = 0, k, x), 3)),
 c, l(b),
 d, l(_a),
 SORT(UNIQUE(VSTACK(c, d)))
)

=LET(a,
    UNIQUE(SORT(TOCOL(MAP(B2:M10,
    LAMBDA(x,
    IF((x=OFFSET(
        x,
        ,
        1
    ))+(x=OFFSET(
        x,
        1,
        
    )),
    x,
    "")))))),
    FILTER(
        a,
        a<>""
    ))

=LET(a,
    UNIQUE(SORT(TOCOL(MAP(B2:M10,
    LAMBDA(x,
    IF(( x=OFFSET(
        x,
        ,
        1
    ))+(x=OFFSET(
        x,
        1,
        
    )),
    x,
    0)))))),
    FILTER(
        a,
        a
    ))

=DROP(SORT(UNIQUE(VSTACK(TOCOL((B2:M10=C2:N10)*B2:M10,3),TOCOL((B2:M10=B3:M11)*B2:M10,3)))),1)

=LET(a,REDUCE(0,B2:M10,LAMBDA(x,y,VSTACK(x,IF(OR(OFFSET(y,1,0)=y,OFFSET(y,-1,0)=y,OFFSET(y,0,1)=y,OFFSET(y,0,-1)=y),y)))),SORT(UNIQUE(FILTER(a,(a>0)*ISNUMBER(a)))))

=LET(
    b,
    LAMBDA(
        x,
        LET(
            n,
            INDEX(
                x,
                SEQUENCE(
                    COUNTA(
                        x
                    )-1
                ),
                1
            )-INDEX(
                x,
                SEQUENCE(
                    COUNTA(
                        x
                    )-1,
                    ,
                    2
                ),
                1
            ),
            UNIQUE(
                FILTER(
                    x,
                    VSTACK(
                        CHOOSEROWS(
                            x,
                            1
                        ),
                        n
                    )=0
                )
            )
        )
    ),
    DROP(
        UNIQUE(
            VSTACK(
                b(
                    TOCOL(
                        B2:M10
                    )
                ),
                b(
                    TOCOL(
                        TRANSPOSE(
                        B2:M10
                    )
                    )
                )
            )
        ),
        1
    )
)

=LET(
a, VSTACK(TOCOL(B2:M10,3,FALSE),TOCOL(B2:M10,3,TRUE)),
DROP(SORT(UNIQUE(IF(a-VSTACK(0,a)=0,a,"")),1,1),-2)
)

=LET(
d,B2:M10,
_row,TOCOL(d,1),
_rowh,VSTACK(0,_row),
_compilerow,HSTACK(VSTACK(_row,0),_rowh),
_compareR,HSTACK(_compilerow,IF(TAKE(_compilerow,,1)=TAKE(_compilerow,,-1),"Take","")),
_takeRow,FILTER(_compareR,TAKE(_compareR,,-1)="Take"),
_column,TOCOL(d,1,TRUE),
_columnh,VSTACK(0,_column),
_compilecolumn,HSTACK(VSTACK(_column,0),_columnh),
_compareC,HSTACK(_compilecolumn,IF(TAKE(_compilecolumn,,1)=TAKE(_compilecolumn,,-1),"Take","")),
_takeCol,FILTER(_compareC,TAKE(_compareC,,-1)="Take"),
_mergeTakeRC,VSTACK(_takeRow,_takeCol),
SORT(UNIQUE(TAKE(_mergeTakeRC,,1)),,1))

=LET(
 source,
     B2:M10,
    
 get_consec,
     LAMBDA(_,
    
 LET(
 a,
     DROP(
         _,
          1
     ),
    
 b,
     DROP(
         _,
          -1
     ),
    
 c,
     (a = b) * b,
    
 FILTER(
     c,
      c <> 0
 )
 )
 ),
    
 consec,
     VSTACK(
         
          get_consec(
              TOCOL(
                  source
              )
          ),
         
          get_consec(
              TOCOL(
                  source,
                   ,
                   1
              )
          )
          
     ),
    
 SORT(
     UNIQUE(
         consec
     )
 )
)

=LET(a,
    A1:M10,
    b,
    TOCOL(
        VSTACK(
            a,
            TOROW(
                a,
                ,
                1
            )
        ),
        2
    ),
    c,
    DROP(
        b,
        1
    ),
    SORT(UNIQUE(TOCOL(IFS(c<>"",
    c/(c=DROP(
        b,
        -1
    ))),
    2))))

=LET(
    _plageFiltree;
    MAP(
        B2:M10;
        LAMBDA(
            l_cel;
            SI(
                ET(
                    ESTNUM(
                        l_cel
                    );
                    OU(
                        DECALER(
                            l_cel;
                            -1;
                            0
                        )=l_cel;
                        DECALER(
                            l_cel;
                            1;
                            0
                        )=l_cel;
                        DECALER(
                            l_cel;
                            0;
                            -1
                        )=l_cel;
                        DECALER(
                            l_cel;
                            0;
                            1
                        )=l_cel
                    )
                );
                l_cel;
                NA()
            )
        )
    );
    
    UNIQUE(
        DANSCOL(
            _plageFiltree;
            3
        )
    )
)

=DROP(SORT(UNIQUE(TOCOL(MAP(B2:M10,LAMBDA(x,x/OR(OFFSET(x,1,,1)=x,OFFSET(x,,1,1)=x))),3))),1)


=DROP(SORT(UNIQUE(TOCOL(MAKEARRAY(9,12,LAMBDA(r,c,LET(b,B2:N11,i,INDEX(b,r,c),i/OR(i=INDEX(b,r+1,c),i=INDEX(b,r,c+1))))),2))),1)

=LET(a,
    B2:N11,
    b,
    VSTACK(
        TOCOL(
            a,
            
        ),
        TOCOL(
            a,
            ,
            1
        )
    ),
    c,
    VSTACK(
        DROP(
            b,
            1
        ),
        0
    ),
    UNIQUE(SORT(FILTER(b,
    (b-c=0)*(c<>0)))))

=LET(
RowStack, TOCOL(B2:M10,1),
ColumnStack, TOCOL(B2:M10,1, 1),
hConseq,UNIQUE(RowStack * (RowStack = VSTACK(0, RowStack))),
vConseq,UNIQUE(ColumnStack * (ColumnStack = VSTACK(0, ColumnStack))),
UNIQUE(FILTER(TOCOL(VSTACK(hConseq, vConseq),2), TOCOL(VSTACK(hConseq, vConseq),2)>0)) )

import pandas as pd
import numpy as np
path = "501 Find Consecutives in Grid.xlsx"
input = pd.read_excel(path, header=None, skiprows=1, usecols="B:M")
test = pd.read_excel(path, usecols="O:O", nrows = 5)
matrix = input.to_numpy()
tmatrix = matrix.T
in_cols = np.unique(matrix[:, :-1][matrix[:, :-1] == matrix[:, 1:]])
in_rows = np.unique(tmatrix[:, :-1][tmatrix[:, :-1] == tmatrix[:, 1:]])
result = pd.DataFrame(np.sort(np.unique(np.concatenate((in_rows, in_cols))))).
 rename(columns={0: "Answer Expected"}).astype("int64")
print(result.equals(test))  # True
                    
                  

df = xl("B2:M10", headers=False)
# Perform data wrangling
df = df.replace(np.nan, "")
arr1 = df.values
arr2 = df.apply(lambda x: x.shift(1)).values
arr3 = df.apply(lambda x: x.shift(-1), axis=1).values
arr4 = ((arr1 == arr2) | (arr1 == arr3)) * arr1 
items = np.unique(arr4[(arr4 != '') & (arr4 != 0)])
items
                    
                  

library(tidyverse)
library(readxl)
path = "Excel/501 Find Consecutives in Grid.xlsx"
input = read_excel(path, range = "B2:M10", col_names = FALSE) 
test = read_excel(path, range = "O1:O6")
i1 = as.matrix(input)
i2 = t(i1)
find_repeats_in_rows <- function(matrix) {
 unique(unlist(apply(matrix, 1, function(row) {
 row[which(diff(row) == 0)]
 })))
}
result = union(find_repeats_in_rows(i1),
 find_repeats_in_rows(i2)) %>%
 sort() %>%
 as_tibble() %>%
 setNames("Answer Expected")
identical(result, test)
# [1] TRUE
                    
                  

library(readxl)
df <- read_excel("Excel/Excel_Challenge_501 - Find Consecutives in Grid.xlsx",
 range = cell_cols("B:M"),
 col_names = FALSE)
df |> 
 as.matrix() |> 
 as.vector() |> 
 rle() |> 
 ((x) x$values[x$lengths > 1])() |> 
 union(
 df |> 
 as.matrix() |> 
 t() |> 
 as.vector() |> 
 rle() |> 
 ((x) x$values[x$lengths > 1])()
 ) |> 
 sort()