Generate a 20×20 matrix as given through a formula.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 483
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate 20×20 Number Matrix with Power Query
Power Query solution 1 for Generate 20×20 Number Matrix, proposed by Aditya Kumar Darak 🇮🇳:
let
List = {{0 .. 4}, {5 .. 9}},
Generate = List.Transform(
{1 .. 4},
each [
Q = Number.IntegerDivide(_ - 1, 2),
IE = Number.IsEven(Q),
T = List.Transform(
{1 .. 4},
(f) =>
[
q = Number.IntegerDivide(f - 1, 2),
m = Number.Mod(f, 2),
tf = if IE then q <> m else q = m,
i = Number.Mod(_ + f - 1, 2),
l = List{i},
g = if tf then List.Reverse(l) else l,
r = List.Repeat({g}, 5),
z = List.Zip(r)
][z]
),
C = List.Combine(T),
R = Table.FromColumns(C)
][R]
),
Return = Table.Combine(Generate)
in
ReturnPower Query solution 2 for Generate 20×20 Number Matrix, proposed by Alexis Olson:
let
b = {0 .. 4},
d = List.Reverse(b),
p = {5 .. 9},
q = List.Reverse(p),
Result = Table.FromRows(
List.Combine(
List.Transform(
{q & b & p & d, d & p & b & q, p & d & q & b, b & q & d & p},
each List.Repeat({_}, 4)
)
),
{"a" .. "t"}
)
in
ResultPower Query solution 3 for Generate 20×20 Number Matrix, proposed by Ahmed Ariem:
let
L1 = List.Buffer({0 .. 4}),
L2 = List.Buffer({5 .. 9}),
L3 = List.Buffer(List.Reverse({0 .. 4})),
L4 = List.Buffer(List.Reverse({5 .. 9})),
lst1 = [t1 = L4 & L1, t2 = L2 & L3, t3 = {t1 & t2 & {0}}][t3],
lst2 = [t1 = L3 & L2, t2 = L1 & L4, t3 = {t1 & t2 & {1}}][t3],
lst3 = [t1 = L2 & L3, t2 = L4 & L1, t3 = {t1 & t2 & {2}}][t3],
lst4 = [t1 = L1 & L4, t2 = L3 & L2, t3 = {t1 & t2 & {3}}][t3],
tbl = Table.Repeat(Table.FromList(lst1 & lst2 & lst3 & lst4, (x) => x), 5),
Source = Table.RemoveColumns(
Table.Sort(tbl, {List.Last(Table.ColumnNames(tbl)), Order.Ascending}),
(List.Last(Table.ColumnNames(tbl)))
)
in
SourcePower Query solution 4 for Generate 20×20 Number Matrix, proposed by Ahmed Ariem:
let
L1 = List.Buffer({0 .. 4}),
L2 = List.Buffer({5 .. 9}),
L3 = List.Buffer(List.Reverse({0 .. 4})),
L4 = List.Buffer(List.Reverse({5 .. 9})),
lst1 = [t1 = L4 & L1, t2 = L2 & L3, t3 = {t1 & t2}][t3],
lst2 = [t1 = L3 & L2, t2 = L1 & L4, t3 = {t1 & t2}][t3],
lst3 = [t1 = L2 & L3, t2 = L4 & L1, t3 = {t1 & t2}][t3],
lst4 = [t1 = L1 & L4, t2 = L3 & L2, t3 = {t1 & t2}][t3],
Source1 = Table.RemoveColumns(
Table.Sort(
Table.Repeat(
Table.FromList(
List.Transform(lst1, (x) => {0} & x)
& List.Transform(lst2, (x) => {1} & x)
& List.Transform(lst3, (x) => {2} & x)
& List.Transform(lst4, (x) => {3} & x),
(x) => x
),
5
),
{{"Column1", Order.Ascending}}
),
{"Column1"}
)
in
Source1Solving the challenge of Generate 20×20 Number Matrix with Excel
Excel solution 1 for Generate 20×20 Number Matrix, proposed by Bo Rydobon 🇹🇭:
=LET(s,SEQUENCE(,20),t,s-(ABS(s*2-21)>9)*(2*s+4),MOD(CHOOSE(TOCOL(s+4)/5,t+4,t-1,-t,-t-5),10))Excel solution 2 for Generate 20×20 Number Matrix, proposed by Rick Rothstein:
=LET(a,MOD(SEQUENCE(5,10,0),10),b,9-a,n,TAKE(b,,5),z,TAKE(a,,5),v,TAKE(a,,-5),f,TAKE(b,,-5),VSTACK(HSTACK(n,z,v,f),HSTACK(f,v,z,n),HSTACK(v,f,n,z),HSTACK(z,n,f,v)))Excel solution 3 for Generate 20×20 Number Matrix, proposed by John V.:
=LET(
s,
SEQUENCE,
b,
IF(
s(
5
),
s(
,
5
)-1
),
c,
5+b,
d,
4-b,
a,
5+d,
h,
HSTACK,
VSTACK(
h(
a,
b,
c,
d
),
h(
d,
c,
b,
a
),
h(
c,
d,
a,
b
),
h(
b,
a,
d,
c
)
)
)Excel solution 4 for Generate 20×20 Number Matrix, proposed by محمد حلمي:
=LET(i,SEQUENCE(,20,0),x,TAKE(i,,10),
e,IF(SEQUENCE(5),HSTACK(9-TAKE(x,,5),x,9-DROP(x,,5))),r,SORTBY(e,
HSTACK(11-x,21-x)),VSTACK(e,r,SORTBY(VSTACK(r,e),-i)))Excel solution 5 for Generate 20×20 Number Matrix, proposed by Konrad Gryczan, PhD:
= 0:4
b = 5:9
patterns = list(
c(
rev(
b
),
a,
b,
rev(
a
)
),
c(
rev(
a
),
b,
a,
rev(
b
)
),
c(
b,
rev(
a
),
rev(
b
),
a
),
c(
a,
rev(
b
),
rev(
a
),
b
)
)
final_matrix = patterns %>%
map(
~ matrix(
rep(
.x,
5
),
nrow = 5,
byrow = TRUE
)
) %>%
reduce(
rbind
) %>%
as.data.frame()
all.equal(
test,
final_matrix,
check.attributes = F
)Excel solution 6 for Generate 20×20 Number Matrix, proposed by Julian Poeltl:
=LET(
O,
SEQUENCE(
5,
,
9,
0
)+SEQUENCE(
,
5,
0,
-1
),
T,
SEQUENCE(
5,
,
0,
0
)+SEQUENCE(
,
5,
0
),
Th,
SEQUENCE(
5,
,
4,
0
)+SEQUENCE(
,
5,
0,
-1
),
F,
SEQUENCE(
5,
,
5,
0
)+SEQUENCE(
,
5,
0
),
FO,
VSTACK(
HSTACK(
O,
T
),
HSTACK(
Th,
F
)
),
FT,
VSTACK(
HSTACK(
F,
Th
),
HSTACK(
T,
O
)
),
VSTACK(
HSTACK(
FO,
FT
),
HSTACK(
FT,
FO
)
)
)Excel solution 7 for Generate 20×20 Number Matrix, proposed by Timothée BLIOT:
=LET(
A,
MOD(
SEQUENCE(
5,
5
)-1,
5
),
B,
MOD(
SEQUENCE(
5,
5,
4,
-1
),
5
),
C,
A+5,
D,
B+5,
H,
HSTACK,
VSTACK(
H(
D,
A,
C,
B
),
H(
B,
C,
A,
D
),
H(
C,
B,
D,
A
),
H(
A,
D,
B,
C
)
)
)Excel solution 8 for Generate 20×20 Number Matrix, proposed by Hussein SATOUR:
=LET(
x,
SEQUENCE(
,
5,
0
),
z,
SEQUENCE(
5
)*0,
b,
HSTACK(
VSTACK(
9-x+z,
4-x+z
),
VSTACK(
x+z,
x+z+5
)
),
c,
HSTACK(
VSTACK(
x+z+5,
x+z
),
VSTACK(
4-x+z,
9-x+z
)
),
HSTACK(
VSTACK(
b,
c
),
VSTACK(
c,
b
)
)
)Excel solution 9 for Generate 20×20 Number Matrix, proposed by Sunny Baggu:
=LET(
l,
LAMBDA(
x,
MAKEARRAY(
5,
5,
LAMBDA(
r,
c,
c + x
)
)
),
a,
ABS(
l(
-10
)
),
b,
l(
-1
),
c,
ABS(
l(
-5
)
),
d,
l(
4
),
e,
VSTACK(
HSTACK(
a,
b
),
HSTACK(
c,
d
)
),
f,
VSTACK(
HSTACK(
d,
c
),
HSTACK(
b,
a
)
),
VSTACK(
HSTACK(
e,
f
),
HSTACK(
f,
e
)
)
)Excel solution 10 for Generate 20×20 Number Matrix, proposed by Hamidi Hamid:
=LET(
w,
SEQUENCE(
5
),
nf,
SI(
w,
SEQUENCE(
,
5,
9,
-1
),
0
),
cq,
SI(
w,
SEQUENCE(
,
5,
5,
1
),
0
),
qt,
SI(
w,
SEQUENCE(
,
5,
4,
-1
),
0
),
zr,
SI(
w,
SEQUENCE(
,
5,
0,
1
),
0
),
ASSEMB.V(
ASSEMB.H(
nf,
zr,
cq,
qt
),
ASSEMB.H(
qt,
cq,
zr,
nf
),
ASSEMB.H(
cq,
qt,
nf,
zr
),
ASSEMB.H(
zr,
nf,
qt,
cq
)
)
)Excel solution 11 for Generate 20×20 Number Matrix, proposed by Pieter de Bruijn:
=LET(
s,
SEQUENCE(
,
5,
0
),
z,
SEQUENCE(
20
),
IF(
z<6,
HSTACK(
9-s,
s,
5+s,
4-s
),
IF(
z<11,
HSTACK(
4-s,
5+s,
s,
9-s
),
IF(
z<16,
HSTACK(
5+s,
4-s,
9-s,
s
),
HSTACK(
s,
9-s,
4-s,
5+s
)
)
)
)
)Excel solution 12 for Generate 20x20 Number Matrix, proposed by Sandeep Marwal:
=LET(
a,
A1:E1,
b,
F1:J1,
fn,
LAMBDA(
n,
CHOOSECOLS(
n,
SEQUENCE(
,
COLUMNS(
n
),
COLUMNS(
n
),
-1
)
)
),
fir,
HSTACK(
a,
b,
fn(
a
),
fn(
b
),
fn(
b
),
fn(
a
),
b,
a
),
sec,
HSTACK(
fir,
fn(
fir
)
),
out,
HSTACK(
{1;2;3;4},
WRAPROWS(
sec,
20
)
),
output,
DROP(
SORT(
VSTACK(
out,
out,
out,
out,
out
),
1
),
,
1
),
output
)Excel solution 13 for Generate 20x20 Number Matrix, proposed by Ricardo Alexis Domínguez Hernández:
=LET(
a,
SEQUENCE(
,
5,
9,
-1
),
b,
SEQUENCE(
,
5,
0,
1
),
c,
SEQUENCE(
,
5,
5,
1
),
d,
SEQUENCE(
,
5,
4,
-1
),
VSTACK(
IF(
SEQUENCE(
5
),
HSTACK(
a,
b,
c,
d
)
),
IF(
SEQUENCE(
5
),
HSTACK(
d,
c,
b,
a
)
),
IF(
SEQUENCE(
5
),
HSTACK(
c,
d,
a,
b
)
),
IF(
SEQUENCE(
5
),
HSTACK(
b,
a,
d,
c
)
)
)
)Solving the challenge of Generate 20x20 Number Matrix with Python
Python solution 1 for Generate 20x20 Number Matrix, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "483 Generate Matrix.xlsx"
test = pd.read_excel(path, usecols="A:T", skiprows=1, header=None)
seg1 = [5, 6, 7, 8, 9]
seg2 = [0, 1, 2, 3, 4]
seg3 = [9, 8, 7, 6, 5]
seg4 = [4, 3, 2, 1, 0]
pattern1 = seg3 + seg2 + seg1 + seg4
pattern2 = seg4 + seg1 + seg2 + seg3
pattern3 = seg1 + seg4 + seg3 + seg2
pattern4 = seg2 + seg3 + seg4 + seg1
block1 = np.tile(pattern1, (5, 1))
block2 = np.tile(pattern2, (5, 1))
block3 = np.tile(pattern3, (5, 1))
block4 = np.tile(pattern4, (5, 1))
final_matrix = np.concatenate((block1, block2, block3, block4), axis=0)
final_matrix = pd.DataFrame(final_matrix)
print(np.array_equal(test, final_matrix)) # True
Solving the challenge of Generate 20x20 Number Matrix with Python in Excel
Python in Excel solution 1 for Generate 20x20 Number Matrix, proposed by Owen Price:
Exploring NumPy operations with a Python in Excel challengeSolving the challenge of Generate 20x20 Number Matrix with R
R solution 1 for Generate 20x20 Number Matrix, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/483 Generate Matrix.xlsx"
test = read_excel(path, range = "A2:T21", col_names = F)
seg1 = 5:9
seg2 = 0:4
seg3 = rev(seg1)
seg4 = rev(seg2)
pattern1 <- c(seg3, seg2, seg1, seg4)
pattern2 <- c(seg4, seg1, seg2, seg3)
pattern3 <- c(seg1, seg4, seg3, seg2)
pattern4 <- c(seg2, seg3, seg4, seg1)
block1 <- matrix(rep(pattern1, 5), nrow = 5, byrow = TRUE)
block2 <- matrix(rep(pattern2, 5), nrow = 5, byrow = TRUE)
block3 <- matrix(rep(pattern3, 5), nrow = 5, byrow = TRUE)
block4 <- matrix(rep(pattern4, 5), nrow = 5, byrow = TRUE)
final_matrix <- rbind(block1, block2, block3, block4) %>% as.data.frame()
all.equal(test, final_matrix, check.attributes = F)
# [1] TRUE
&&