Stack the diagonals read from left to right of a matrix in rows. Ex. 1 2 3 4 5 6 7 8 9 First diagonal : 1 Second diagonal : 4 2 Third diagonal : 7 5 3 Fourth diagonal : 8 6 Fifth diagonal : 9
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 421
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Stack Matrix Diagonals in Rows with Power Query
Power Query solution 1 for Stack Matrix Diagonals in Rows, proposed by Bo Rydobon 🇹🇭:
let
n = 5,
Ans = Table.FromRows(
List.Transform(
{1 .. n * 2 - 1},
(r) =>
List.Transform(
{1 .. n},
(c) =>
let
i = List.Min({r, n}) - c + 1,
j = List.Max({0, r - n}) + c
in
if i > 0 and j <= n then i * n + j - n else null
)
)
)
in
AnsPower Query solution 2 for Stack Matrix Diagonals in Rows, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToCol = List.Combine(Table.ToColumns(Source)),
Count = Table.RowCount(Source),
Array = List.Combine(List.Transform({1 .. Count}, each {_ .. _ + Count - 1})),
Table = Table.FromColumns({ToCol, Array}),
Group = Table.Group(
Table,
"Column2",
{"Group", each Table.FromColumns(List.Transform([Column1], (f) => {f}))}
),
Result = Table.Combine(Group[Group])
in
ResultSolving the challenge of Stack Matrix Diagonals in Rows with Excel
Excel solution 1 for Stack Matrix Diagonals in Rows, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A20:E24,
n,
ROWS(
z
),
MAKEARRAY(n*2-1,
n,
LAMBDA(r,
c,
LET(i,
MIN(
r,
n
)-c+1,
j,
MAX(
0,
r-n
)+c,
IF((i>0)*(j<=n),
INDEX(
z,
i,
j
),
"")))))Excel solution 2 for Stack Matrix Diagonals in Rows, proposed by John V.:
=LET(a,
A6:C8,
n,
ROWS(
a
),
MAKEARRAY(2*n-1,
n,
LAMBDA(r,
c,
IF((c>r)+(2*n-cExcel solution 3 for Stack Matrix Diagonals in Rows, proposed by محمد حلمي:
=LET(
v,
A20:E24,
DROP(
REDUCE(
0,
SEQUENCE(
ROWS(
v
)*2-1
),
LAMBDA(
a,
d,
LET(
e,
SEQUENCE(
d
),
IFNA(
VSTACK(
a,
TOROW(
INDEX(
v,
d+1-e,
e
),
2
)
),
""
)
)
)
),
1
)
)Excel solution 4 for Stack Matrix Diagonals in Rows, proposed by Kris Jaganah:
=LET(v,
5,
MAKEARRAY(v*2-1,
v,
LAMBDA(x,
y,
LET(a,
(x-1)*v+1,
IFS((x<=v)*(y=1),
a,
((x<=v)*(x-(y-1)))>0,
a-((v-1)*(y-1)),
(x>=v)*((y-(v-x))<=v),
a-(v*(x-v))+(x-v)-((y-1)*(v-1)),
1,
"")))))Excel solution 5 for Stack Matrix Diagonals in Rows, proposed by Kris Jaganah:
=LET(a,
2,
b,
SEQUENCE(
a,
a
),
c,
TOCOL(
VSTACK(
TAKE(
b,
,
1
),
DROP(
TAKE(
b,
-1
),
,
1
)
),
3
),
d,
XMATCH(
c,
c
),
e,
TAKE(
b,
1
),
HSTACK(c,
IFS((d<=a)*(d-e>0),
c-((a-1)*e),
(d>a)*((d-(a-e))Excel solution 6 for Stack Matrix Diagonals in Rows, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_rng,
A2:B3,
_frng,
TOCOL(
_rng,
,
1
),
_cnt,
ROWS(
_rng
),
_array,
TOCOL(
SEQUENCE(
_cnt
) + SEQUENCE(
1,
_cnt,
0
)
),
_f,
REDUCE(
"",
SEQUENCE(
_cnt * 2 - 1
),
LAMBDA(
a,
b,
VSTACK(
a,
TOROW(
FILTER(
_frng,
_array = b
)
)
)
)
),
_r,
IFNA(
DROP(
_f,
1
),
""
),
_r
)Excel solution 7 for Stack Matrix Diagonals in Rows, proposed by Timothée BLIOT:
=LET(
A,
A2:B3,
B,
ROWS(
A
),
IFNA(
REDUCE(
INDEX(
A,
1,
1
),
SEQUENCE(
B*2-2,
,
3
),
LAMBDA(
w,
v,
VSTACK(
w,
TOROW(
MAKEARRAY(
B,
B,
LAMBDA(
x,
y,
IF(
x+y=v,
INDEX(
A,
y,
x
),
1/0
)
)
),
3
)
)
)
),
""
)
)Excel solution 8 for Stack Matrix Diagonals in Rows, proposed by Sunny Baggu:
=LET(
rng,
A20:E24,
_r,
ROWS(
rng
),
_ct,
MAKEARRAY(
_r,
_r,
LAMBDA(
r,
c,
INDEX(
SEQUENCE(
,
r
),
c
)
)
),
_rt,
MAKEARRAY(
_r,
_r,
LAMBDA(
r,
c,
INDEX(
SEQUENCE(
,
r,
r,
-1
),
c
)
)
),
_cb,
MAKEARRAY(
_r - 1,
_r - 1,
LAMBDA(
r,
c,
INDEX(
TAKE(
SEQUENCE(
,
_r - r,
r + 1
),
,
_r - r
),
c
)
)
),
_rb,
MAKEARRAY(
_r - 1,
_r - 1,
LAMBDA(
r,
c,
INDEX(
TAKE(
SEQUENCE(
,
_r - 1,
_r,
-1
),
,
_r - r
),
c
)
)
),
IFERROR(
VSTACK(
INDEX(
rng,
_rt,
_ct
),
INDEX(
rng,
_rb,
_cb
)
),
""
)
)Excel solution 9 for Stack Matrix Diagonals in Rows, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,
A20:E24,
r,
ROWS(
i
),
s,
SEQUENCE(
r
),
t,
DROP(
s,
-1
),
l,
TOROW(
s
),
IFERROR(INDEX(i,
1/(1/(VSTACK(
s,
r*t^0
)-l+1)),
VSTACK(
s^0,
t+1
)+l-1),
""))Excel solution 10 for Stack Matrix Diagonals in Rows, proposed by Pieter de B.:
=LET(
n,
4,
REDUCE(
SCAN(
0,
SEQUENCE(
2*n-1
),
LAMBDA(
x,
y,
x+IF(
y>n,
n,
1
)
)
),
SEQUENCE(
n-1
),
LAMBDA(
x,
y,
IFNA(
HSTACK(
x,
VSTACK(
EXPAND(
"",
y,
,
""
),
DROP(
DROP(
TAKE(
x,
,
-1
),
y
),
-y
)+n-1
)
),
""
)
)
)
)Excel solution 11 for Stack Matrix Diagonals in Rows, proposed by Charles Roldan:
=LAMBDA(
m,
LET(
s,
TAKE(
m,
1
),
k,
TOROW(
s + TOCOL(
s
) - 1,
,
1
),
REDUCE(
,
UNIQUE(
k,
1
),
LAMBDA(
_,
j,
IFNA(
VSTACK(
_,
FILTER(
TOROW(
m,
,
1
),
k = j
)
),
""
)
)
)
)
)(A20:E24)Excel solution 12 for Stack Matrix Diagonals in Rows, proposed by Charles Roldan:
=LAMBDA(
n,
LET(
s,
SEQUENCE(
n
),
t,
TOCOL(
s+TOROW(
s
)-1
),
REDUCE(
,
UNIQUE(
t
),
LAMBDA(
a,
b,
IFNA(
VSTACK(
a,
TOROW(
FILTER(
TOCOL(
SEQUENCE(
n,
n
),
,
1
),
t=b
)
)
),
""
)
)
)
)
)(5)Excel solution 13 for Stack Matrix Diagonals in Rows, proposed by Sandeep Marwal:
=LET(
input,
A1:E5,
series,
ROWS(
input
)*2,
seqseries,
SEQUENCE(
series-1
),
output,
MAP(seqseries,
LAMBDA(a,
LET(
rng,
TAKE(input,
IF(a<=(series/2),
a,
a-series),
IF(a<=(series/2),
a,
a-series)),
rowno,
ROWS(
rng
),
list,
TOCOL(
TRANSPOSE(
rng
)
),
strg,
TEXTJOIN(
"/",
,
list
),
seq,
SEQUENCE(
rowno
),
result,
REDUCE(
"",
seq,
LAMBDA(
a,
b,
CONCAT(
a,
TEXTBEFORE(
TEXTAFTER(
strg,
"/",
rowno*b-b
),
"/"
)
)
)
),
IF(
rowno=1,
rng,
result
)
))),
output
)Excel solution 14 for Stack Matrix Diagonals in Rows, proposed by Burhan Cesur:
=IFNA(
DROP(
REDUCE(
"";
SEQUENCE(
COLUMNS(
A1:B2
)
);
LAMBDA(
s;
v;
LET(
a;
A1:B2;
b;
COLUMNS(
a
);
HSTACK(
s;
VSTACK(
EXPAND(
" ";
v;
;
" "
);
OFFSET(
a;
0;
v-1;
1+b-v;
1
);
IFERROR(
TOCOL(
OFFSET(
a;
b-v;
v;
1;
b-v
)
);
""
)
)
)
)
)
);
1;
1
);
""
)Excel solution 15 for Stack Matrix Diagonals in Rows, proposed by Burhan Cesur:
=LET(
a,
A20:E24,
u,
SEQUENCE(
ROWS(
a
)+COLUMNS(
a
)-1
),
g,
VSTACK(
SEQUENCE(
ROWS(
a
)
),
COLUMNS(
& a
)-SEQUENCE(
COLUMNS(
a
)-1
)
),
f,
HSTACK(
SEQUENCE(
,
ROWS(
a
)
),
COLUMNS(
a
)-SEQUENCE(
,
COLUMNS(
a
)-1
)
),
t,
MOD(
SEQUENCE(
ROWS(
g
),
,
0
),
ROWS(
a
)
)+1,
q,
REDUCE(
,
u,
LAMBDA(
x,
y,
LET(
s,
f,
d,
t,
c,
IF(
y>ROWS(
a
),
SEQUENCE(
,
INDEX(
s,
y
),
INDEX(
d,
y
)+1
),
SEQUENCE(
,
INDEX(
s,
y
)
)
),
r,
IF(
y>ROWS(
a
),
1+INDEX(
d,
y
)+INDEX(
s,
y
)-SEQUENCE(
,
INDEX(
s,
y
)
),
1+INDEX(
s,
y
)-SEQUENCE(
,
INDEX(
s,
y
)
)
),
IFNA(
VSTACK(
x,
INDEX(
a,
r,
c
)
),
""
)
)
)
),
q
)Excel solution 16 for Stack Matrix Diagonals in Rows, proposed by Luis Couto:
=LET(
m,
A20:E24,
f,
ROWS(
m
),
MAKEARRAY(
2*f-1,
f,
LAMBDA(
x,
y,
IF(
IF(
xx,
y>f-MOD(
x,
f
)
),
"",
IF(
x>f,
INDEX(
m,
f-y+1,
y+MOD(
x,
f
)
),
INDEX(
m,
x-y+1,
y
)
)
)
)
)
) Excel solution 17 for Stack Matrix Diagonals in Rows, proposed by Alexandra Popoff:
=1 as the baseline:
¤ When x=1 => y is equal to Y + n up to the mid point,
then +1 by step.
¤ For x>1 => value for (y,
1) minus x*(n-1).
2: z_Keep: build a "pyramid" of TRUE/FALSE to apply to z_Arr to convert value outside of the pyramid to zero.
3: Output: convert 0 to null
Fx_Triangle = LAMBDA(z_In,
LET(
z_row,
(z_In-1)*2+1,
z_Arr,
MAKEARRAY(z_row,
z_In,
LAMBDA(z_y,
z_x,
1 + z_In * MIN(
z_y - 1,
z_In - 1
) + N(
z_y > z_In
) * (z_y - z_In) - (z_x - 1) * (z_In - 1)
)),
z_Keep,
MAKEARRAY(z_row,
z_In,
LAMBDA(z_y,
z_x,
LET(Inv_X,
(z_In + 1 - z_x),
AND((z_In - (Inv_X - 1)) <= z_y,
z_y <= (z_In + (Inv_X - 1))))
)),
z_out,
z_Arr * z_Keep,
IF(
z_out = 0,
"",
z_out
)
))Excel solution 18 for Stack Matrix Diagonals in Rows, proposed by Talia Cao, CPA:
=LET(
a,
A12:D15,
n,
ROWS(
a
),
m,
n * 2 - 1,
MAKEARRAY(
m,
n,
LAMBDA(
r,
c,
IF(
OR(
r < c,
r > m - c + 1
),
"",
INDEX(
a,
MIN(
r,
n
) - c + 1,
MAX(
0,
r - n
) + c
)
)
)
)
)Solving the challenge of Stack Matrix Diagonals in Rows with Python
Python solution 1 for Stack Matrix Diagonals in Rows, proposed by Konrad Gryczan, PhD:
import math
import numpy as np
import pandas as pd
def extract_antidiagonals(matrix_size):
dim = int(math.sqrt(matrix_size))
M = np.arange(1, matrix_size+1).reshape(dim, dim)
diags = [M[::-1,:].diagonal(i) for i in range(-M.shape[0]+1,M.shape[1])]
for diag in diags:
print(diag)
extract_antidiagonals(4)
print("n")
extract_antidiagonals(9)
print("n")
extract_antidiagonals(16)
print("n")
extract_antidiagonals(25)
Solving the challenge of Stack Matrix Diagonals in Rows with R
R solution 1 for Stack Matrix Diagonals in Rows, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
test1 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G2:H4", col_names = F)
test2 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G6:I10", col_names = F)
test3 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G12:J18", col_names = F)
test4 = read_excel("Excel/421 Stack Diagonals.xlsx", range = "G20:K28", col_names = F)
extract_antidiagonals = function(matrix_size) {
dim = sqrt(matrix_size)
M = matrix(1:matrix_size, nrow=dim, ncol=dim)
d = row(M)+col(M)
x = split(M, d)
x = lapply(x, rev) %>%
lapply(function(x) c(x, rep(NA, nrow(M) - length(x))) )
N = matrix(nrow = length(x), ncol = ncol(M)) %>%
as.data.frame()
for (i in 1:length(x)) {
N[i,] = x[[i]]
}
return(N)
}
extract_antidiagonals(4) == test1
extract_antidiagonals(9) == test2
extract_antidiagonals(16) == test3
extract_antidiagonals(25) == test4
&&