Stack both the diagonals of n x n metrices horizontally.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 358
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Stack Matrix Diagonals Horizontally with Power Query
Power Query solution 1 for Stack Matrix Diagonals Horizontally, proposed by John V.:
let
S = Excel.CurrentWorkbook(){[Name="data"]}[Content],
T = Table.ToRows(S), n = Table.RowCount(S)
in
Table.FromRows(List.Transform({0..n - 1}, each {T{_}{_}, T{_}{n - _ - 1}}))
Blessings!
Power Query solution 2 for Stack Matrix Diagonals Horizontally, proposed by Aditya Kumar Darak 🇮🇳:
let
// table = A2:C4, header = false
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToRow = Table.ToRows(Source),
Rows = List.Count(ToRow),
Record = List.Transform(
{1 .. Rows},
each [N1 = _ - 1, N2 = Rows - _, D1 = ToRow{N1}{N1}, D2 = ToRow{N1}{N2}][[D1], [D2]]
),
Return = Table.FromRecords(Record)
in
ReturnPower Query solution 3 for Stack Matrix Diagonals Horizontally, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Fx = (f) =>
let
D1 = List.Transform({0 .. Table.ColumnCount(f) - 1}, each Table.ToRows(f){_}{_}),
D2 = List.Transform({0 .. Table.ColumnCount(f) - 1}, each List.Reverse(Table.ToRows(f){_}){_}),
a = Table.FromColumns({D1, D2}, {"D1", "D2"})
in
a,
Sol = Table.Combine(List.Transform({0}, each Fx(Excel.CurrentWorkbook(){_}[Content])))
in
SolPower Query solution 4 for Stack Matrix Diagonals Horizontally, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla3"]}[Content],
a = List.Combine(Table.ToRows(Origen)),
b = List.Alternate(a, Table.RowCount(Origen), 1, 1),
c = Table.FromColumns(List.Transform(Table.ToRows(Origen), each List.Reverse(_))),
d = List.Combine(Table.ToRows(c)),
e = List.Alternate(d, Table.RowCount(Origen), 1, 1),
Sol = Table.FromColumns({b, e}, {"D1", "D2"})
in
SolPower Query solution 5 for Stack Matrix Diagonals Horizontally, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
#"Kept First Rows" = Table.FirstN(
Source,
List.Last(
List.Generate(
() => [I = 0, F = 0],
each [F] = 0,
each [
I = [I] + 1,
F = Number.From(Source[Column1]{[I]} = null and Source[Column1]{[I] + 1} = null)
],
each [I]
)
)
),
#"Grouped Rows" = Table.Group(
#"Kept First Rows",
{"Column1"},
{
{
"StackedDiag",
each [
TableData = Table.SelectColumns(
Table.RemoveFirstN(_, 1),
List.Transform({0 .. Table.RowCount(_) - 2}, each Table.ColumnNames(Source){_})
),
TableColumns = Table.ToColumns(TableData),
RowCount = Table.RowCount(TableData),
RowList = {0 .. RowCount - 1},
CreateStack = Table.FromColumns(
{
List.Transform(RowList, (r) => TableColumns{r}{r}),
List.Transform(RowList, (r) => List.Reverse(TableColumns){r}{r})
}
)
][CreateStack],
type table
}
},
GroupKind.Local,
(x, y) => Number.From(y[Column1] is null)
),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type)[
[StackedDiag],
[Index]
],
#"Expanded StackedDiag" = Table.ExpandTableColumn(
#"Added Index",
"StackedDiag",
{"Column1", "Column2"},
{"StackedDiag.Column1", "StackedDiag.Column2"}
)
in
#"Expanded StackedDiag"Solving the challenge of Stack Matrix Diagonals Horizontally with Excel
Excel solution 1 for Stack Matrix Diagonals Horizontally, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:C4,
r,
ROW(
z
),
s,
r-@r+1,
INDEX(
z,
s,
HSTACK(
s,
-SORT(
-s
)
)
)
)Excel solution 2 for Stack Matrix Diagonals Horizontally, proposed by Rick Rothstein:
=LET(
n,
A11:E15,
a,
{0,
1},
r,
ROWS(
n
),
s,
SEQUENCE(
r
),
INDEX(
n,
s,
a*r-{-1,
1}*s+a
)
)Excel solution 3 for Stack Matrix Diagonals Horizontally, proposed by John V.:
=LET(
r,
A2:C4,
n,
ROWS(
r
),
d,
SEQUENCE(
n
),
INDEX(
r,
d,
HSTACK(
d,
1+n-d
)
)
)Excel solution 4 for Stack Matrix Diagonals Horizontally, proposed by محمد حلمي:
=LET(
d,
A11:E15,
i,
ROWS(
d
),
s,
SEQUENCE(
i
),
INDEX(
d,
s,
HSTACK(
s,
i+1-s
)
)
)
///
=LET(
v,
A1:F15,
j,
SCAN(
0,
TAKE(
v,
,
1
)="",
LAMBDA(
a,
d,
a+d
)
),
DROP(
REDUCE(
0,
UNIQUE(
j
),
LAMBDA(
a,
c,
LET(
e,
DROP(
FILTER(
v,
j=c
),
1
),
d,
FILTER(
e,
TAKE(
e,
1
)
),
i,
ROWS(
d
),
s,
SEQUENCE(
i
),
VSTACK(
a,
VSTACK(
{"",
""},
INDEX(
d,
s,
HSTACK(
s,
i+1-s
)
)
)
)
)
)
),
2
)
)Excel solution 5 for Stack Matrix Diagonals Horizontally, proposed by Kris Jaganah:
=LET(a,
A1:E15,
b,
BYROW(a,
LAMBDA(x,
SUM(--(x>0)))),
c,
MAP(SEQUENCE(
ROWS(
b
)
),
LAMBDA(x,
SUM(--(CHOOSEROWS(
b,
x
)=TAKE(
b,
x
))))),
d,
b-c+1,
e,
MAX(
b
),
f,
SEQUENCE(
,
e
),
g,
HSTACK(TOCOL(
IFS(
c=f,
a
),
3
),
MMULT(IF((d=f),
a,
0),
SEQUENCE(
e,
,
,
0
))),
IF(
g=0,
"",
g
))Excel solution 6 for Stack Matrix Diagonals Horizontally, proposed by Julian Poeltl:
=LET(
A,
A2:C4,
S,
SQRT(
COUNTA(
A
)
),
O,
INDEX(
A,
SEQUENCE(
S
),
SEQUENCE(
S
)
),
T,
INDEX(
A,
SEQUENCE(
S
),
SEQUENCE(
S,
,
S,
-1
)
),
HSTACK(
O,
T
)
)Excel solution 7 for Stack Matrix Diagonals Horizontally, proposed by Timothée BLIOT:
=LET(
A,
A2:C4,
B,
MUNIT(
ROWS(
A
)
),
F,
LAMBDA(
n,
m,
TOCOL(
IF(
n*m=0,
1/0,
n*m
),
3
)
),
HSTACK(
F(
A,
B
),
F(
A,
SORT(
B,
SEQUENCE(
SUM(
B
)
)
)
)
)
)Excel solution 8 for Stack Matrix Diagonals Horizontally, proposed by Oscar Mendez Roca Farell:
=LET(
_d,
A2:C4,
_n,
ROWS(
_d
),
_u,
MUNIT(
_n
),
_f,
LAMBDA(
i,
TOCOL(
_d/i,
2
)
),
HSTACK(
_f(
_u
),
_f(
CHOOSECOLS(
_u,
_n+1-SEQUENCE(
_n
)
)
)
)
)Excel solution 9 for Stack Matrix Diagonals Horizontally, proposed by Sunny Baggu:
=LET(
rng,
A11:E15,
_a,
SEQUENCE(
ROWS(
rng
)
),
_b,
SORT(
_a,
,
-1
),
HSTACK(
INDEX(
rng,
_a,
_a
),
INDEX(
rng,
_a,
_b
)
)
)Excel solution 10 for Stack Matrix Diagonals Horizontally, proposed by Sunny Baggu:
=LET(
rng,
A11:E15,
_r1,
SEQUENCE(
ROWS(
rng
)
),
_r2,
SORT(
_r1,
,
-1
),
_e1,
LAMBDA(
cond,
TOCOL(
IF(
cond,
rng,
1 / x
),
3
)
),
HSTACK(_e1(_r1 = TOROW(
_r1
)),
_e1(_r1 = TOROW(
_r2
)))
)Excel solution 11 for Stack Matrix Diagonals Horizontally, proposed by LEONARD OCHEA 🇷🇴:
=LET(m,
A2:C4,
n,
ROWS(
m
),
f,
SEQUENCE(
n
),
c,
TOROW(
f
),
HSTACK(TOCOL(m/(f=c),
3),
TOCOL(m/(f+c=n+1),
3)))
=LET(m,
A2:C4,
n,
ROWS(
m
),
s,
SEQUENCE(
n
),
INDEX(TOCOL(
m
),
HSTACK(s*(n+1)-n,
s*(n-1)+1)))Excel solution 12 for Stack Matrix Diagonals Horizontally, proposed by Abdallah Ally:
=LET(
a,
A2:C4,
b,
ROWS(
a
),
c,
SEQUENCE(
b
),
HSTACK(
INDEX(
a,
c,
c
),
INDEX(
a,
c,
SORT(
c,
,
-1
)
)
)
)Excel solution 13 for Stack Matrix Diagonals Horizontally, proposed by 🇵🇪 Ned Navarrete C.:
LET(
a,
A2:C4,
r,
ROWS(
a
),
s,
SEQUENCE(
,
r
),
u,
MUNIT(
r
),
b,
SORTBY(
a,
s,
-1
),
HSTACK(
TOCOL(
a/u,
3
),
TOCOL(
b/u,
3
)
)
)Excel solution 14 for Stack Matrix Diagonals Horizontally, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
a,
A2:C4,
b,
A6:D9,
c,
A11:E15,
f,
LAMBDA(
r,
LET(
i,
ROWS(
r
),
s,
SEQUENCE(
i
),
EXPAND(
INDEX(
r,
s,
HSTACK(
s,
i+1-s
)
),
i+1,
2,
""
)
)
),
DROP(
VSTACK(
f(
a
),
f(
b
),
f(
c
)
),
-1
)
)Excel solution 15 for Stack Matrix Diagonals Horizontally, proposed by Thang Van:
=LET(_r,
IFERROR(LET(
_a,
A2:E15,
b,
BYROW(_a,
LAMBDA(x,
SUM(--(x>0)))),
c,
HSTACK(
b,
_a
),
d,
FILTER(
UNIQUE(
b
),
UNIQUE(
b
)>0
),
REDUCE(
"",
FILTER(
d,
d<>0
),
LAMBDA(
_init,
_each,
VSTACK(
_init,
LET(
_a,
DROP(
TAKE(
FILTER(
c,
CHOOSECOLS(
c,
1
)=_each
),
_each,
_each+1
),
,
1
),
_len,
SEQUENCE(
COUNT(
CHOOSECOLS(
_a,
1
)
),
,
1
),
_decen_len,
SORT(
_len,
1,
-1
),
_r1,
REDUCE(
"",
_len,
LAMBDA(
_init,
_each,
VSTACK(
_init,
CHOOSECOLS(
CHOOSEROWS(
_a,
_each
),
_each
)
)
)
),
_r2,
REDUCE(
"",
_len,
LAMBDA(
_init,
_each,
VSTACK(
_init,
CHOOSECOLS(
CHOOSEROWS(
_a,
_each
),
& 1+MAX(
_decen_len
)-_each
)
)
)
),
HSTACK(
_r1,
_r2
)
),
1
)
)
)
),
"none"),
DROP(
FILTER(
_r,
CHOOSECOLS(
_r,
2
)<>"none"
),
1
))Excel solution 16 for Stack Matrix Diagonals Horizontally, proposed by Charles Roldan:
=LAMBDA(
Mat,
LET(
n,
SEQUENCE(
ROWS(
Mat
)
),
INDEX(
Mat,
n,
HSTACK(
n,
SORT(
n,
,
-1
)
)
)
)
)(A11:E15)Excel solution 17 for Stack Matrix Diagonals Horizontally, proposed by Ziad A.:
=LET(r,A1:C3,n,ROWS(r),m,MUNIT(n),INDEX({TOCOL(1/(m*r)^-1,2),TOCOL(1/(SORT(m,SEQUENCE(n),)*r)^-1,2)}))Excel solution 18 for Stack Matrix Diagonals Horizontally, proposed by Iván Cortinas Rodríguez:
=LET(
m,
A6:D9,
nr,
SEQUENCE(
ROWS(
m
)
),
HSTACK(
INDEX(
m,
nr,
nr
),
SORT(
MAP(
SEQUENCE(
ROWS(
m
),
1,
ROWS(
m
),
-1
),
nr,
LAMBDA(
f,
c,
INDEX(
m,
f,
c
)
)
),
,
1
)
)
)Excel solution 19 for Stack Matrix Diagonals Horizontally, proposed by Edwin Tisnado:
=LET(a,
A2:C4,
b,
MUNIT(
ROWS(
a
)
),
c,
1/(1/a*b),
d,
1/(1/SORTBY(
b,
-SEQUENCE(
,
COLUMNS(
b
)
)
))*a,
HSTACK(
TOCOL(
c,
2
),
TOCOL(
d,
2
)
))Excel solution 20 for Stack Matrix Diagonals Horizontally, proposed by Abdelrahman Omer, MBA, PMP:
=LET(a,
A2:C4,
HSTACK(TOCOL(
a/MUNIT(
ROWS(
a
)
),
2
),
TOCOL(a/MAKEARRAY(ROWS(
a
),
ROWS(
a
),
LAMBDA(r,
c,
--(r+c-1=ROWS(
a
)))),
2)))Excel solution 21 for Stack Matrix Diagonals Horizontally, proposed by Anup Kumar:
=LET(
m,
A11:E15,
n,
ROWS(
m
),
a,
SCAN(
1,
SEQUENCE(
n
),
LAMBDA(
x,
y,
INDEX(
m,
y,
y
)
)
),
b,
SCAN(
1,
SEQUENCE(
n
),
LAMBDA(
x,
y,
INDEX(
CHOOSECOLS(
m,
SEQUENCE(
n,
,
n,
-1
)
),
y,
y
)
)
),
HSTACK(
a,
b
)
)
Using XLOOKUP
=LET(
m,
A6:D9,
c,
ROWS(
m
),
r,
SEQUENCE(
c*c
),
d,
TOCOL(
m
),
HSTACK(
XLOOKUP(
SEQUENCE(
c,
,
1,
c+1
),
r,
d
),
XLOOKUP(
SEQUENCE(
c,
,
c,
c-1
),
r,
d
)
)
)Excel solution 22 for Stack Matrix Diagonals Horizontally, proposed by Rayan S.:
=LET(
arr,
A11:E15,
c,
COUNT(
A11:A15
),
x,
SEQUENCE(
c
),
y,
SEQUENCE(
c,
,
c,
-1
),
HSTACK(
INDEX(
arr,
x,
x
),
INDEX(
arr,
x,
y
)
)
)Excel solution 23 for Stack Matrix Diagonals Horizontally, proposed by Hazem Hassan:
=LET(
a,
A6:D9,
b,
SEQUENCE(
ROWS(
a
)
),
c,
TOROW(
b
),
HSTACK(
TOCOL(
IF(
b = c,
a,
1 / 0
),
3
),
TOCOL(
IF(
SORT(
b,
,
-1
) = c,
a,
1 / 0
),
3
)
)
)Excel solution 24 for Stack Matrix Diagonals Horizontally, proposed by Hazem Hassan:
=LET(
a,
A2:C4,
b,
ROWS(
a
),
c,
MUNIT(
b
),
HSTACK(
TOCOL(
IF(
c,
a,
1 / 0
),
3
),
TOCOL(
IF(
CHOOSECOLS(
c,
SORT(
SEQUENCE(
b
),
,
-1
)
),
a,
1 / 0
),
3
)
)
)Excel solution 25 for Stack Matrix Diagonals Horizontally, proposed by Gabriel Raigosa:
=LET(
d,
A2:C4,
s,
SEQUENCE(
ROWS(
d
)
),
INDEX(
d,
s,
HSTACK(
s,
-SORT(
-s
)
)
)
)
▶️ES:
=LET(
d,
A2:C4,
s,
SECUENCIA(
FILAS(
d
)
),
INDICE(
d,
s,
APILARH(
s,
-ORDENAR(
-s
)
)
)
)Excel solution 26 for Stack Matrix Diagonals Horizontally, proposed by Luis Couto:
=LET(
d;
A11:E15;
f;
FILAS(
d
);
u;
M.UNIDAD(
f
);
i;
--ARCHIVOMAKEARRAY(
f;
f;
LAMBDA(
x;
y;
x+y=f+1
)
);
APILARH(
ENCOL(
d/u;
3
);
ENCOL(
d/i;
3
)
)
)Solving the challenge of Stack Matrix Diagonals Horizontally with Python in Excel
Python in Excel solution 1 for Stack Matrix Diagonals Horizontally, proposed by John V.:
Hi everyone!
One [Python] option could be:
d = xl("A2:C4")
f = np.diag
np.array([f(d), f(np.fliplr(d))]).T
Blessings!
Solving the challenge of Stack Matrix Diagonals Horizontally with R
R solution 1 for Stack Matrix Diagonals Horizontally, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input_1 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "A2:C4", col_names = F) %>% as.matrix()
input_2 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "A6:D9", col_names = F) %>% as.matrix()
input_3 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "A11:E15", col_names = F) %>% as.matrix()
test_1 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "G2:H4", col_names = c("A", "B"))
test_2 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "G6:H9", col_names = c("A", "B"))
test_3 = read_excel("Excel/358 Stack Diagonal Values.xlsx",
range = "G11:H15", col_names = c("A", "B"))
get_diagonals = function(M) {
result = tibble(
A = diag(M),
B = diag(M[, ncol(M):1])
)
return(result)
}
identical(test_1, get_diagonals(input_1))
#> [1] TRUE
identical(test_2, get_diagonals(input_2))
#> [1] TRUE
identical(test_3, get_diagonals(input_3))
#> [1] TRUE
R solution 2 for Stack Matrix Diagonals Horizontally, proposed by Krzysztof Nowak:
Data1 <- c(95, 38, 17,87, 54, 10,57, 99, 18)
Data2 <- c(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)
Data3 <- c(3, 59, 32, 96, 63,33, 77, 21, 84, 47,55, 24, 13, 31, 9,14, 79, 47, 27, 22,91, 17, 23, 84, 24)
Matrix1 <- matrix(Data1, nrow = 3, byrow = TRUE)
Matrix2 <- matrix(Data2, nrow = 4, byrow = TRUE)
Matrix3 <- matrix(Data3, nrow = 5, byrow = TRUE)
Diagonals <- function(mat) {
n <- nrow(mat)
row_indices <- 1:n
col_indices <- n:1
Reversed <- diag(mat[row_indices, col_indices])
FirstDiagon <- diag(mat)
bind_cols(FirstDiagon,Reversed)
}
&&