Find the N digit maximum number in given 6×6 grid either rows (left to right) or columns (top to down). The start can be any place in the grid to find the number (Start won’t be always in first row or column).
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 611
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Maximum N-Digit Grid Number with Power Query
Power Query solution 1 for Find Maximum N-Digit Grid Number, proposed by Kris Jaganah:
let
A = Table.ToRows(
Table.TransformColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{},
each Text.From(_)
)
),
B = Table.FromRows(
List.Transform(
{1 .. 6},
(x) => {
x,
List.Max(
List.TransformMany(
A & List.Zip(A),
(z) => List.Transform({0 .. 5}, each Number.From(Text.Combine(List.Range(z, _, x)))),
(v, w) => w
)
)
}
),
{"N", "Max Number"}
)
in
BPower Query solution 2 for Find Maximum N-Digit Grid Number, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
N = {1 .. 6},
Listas = List.Transform(
Table.ToColumns(Source) & Table.ToRows(Source),
each Text.Combine(List.Transform(_, Text.From))
),
Num = List.Combine(
List.Transform(
Listas,
each List.TransformMany({0 .. 5}, (x) => N, (x, y) => Text.Middle(_, x, y))
)
),
Num2 = List.Transform(N, each List.Select(Num, (x) => Text.Length(x) = _)),
Sol = Table.FromColumns(
{N, List.Transform(Num2, each List.Max(List.Transform(_, Number.From)))},
{"N", "Max Number"}
)
in
SolPower Query solution 3 for Find Maximum N-Digit Grid Number, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
N = {1 .. 6},
Listas = Table.ToColumns(Source) & Table.ToRows(Source),
Unos = List.Select(
List.RemoveLastN(
let
Num = {1 .. Number.Power(2, Table.ColumnCount(Source))},
Base = 2,
Start = {},
G = (f, g, h) =>
let
a = Number.IntegerDivide(f, g),
b = Number.Mod(f, g),
c = h,
d = if f = 0 then (List.RemoveLastN(c & {a})) else @G(a, g, c & {b})
in
d,
Cal = List.Transform(Num, each G(_, Base, Start)),
Comb = List.Transform(Cal, each List.PositionOf(_, 1, 2)),
Sel = List.Transform(
Comb,
each
if List.Count(_) = 1 then
_
else if List.Max(List.Transform({1 .. List.Count(_) - 1}, (x) => _{x} - _{x - 1})) > 1 then
{null}
else
_
)
in
Sel
),
each List.Count(_) < 7
),
Comb = List.Combine(
List.Transform(
Listas,
each List.Transform(
Unos,
(x) =>
Number.From(Text.Combine(List.Transform(x, (y) => try Text.From(_{y}) otherwise null)))
)
)
),
Sol = Table.FromRows(
List.Transform(N, each {_, List.Max(List.Select(Comb, (x) => Text.Length(Text.From(x)) = _))}),
{"N", "Max Number"}
)
in
SolPower Query solution 4 for Find Maximum N-Digit Grid Number, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform1 = Table.TransformColumns(Source, {}, Text.From),
Rows = List.Transform(Table.ToRows(Transform1), Text.Combine),
Cols = List.Transform(Table.ToColumns(Transform1), Text.Combine),
Transform2 = List.TransformMany(
List.TransformMany(Rows & Cols, each {1 .. 6}, (x, y) => {x, y}),
each {0 .. 5},
(u, v) => [N = u{1}, Number = Number.From(Text.Middle(u{0}, v, u{1}))]
),
FromRec = Table.FromRecords(List.Distinct(Transform2)),
Result = Table.Group(FromRec, "N", {"Max Number", each List.Max([Number])})
in
ResultPower Query solution 5 for Find Maximum N-Digit Grid Number, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToRows(S) & Table.ToColumns(S),
Fx = (x) =>
let
b = List.Generate(
() => [i = 0, j = 1],
each [i] < List.Count(x),
each if [j] = List.Count(x) then [i = [i] + 1, j = [i] + 2] else [i = [i], j = [j] + 1],
each List.Range(x, [i], [j] - [i])
),
c = List.Transform(b, each Text.Combine(List.Transform(_, Text.From))),
d = List.Sort(List.Transform(c, Number.From))
in
d,
e = List.Transform(a, each Fx(_)),
f = List.Distinct(List.Sort(List.Combine(e))),
g = Table.FromColumns({List.Transform(f, Text.From)}, {"Max Number"}),
h = Table.Group(
g,
"Max Number",
{"G", each _},
1,
(x, y) => Value.Compare(Text.Length(x), Text.Length(y))
)[G],
i = List.Transform(h, each Table.TransformColumnTypes(_, {"Max Number", Int64.Type})),
j = Table.Combine(List.Transform(i, each Table.LastN(Table.Sort(_, {"Max Number", 0}), 1))),
Sol = Table.SelectColumns(Table.AddIndexColumn(j, "N", 1), {"N", "Max Number"})
in
SolPower Query solution 6 for Find Maximum N-Digit Grid Number, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Func = (x as list, y as number) =>
[
A = List.Accumulate({0 .. y - 1}, {}, (a, v) => a & {List.Skip(x, v)}),
T = List.Transform(
List.Zip(A),
each Number.From(Text.Combine(List.Transform(_, each Text.From(_))))
),
M = List.Max(T)
][M],
Lists = Table.ToColumns(Source) & Table.ToRows(Source),
Rows = List.Transform({1 .. 6}, each {_, List.Max(List.Transform(Lists, (x) => Func(x, _)))}),
ColNames = {"N", "Max Number"},
Res = Table.FromRows(Rows, ColNames)
in
ResPower Query solution 7 for Find Maximum N-Digit Grid Number, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform,
C = (x) =>
List.Zip(
B(
x(A),
each B(List.Generate(() => B(_, Text.From), each _ <> {}, each List.Skip(_)), Text.Combine)
)
),
D = B(List.Zip({C(Table.ToRows), C(Table.ToColumns)}), each List.Max(List.Combine(_))),
E = Table.FromColumns({{"1" .. Text.From(List.Count(D))}, List.Reverse(D)}, {"N", "Max Number"})
in
EPower Query solution 8 for Find Maximum N-Digit Grid Number, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlbSUTIBYiMgNgViMzA/VidayQDIMgRiA6gshAbJmEDVGUH1IvTATDCF6jUH6wPJGEPVm0P1WIBVINtjjGSapVJsLAA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Column1 = _t,
Column2 = _t,
Column3 = _t,
Column4 = _t,
Column5 = _t,
Column6 = _t
]
),
n = 6,
Result = List.Sort(
List.Accumulate(
{0 .. n - 1},
{},
(s, c) =>
s
& {
List.Max(
List.Transform(
Table.ToColumns(Source) & Table.ToRows(Source),
(x) => Number.From(Text.Combine(List.Range(x, c, n)))
)
)
}
)
)
in
ResultSolving the challenge of Find Maximum N-Digit Grid Number with Excel
Excel solution 1 for Find Maximum N-Digit Grid Number, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
A3:F8,
s,
SEQUENCE(
ROWS(
g
)
),
b,
BYROW(
VSTACK(
g,
TRANSPOSE(
g
)
),
CONCAT
),
HSTACK(
s,
MAP(
s,
LAMBDA(
i,
MAX(
--MID(
b,
TOROW(
s
),
i
)
)
)
)
)
)Excel solution 2 for Find Maximum N-Digit Grid Number, proposed by Rick Rothstein:
=LET(a,
A3:F8,
r,
BYROW(
VSTACK(
a,
TRANSPOSE(
a
)
),
CONCAT
),
MAP(H3:H8,
LAMBDA(x,
MAX(BYROW(r,
LAMBDA(n,
MAX(0+(0&MID(
n,
SEQUENCE(
99
),
x
)))))))))Excel solution 3 for Find Maximum N-Digit Grid Number, proposed by John V.:
=LET(s,ROW(1:6),HSTACK(s,MAP(s,LAMBDA(x,MAX(--MID(BYROW(VSTACK(A3:F8,TRANSPOSE(A3:F8)),CONCAT),TOROW(s),x))))))Excel solution 4 for Find Maximum N-Digit Grid Number, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
6
),
b,
LAMBDA(
v,
w,
v(
A3:F8,
LAMBDA(
x,
MAX(
--MID(
CONCAT(
x
),
a,
w
)
)
)
)
),
HSTACK(
a,
MAP(
a,
LAMBDA(
z,
MAX(
b(
BYCOL,
z
),
b(
BYROW,
z
)
)
)
)
)
)Excel solution 5 for Find Maximum N-Digit Grid Number, proposed by Julian Poeltl:
=LET(R,REDUCE(0,BYROW(VSTACK(A3:F8,TRANSPOSE(A3:F8)),CONCAT),LAMBDA(A,B,--VSTACK(A,TOCOL(MID(B,SEQUENCE(LEN(B)),SEQUENCE(,LEN(B))))))),XLOOKUP(--REPT(9,H3:H8),R,R,,-1))Excel solution 6 for Find Maximum N-Digit Grid Number, proposed by Julian Poeltl:
=LET(
R,
REDUCE(
0,
TOCOL(
VSTACK(
BYROW(
A3:F8,
CONCAT
),
BYCOL(
A3:F8,
CONCAT
)
),
3
),
LAMBDA(
A,
B,
--LET(
L,
LEN(
B
),
TOCOL(
VSTACK(
A,
--MID(
B,
SEQUENCE(
L
),
L
),
--MID(
B,
SEQUENCE(
L,
,
L,
-1
),
SEQUENCE(
,
L
)
)
),
3
)
)
)
),
XLOOKUP(
--REPT(
9,
H3:H8
),
R,
R,
,
-1
)
)Excel solution 7 for Find Maximum N-Digit Grid Number, proposed by Timothée BLIOT:
=MAP(H3:H8,LAMBDA(z,LET(A,A3:F8,F,LAMBDA(n,BYCOL(n,LAMBDA(x,TEXTJOIN("|",,MAP(SEQUENCE(7-z),LAMBDA(y,CONCAT(DROP(TAKE(x,y-1+z),MAX(0,y-1))))))))),MAX(--TEXTSPLIT(TEXTJOIN("|",,F(TRANSPOSE(A)),F(A)),"|")))))Excel solution 8 for Find Maximum N-Digit Grid Number, proposed by Oscar Mendez Roca Farell:
=LET(s, ROW(1:6), F, LAMBDA(i, j, -MAP(A3:F8,LAMBDA(a,-CONCAT(TAKE(a:F8,i,j))))), HSTACK(s, MAP(s, LAMBDA(a, MAX(VSTACK(F(1,a), F(a,1)))))))Excel solution 9 for Find Maximum N-Digit Grid Number, proposed by Duy Tùng:
=MAP(
H3:H8,
LAMBDA(
x,
MAX(
--MID(
BYCOL(
HSTACK(
A3:F8,
TRANSPOSE(
A3:F8
)
),
CONCAT
),
SEQUENCE(
6
),
x
)
)
)
)Excel solution 10 for Find Maximum N-Digit Grid Number, proposed by Sunny Baggu:
=LET(
_r,
A3:F8,
_a,
BYROW(
VSTACK(
_r,
TRANSPOSE(
_r
)
),
LAMBDA(
a,
CONCAT(
a
)
)
),
MAP(
SEQUENCE(
ROWS(
_r
)
),
LAMBDA(
b,
MAX(
MAP(
_a,
LAMBDA(
a,
MAX(
MID(
a,
SEQUENCE(
LEN(
a
)
),
b
) + 0
)
)
)
)
)
)
)Excel solution 11 for Find Maximum N-Digit Grid Number, proposed by Anshu Bantra:
=LET(
data_, A3:F8,
seq_, SEQUENCE(ROWS(data_)),
combi_, BYROW( VSTACK( data_, TRANSPOSE(data_)), CONCAT),
HSTACK(seq_, SORT(TRANSPOSE(BYCOL(--MID( combi_, TOROW( seq_), TAKE(seq_,-1) ), MAX))))
)Excel solution 12 for Find Maximum N-Digit Grid Number, proposed by Hamidi Hamid:
=LET(x,BYROW(RIGHT(BYCOL(A3:F8,CONCAT),SEQUENCE(6))*1,MAX),y,TRANSPOSE(BYCOL(RIGHT(BYROW(A3:F8,CONCAT),SEQUENCE(,6))*1,MAX)),BYROW(HSTACK(x,y),MAX))Excel solution 13 for Find Maximum N-Digit Grid Number, proposed by ferhat CK:
=MAP(H3:H8,LAMBDA(x,MAX(LET(a,VSTACK(BYROW(A3:F8,CONCAT),TOCOL(BYCOL(A3:F8,CONCAT))),MAP(a,LAMBDA(i,MAX(--MID(i,SEQUENCE(LEN(&i)),x))))))))Excel solution 14 for Find Maximum N-Digit Grid Number, proposed by Edwin Tisnado:
=LET(
a,
A3:F8,
s,
SEQUENCE(
6
),
MAP(
s,
LAMBDA(
v,
MAX(
MAP(
BYROW(
a,
CONCAT
),
LAMBDA(
x,
MAX(
--MID(
x,
s,
v
)
)
)
),
MAP(
BYCOL(
a,
CONCAT
),
LAMBDA(
x,
MAX(
--MID(
x,
s,
v
)
)
)
)
)
)
)
)Excel solution 15 for Find Maximum N-Digit Grid Number, proposed by Md Ismail Hosen:
=LAMBDA(
GridData,
Ns,
LET(
_NewVectorLists,
HSTACK(
GridData,
TRANSPOSE(
GridData
)
),
_ColIndexes,
SEQUENCE(
ROWS(
GridData
)*2
),
_MaxValueForN,
LAMBDA(
GridData,
N,
LET(
_ColIndexInPatterns,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
TAKE(
_NewVectorLists,
,
1
)
)-N+1
),
LAMBDA(
a,
c,
HSTACK(
a,
SEQUENCE(
N,
,
c
)
)
)
),
,
1
),
_Result,
MAX(
DROP(
REDUCE(
"",
_ColIndexes,
LAMBDA(
a,
c,
HSTACK(
a,
BYCOL(
_ColIndexInPatterns,
LAMBDA(
RowIndexes,
CONCAT(
INDEX(
_NewVectorLists,
RowIndexes,
c
)
)*1
)
)
)
)
),
,
1
)
),
_Result
)
),
_Result,
MAP(
Ns,
LAMBDA(
N,
_MaxValueForN(
GridData,
N
)
)
),
_Result
)
)(A3:F8,
H3:H8)Solving the challenge of Find Maximum N-Digit Grid Number with Python
Python solution 1 for Find Maximum N-Digit Grid Number, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
input_matrix = pd.read_excel(path, header=None, usecols="A:F", skiprows=2, nrows=6).values
test = pd.read_excel(path, usecols="H:I", skiprows=1, nrows=7)
def get_substrings(string, length):
return [string[i:i+length] for i in range(len(string) - length + 1)]
concatenated = [''.join(map(str, row)) for row in np.vstack((input_matrix, input_matrix.T))]
df = pd.DataFrame({'Substrings': [substring for element in concatenated for length in range(1, 7) for substring in get_substrings(element, length)]})
df['Length'] = df['Substrings'].apply(len)
df['Substrings'] = df['Substrings'].astype(np.int64)
max_numbers = df.groupby('Length')['Substrings'].max().reset_index()
print(max_numbers['Substrings'].equals(test['Max Number'])) # True
Solving the challenge of Find Maximum N-Digit Grid Number with Python in Excel
Python in Excel solution 1 for Find Maximum N-Digit Grid Number, proposed by Alejandro Campos:
grid = xl("A3:F8").values.tolist()
def find_max(grid, n):
max_number = "0"
for r in range(6):
for c in range(7 - n):
max_number = max(max_number, ''.join(map(str, grid[r][c:c + n])))
for c in range(6):
for r in range(7 - n):
max_number = max(max_number, ''.join(map(str, [grid[r + i][c] for i in range(n)])))
return int(max_number)
pd.DataFrame({'N': range(1, 7), 'Max Number': [find_max(grid, n) for n in range(1, 7)]})
Python in Excel solution 2 for Find Maximum N-Digit Grid Number, proposed by Anshu Bantra:
data = xl("Sheet1!A3:F8").values.tolist()
def find_max_number(data, num_digits):
max_number = "0"
num_rows, num_cols = len(data), len(data[0])
for row in range(num_rows):
for col in range(num_cols - num_digits + 1):
horizontal_sequence = ''.join(map(str, data[row][col:col + num_digits]))
max_number = max(max_number, horizontal_sequence)
if row <= num_rows - num_digits:
vertical_sequence = ''.join(map(str, [data[row + i][col] for i in range(num_digits)]))
max_number = max(max_number, vertical_sequence)
return int(max_number)
pd.DataFrame({
'N': range(1, 7),
'Max Number': [find_max_number(data, _) for _ in range(1, 7)]
})
Solving the challenge of Find Maximum N-Digit Grid Number with R
R solution 1 for Find Maximum N-Digit Grid Number, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A3:F8", col_names = FALSE) %>% as.matrix()
test = read_excel(path, range = "H2:I8")
get_substrings <- function(string, length) {
n <- nchar(string)
if (length > n) return(character(0))
map_chr(1:(n - length + 1), ~ str_sub(string, .x, .x + length - 1))
}
result = input %>% t() %>%
rbind(input) %>%
as_tibble() %>%
unite("con", 1:6, sep = "") %>%
mutate(s1 = map(con, ~ get_substrings(.x, 1)),
s2 = map(con, ~ get_substrings(.x, 2)),
s3 = map(con, ~ get_substrings(.x, 3)),
s4 = map(con, ~ get_substrings(.x, 4)),
s5 = map(con, ~ get_substrings(.x, 5)),
s6 = map(con, ~ get_substrings(.x, 6))) %>%
select(-con) %>%
pivot_longer(cols = everything(), names_to = "length", values_to = "substrings") %>%
unnest(substrings) %>%
summarise(max = max(as.numeric(substrings)), .by = length)
all.equal(result$max, test$`Max Number`)
#> [1] TRUE
&&