Generate the given grid
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 385
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Render Provided Grid Design with Power Query
Power Query solution 1 for Render Provided Grid Design, proposed by John V.:
let
b = {0..9}, M = Number.Mod, N = Number.From,
r = List.TransformMany(b, each b, (r, c) => M(5 * (N(r > 4) + N(c > 4)) + M(r + c, 5), 10))
in
Table.FromRows(List.Split(r, 10))
Blessings!
Power Query solution 2 for Render Provided Grid Design, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
A = {0 .. 4},
B = {5 .. 9},
C = List.Transform(A, each List.Skip(A, _) & List.FirstN(A, _)),
D = List.Transform(A, each List.Skip(B, _) & List.FirstN(B, _)),
Sol = Table.FromRows(List.Transform(A, each C{_} & D{_}) & List.Transform(A, each D{_} & C{_}))
in
SolPower Query solution 3 for Render Provided Grid Design, proposed by Ramiro Ayala Chávez:
let
a = {0 .. 4},
b = List.Generate(
() => [i = 0, j = 0],
each [j] < List.Count(a),
each [i = [i] + 1, j = [j] + 1],
each List.Range(a, [i], 5) & a
),
c = List.Transform(b, each List.FirstN(_, 5)),
d = List.Transform(c, each List.Transform(_, each _ + 5)),
e = Table.FromColumns(List.Split(List.Combine(c & d), 5)),
Sol = e & Table.FromColumns(List.Split(List.Combine(d & c), 5))
in
SolSolving the challenge of Render Provided Grid Design with Excel
Excel solution 1 for Render Provided Grid Design, proposed by Bo Rydobon 🇹🇭:
=MAKEARRAY(
11,
11,
LAMBDA(
r,
c,
IF(
r+c=2,
"",
MOD(
r+c-4+OR(
r=1,
c=1
),
5
)+5*XOR(
r>6,
c>6
)
)
)
)Excel solution 2 for Render Provided Grid Design, proposed by Rick Rothstein:
=LET(
a,
TAKE(
MOD(
+SEQUENCE(
5,
6,
0
),
5
),
,
5
),
VSTACK(
HSTACK(
a,
a+5
),
HSTACK(
a+5,
a
)
)
)
With the border numbers...
=LET(
a,
TAKE(
MOD(
+SEQUENCE(
5,
6,
0
),
5
),
,
5
),
b,
VSTACK(
HSTACK(
a,
a+5
),
HSTACK(
a+5,
a
)
),
HSTACK(
TAKE(
VSTACK(
" ",
b
),
,
1
),
VSTACK(
TAKE(
b,
1
),
b
)
)
)Excel solution 3 for Render Provided Grid Design, proposed by Rick Rothstein:
=MAKEARRAY(10,
10,
LAMBDA(r,
c,
MOD(MOD(
r+c-2,
5
)+5*((r>5)+(c>5)),
10)))Excel solution 4 for Render Provided Grid Design, proposed by John V.:
=LET(r,
ROW(
1:10
),
c,
TOROW(
r
),
MOD(5*((r>5)+(c>5))+MOD(
r+c-2,
5
),
10))
With borders:
✅ =LET(r,
ROW(
1:11
),
c,
TOROW(
r
),
IF(r+c=2,
"",
MOD(5*((r>6)+(c>6))+MOD(r+c-2-(r>1)-(c>1),
5),
10)))Excel solution 5 for Render Provided Grid Design, proposed by محمد حلمي:
=MAKEARRAY(11,
11,
LAMBDA(r,
c,
LET(x,
r+c,
i,
MOD(
x-4,
5
),
IFS(x=2,
"",
c*r<12,
x-3,
(r<7)*(c<7)+(r>6)*(c>6),
i,
1,
i+5))))Excel solution 6 for Render Provided Grid Design, proposed by محمد حلمي:
=LET(
s,
SEQUENCE(
5
),
e,
TOROW(
s
),
i,
MOD(
e+s+3,
5
),
v,
SEQUENCE(
10
)-1,
VSTACK(
HSTACK(
"",
TOROW(
v
)
),
HSTACK(
v,
VSTACK(
i,
5+i
),
VSTACK(
i+5,
i
)
)
)
)Excel solution 7 for Render Provided Grid Design, proposed by Kris Jaganah:
=LET(
a,
MAKEARRAY(
5,
10,
LAMBDA(
x,
y,
x+y-2
)
),
b,
TAKE(
a,
,
5
),
c,
DROP(
a,
,
5
),
d,
IF(
b>4,
b-5,
b
),
e,
IF(
c>9,
c-5,
c
),
f,
VSTACK(
HSTACK(
d,
e
),
HSTACK(
e,
d
)
),
g,
TAKE(
f,
1
),
HSTACK(
VSTACK(
"",
TOCOL(
g
)
),
VSTACK(
g,
f
)
)
)Excel solution 8 for Render Provided Grid Design, proposed by Timothée BLIOT:
=LET(
A,
MOD(
SEQUENCE(
5
)+SEQUENCE(
5,
5
)-2,
5
),
HSTACK(
VSTACK(
"",
SEQUENCE(
10
)-1
),
VSTACK(
SEQUENCE(
,
10
)-1,
HSTACK(
A,
A+5
),
HSTACK(
A+5,
A
)
)
)
)Excel solution 9 for Render Provided Grid Design, proposed by Sunny Baggu:
=LET(
n,
10,
_c,
SEQUENCE(
n / 2,
,
0
),
_r,
TOROW(
_c
),
_v,
_c + _r,
_t,
IF(
_v >= n / 2,
_v - n / 2,
_v
),
VSTACK(
HSTACK(
"",
SEQUENCE(
,
n,
0
)
),
HSTACK(
SEQUENCE(
n,
,
0
),
VSTACK(
_t,
_t + n / 2
),
VSTACK(
_t + n / 2,
_t
)
)
)
)Excel solution 10 for Render Provided Grid Design, proposed by LEONARD OCHEA 🇷🇴:
=MAKEARRAY(11,
11,
LAMBDA(a,
b,
LET(r,
MOD(
a+b+1,
5
),
IFS(a+b=2,
"",
a=1,
b-2,
b=1,
a-2,
(a>6)*(b<7)+(a<7)*(b>6),
r+5,
1,
r))))Excel solution 11 for Render Provided Grid Design, proposed by Charles Roldan:
=LET(
h,
LAMBDA(
a,
a + TRANSPOSE(
a
)
),
g,
LAMBDA(
a,
b,
TRANSPOSE(
VSTACK(
a,
a + b
)
)
),
LAMBDA(
n,
MOD(
g(
g(
MOD(
h(
SEQUENCE(
n
) - 1
),
n
),
n
),
n
),
2 * n
)
)
)(5)Excel solution 12 for Render Provided Grid Design, proposed by Giorgi Goderdzishvili:
=LET(
_sq,
SEQUENCE(
,
5
),
_sq9,
SEQUENCE(
10,
,
0
),
_fr,
MID(
SCAN(
"40123",
SEQUENCE(
5
),
LAMBDA(
f,
s,
CONCAT(
INDEX(
MID(
f,
_sq,
1
),
1,
{2,
3,
4,
5,
1}
)
)
)
),
_sq,
1
)*1,
_fn,
HSTACK(
VSTACK(
"",
_sq9
),
VSTACK(
TRANSPOSE(
_sq9
),
VSTACK(
HSTACK(
_fr,
_fr+5
),
HSTACK(
_fr+5,
_fr
)
)
)
),
_fn
)Excel solution 13 for Render Provided Grid Design, proposed by Tyler Cameron:
=LET(
x,
MAKEARRAY(
5,
5,
LAMBDA(
r,
c,
LET(
a,
r-1,
b,
c-1,
IF(
a+b<5,
a+b,
a+b-5
)
)
)
),
y,
MAKEARRAY(
5,
5,
LAMBDA(
r,
c,
LET(
a,
r+4,
b,
c-1,
IF(
a+b<10,
a+b,
a+b-5
)
)
)
),
VSTACK(
HSTACK(
x,
y
),
HSTACK(
y,
x
)
)
)Excel solution 14 for Render Provided Grid Design, proposed by Surendra Reddy:
=LET(
a,
5,
b,
SEQUENCE(
a,
,
0
)+SEQUENCE(
,
a,
0
),
d,
SEQUENCE(
a,
,
a
)+SEQUENCE(
,
a,
a
),
e,
IF(
b>=a,
b-a,
b
),
f,
VSTACK(
HSTACK(
e,
e+a
),
HSTACK(
e+a,
e
)
),
VSTACK(
HSTACK(
"",
SEQUENCE(
,
a+a,
0
)
),
HSTACK(
SEQUENCE(
a+a,
,
0
),
f
)
)
)Excel solution 15 for Render Provided Grid Design, proposed by Luis Couto:
=LET(
m;
RESIDUO(
C2:G2+B3:B7;
5
);
APILARV(
APILARH(
m;
m+5
);
APILARH(
m+5;
m
)
)
)
=LET(
m;
REDUCE(
C2:G2;
SECUENCIA(
4;
);
LAMBDA(
a;
i;
APILARV(
a;
ORDENARPOR(
TOMAR(
a;
-1
);
{1 };
)
)
)
);
APILARV(
APILARH(
m;
m+5
);
APILARH(
m+5;
m
)
)
)Solving the challenge of Render Provided Grid Design with R
R solution 1 for Render Provided Grid Design, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
as.matrix() %>%
{attr(., "dimnames") <- NULL; .}
generate = function(n){
grid_df <- expand.grid(i = 1:n, j = 1:n) %>%
mutate(value = (i + j - 2) %% n) %>%
pull(value)
matrix(grid_df, nrow = n, ncol = n)
}
a = generate(5)
b = a + 5
c = cbind(a,b)
d = cbind(b,a)
result = rbind(c,d) %>% {attr(., "dimnames") <- NULL; .}
&&&