Last week, we had this challenge. Here is a variation. List the odd numbers reading rows from left to right, right to left and columns from top to bottom and bottom to top.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 550
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Odd Numbers All Directions with Power Query
Power Query solution 1 for Odd Numbers All Directions, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
A = List.Transform,
Row = Table.ToRows(Source),
RowsRev = A(Row, each List.Reverse(_)),
Col = Table.ToColumns(Source),
ColRev = A(Col, each List.Reverse(_)),
Num = A(Row & RowsRev & Col & ColRev, each Number.From(Text.Combine(A(_, each Text.From(_))))),
Sol = Text.Combine(A(List.Select(Num, each Number.IsOdd(_)), Text.From), ", ")
in
SolPower Query solution 2 for Odd Numbers All Directions, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = List.Transform,
a = Table.ToRows(S) & Table.ToColumns(S),
b = a & T(a, List.Reverse),
c = T(b, each T(_, Text.From)),
d = T(c, Text.Combine),
e = T(d, Number.From),
f = List.Select(e, Number.IsOdd),
g = Text.Combine(T(f, Text.From), ", "),
Sol = Table.FromValue(g, [DefaultColumnName = "Answer"])
in
SolPower Query solution 3 for Odd Numbers All Directions, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform =
let
Rw = Table.ToRows(Source),
Cl = Table.ToColumns(Source),
L = List.Transform,
Rrw = L(Rw, each List.Reverse(_)),
Rcl = L(Cl, each List.Reverse(_)),
Lst = L(Rw & Cl & Rrw & Rcl, each Text.Combine(L(_, Text.From)))
in
Lst,
Result = Text.Combine(List.Select(Transform, (f) => Number.Mod(Number.From(f), 2) = 1), ", ")
in
ResultSolving the challenge of Odd Numbers All Directions with Excel
Excel solution 1 for Odd Numbers All Directions, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A18:E22,
f,
SORTBY(
SORTBY(
z,
-ROW(
z
)
),
-COLUMN(
z
)
),
n,
BYROW(
VSTACK(
z,
TRANSPOSE(
z
),
f,
TRANSPOSE(
f
)
),
CONCAT
),
ARRAYTOTEXT(
FILTER(
n,
ISODD(
n
)
)
)
)Excel solution 2 for Odd Numbers All Directions, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A23,
LAMBDA(a,
LET(b,
a:E23,
n,
COUNT(
TAKE(
b,
1
)
),
z,
TAKE(
b,
n,
n
),
f,
SORTBY(
SORTBY(
z,
-ROW(
z
)
),
-COLUMN(
z
)
),
x,
BYROW(
VSTACK(
z,
TRANSPOSE(
z
),
f,
TRANSPOSE(
f
)
),
CONCAT
),
IF(n*(n=XMATCH(
,
TAKE(
b,
,
1
)
)-1),
ARRAYTOTEXT(
FILTER(
x,
ISODD(
x
)
)
),
""))))Excel solution 3 for Odd Numbers All Directions, proposed by Rick Rothstein:
=LET(
a,
A18:E22,
t,
TRANSPOSE(
a
),
c,
COLUMNS(
a
),
ff,
LAMBDA(
x,
FILTER(
x,
MOD(
TAKE(
x,
,
-1
),
2
),
""
)
),
fr,
LAMBDA(
x,
SORTBY(
x,
SEQUENCE(
,
c,
c,
-1
)
)
),
v,
VSTACK(
ff(
a
),
ff(
t
),
ff(
fr(
a
)
),
ff(
fr(
t
)
)
),
ARRAYTOTEXT(
TOCOL(
BYROW(
v,
LAMBDA(
r,
CONCAT(
r
)
)
),
3
)
)
)Excel solution 4 for Odd Numbers All Directions, proposed by John V.:
=LET(r,A5:C7,s,-COLUMN(r),p,SORTBY,f,TRANSPOSE(r),b,BYROW(VSTACK(r,f,p(r,s),p(f,s)),CONCAT),ARRAYTOTEXT(FILTER(b,ISODD(b))))Excel solution 5 for Odd Numbers All Directions, proposed by Kris Jaganah:
=LET(
a,
A18:E22,
b,
ROWS(
a
),
c,
VSTACK(
BYROW(
a,
CONCAT
),
TOCOL(
BYCOL(
a,
CONCAT
)
)
),
d,
VSTACK(
c,
MAP(
c,
LAMBDA(
x,
CONCAT(
MID(
x,
SEQUENCE(
b,
,
b,
-1
),
1
)
)
)
)
),
ARRAYTOTEXT(
TOCOL(
d/ISODD(
-d
),
3
)
)
)Excel solution 6 for Odd Numbers All Directions, proposed by Julian Poeltl:
=LET(
A,
A5:C7,
D,
ROWS(
A
),
N,
TOROW(
VSTACK(
BYROW(
A,
CONCAT
),
BYCOL(
A,
CONCAT
),
MAP(
SEQUENCE(
D
),
LAMBDA(
B,
CONCAT(
INDEX(
A,
SEQUENCE(
D,
,
D,
-1
),
B
)
)
)
),
MAP(
SEQUENCE(
D
),
LAMBDA(
B,
CONCAT(
INDEX(
A,
B,
SEQUENCE(
D,
,
D,
-1
)
)
)
)
)
),
3
),
IFERROR(
TEXTJOIN(
", ",
,
FILTER(
N,
ISODD(
--N
)
)
),
""
)
)Excel solution 7 for Odd Numbers All Directions, proposed by Timothée BLIOT:
=LET(M,A2:B3,N,TEXTSPLIT(TEXTJOIN("|",,BYROW(M,LAMBDA(x, CONCAT(x) &"|"&CONCAT(SORTBY(x,COLUMNS(x)-SEQUENCE(,COLUMNS(x))+1)))),
BYCOL( M,LAMBDA(x, CONCAT(x)&"|"&CONCAT(SORTBY(x,ROWS(x)-SEQUENCE(ROWS(x))+1))))),,"|"),ARRAYTOTEXT(SORT(FILTER(N,ISODD(--N)))))Excel solution 8 for Odd Numbers All Directions, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(k, A18:E22, _reverse_row, LAMBDA(ar, INDEX(ar, 1, SEQUENCE(1, COLUMNS(ar),COLUMNS(ar), -1))), _reverse_col, LAMBDA(arr, INDEX(arr, SEQUENCE(ROWS(arr),,ROWS(arr), -1))), p, BYROW(k, LAMBDA(x, CONCAT(x))), q, TOCOL(BYCOL(k, LAMBDA(y, CONCAT(y)))), _p, BYROW(k, LAMBDA(x, CONCAT(_reverse_row(x)))), _q, TOCOL(BYCOL(k, LAMBDA(y, CONCAT(_reverse_col(y))))), r, VSTACK(p, q, _p, _q), s, FILTER(r, MOD(r, 2)=1), TEXTJOIN(", ",,s))Excel solution 9 for Odd Numbers All Directions, proposed by Hussein SATOUR:
=LET(n,A13:D16,f,LAMBDA(x,BYROW(x,CONCAT)),s,SEQUENCE(ROWS(n),,ROWS(n),-1),lr,f(n),tb,f(TRANSPOSE(n)),rl,f(CHOOSECOLS(n,s)),bt,f(TRANSPOSE(CHOOSEROWS(n,s))),r,VSTACK(lr,tb,rl,bt),ARRAYTOTEXT(FILTER(r,ISODD(r))))Excel solution 10 for Odd Numbers All Directions, proposed by Oscar Mendez Roca Farell:
=LET(m, A25:D28, r, ROWS(m), i, r-SEQUENCE(r)+1, n, INDEX(m, i, TOROW(i)), F, TRANSPOSE, b, BYROW(VSTACK(m, F(m), n, F(n)), CONCAT), ARRAYTOTEXT(FILTER(b, ISODD(b))))Excel solution 11 for Odd Numbers All Directions, proposed by Sunny Baggu:
=LET(
rng,
A5:C7,
_a,
BYROW(
rng,
LAMBDA(
a,
CONCAT(
a
)
)
),
l,
LAMBDA(
x,
MAP(
x,
LAMBDA(
b,
CONCAT(
MID(
b,
1 + LEN(
b
) - SEQUENCE(
LEN(
b
)
),
1
)
)
)
)
),
_c,
TOCOL(
BYCOL(
rng,
LAMBDA(
c,
CONCAT(
c
)
)
)
),
_v,
VSTACK(
_a,
l(
_a
),
_c,
l(
_c
)
),
ARRAYTOTEXT(
FILTER(
_v,
ISODD(
--_v
)
)
)
)Excel solution 12 for Odd Numbers All Directions, proposed by LEONARD OCHEA 🇷🇴:
=LET(r,A18:E22,f,ROWS(r),a,SEQUENCE(f),b,TOROW(a),c,f-b+1,I,INDEX,m,BYROW(VSTACK(I(r,a,b),I(r,b,a),I(r,a,c),I(r,c,a)),CONCAT),ARRAYTOTEXT(FILTER(m,ISODD(--m))))Excel solution 13 for Odd Numbers All Directions, proposed by Abdallah Ally:
=LET(a,A2:B3,f,LAMBDA(x,LET(s,SEQUENCE(LEN(x)),CONCAT( SORTBY(MID(x,s,1),-s)))),b,LET(r,BYROW(a,CONCAT),s,MAP(r,f),c,TOCOL( BYCOL(a,CONCAT)),d,MAP(c,f),VSTACK(r,s,c,d)),ARRAYTOTEXT(FILTER(b,ISODD(--b))))Excel solution 14 for Odd Numbers All Directions, proposed by Anshu Bantra:
=LAMBDA(data_,
LET(
rows_, ROWS(data_),
cols_, ROWS(data_),
seq_, SEQUENCE(rows_, cols_, rows_*cols_, -1),
rev_data_, INDEX(TEXTSPLIT(TEXTJOIN(",",,data_),,","), seq_),
row_lr_, BYROW(data_, LAMBDA(x, TEXTJOIN("",,x))),
col_tb_, BYCOL(data_, LAMBDA(x, TEXTJOIN("",,x))),
row_rl_, BYROW(rev_data_, LAMBDA(x, TEXTJOIN("",,x))),
col_bt_, BYCOL(rev_data_, LAMBDA(x, TEXTJOIN("",,x))),
all_, VSTACK(row_lr_,TRANSPOSE(col_tb_),row_rl_,TRANSPOSE(col_bt_)),
TEXTJOIN(", ",,FILTER(all_, ISODD(all_)))
))(A18:E22)Excel solution 15 for Odd Numbers All Directions, proposed by Hamidi Hamid:
=LET(
x,
A18:E22,
tx,
TRANSPOSE(
x
),
tr,
CHOOSECOLS(
A18:E22,
SEQUENCE(
,
5,
5,
-1
)
),
w,
TRANSPOSE(
CHOOSEROWS(
A18:E22,
SEQUENCE(
,
5,
5,
-1
)
)
),
u,
IF(
MOD(
TAKE(
x,
,
-1
),
2
)=1,
x,
""
),
d,
IF(
MOD(
TAKE(
tx,
,
-1
),
2
)=1,
tx,
""
),
t,
IF(
MOD(
TAKE(
tr,
,
-1
),
2
)=1,
tr,
""
),
q,
BYROW(
w,
LAMBDA(
a,
IF(
MOD(
TAKE(
a,
,
-1
),
2
)=1,
CONCAT(
a
),
""
)
)
),
s,
BYROW(
VSTACK(
u,
d,
t
),
LAMBDA(
a,
CONCAT(
a
)
)
),
TEXTJOIN(
", ",
1,
VSTACK(
s,
q
)
)
)Excel solution 16 for Odd Numbers All Directions, proposed by ferhat CK:
=LET(a,A5:C7,b,--BYROW(a,CONCAT),c,MAP(b,LAMBDA(x,--CONCAT(MID(x,SEQUENCE(LEN(x),,LEN(x),-1),1)))),
d,TOCOL(--BYCOL(a,CONCAT)),e,TOCOL(MAP(d,LAMBDA(x,--CONCAT(MID(x,SEQUENCE(LEN(x),,LEN(x),-1),1))))),f,VSTACK(b,c,d,e),TEXTJOIN(", ",1,IF(ISODD(f),f,"")))Excel solution 17 for Odd Numbers All Directions, proposed by Meganathan Elumalai:
=LET(
R,
A18:E22,
br,
BYROW(
R,
CONCAT
),
bc,
BYCOL(
R,
CONCAT
),
rev,
LAMBDA(
rr,
SCAN(
"",
rr,
LAMBDA(
x,
y,
CONCAT(
MID(
y,
LEN(
y
)-SEQUENCE(
LEN(
y
),
,
0
),
1
)
)
)
)
),
all,
VSTACK(
br,
TOCOL(
bc
),
rev(
br
),
TOCOL(
rev(
bc
)
)
),
ARRAYTOTEXT(
UNIQUE(
FILTER(
all,
MOD(
all,
2
)
)
)
)
)Excel soluti&on 18 for Odd Numbers All Directions, proposed by Bilal Mahmoud kh.:
=LET(
in,
A29:C31,
TEXTJOIN(
",",
TRUE,
BYROW(
VSTACK(
in,
TRANSPOSE(
in
)
),
LAMBDA(
x,
LET(
num,
CONCAT(
x
),
rev,
CONCAT(
MID(
num,
SEQUENCE(
LEN(
num
),
,
LEN(
num
),
-1
),
1
)
),
TEXTJOIN(
",",
TRUE,
IF(
ISODD(
num
),
num,
""
),
IF(
ISODD(
rev
),
rev,
""
)
)
)
)
)
)
)Excel solution 19 for Odd Numbers All Directions, proposed by Imam Hambali:
=LET(
l,
LAMBDA(
x,
CHOOSECOLS(
x,
SEQUENCE(
,
COLUMNS(
x
),
COLUMNS(
x
),
-1
)
)
),
ltr,
A18:E22,
u,
VSTACK(
ltr,
l(
ltr
),
TRANSPOSE(
ltr
),
l(
TRANSPOSE(
ltr
)
)
),
f,
BYROW(
u,
LAMBDA(
a,
CONCAT(
a
)*1
)
),
ARRAYTOTEXT(
FILTER(
f,
ISODD(
f
)
)
)
)Excel solution 20 for Odd Numbers All Directions, proposed by Eddy Wijaya:
=LET(d,A18:E22,
i_d,DROP(SORT(HSTACK(SEQUENCE(ROWS(d)),d),,-1),,1),
b_i_d,DROP(SORT(VSTACK(SEQUENCE(,ROWS(d)),d),,-1,TRUE),1),
f,LAMBDA(n,VSTACK(BYROW(n,LAMBDA(r,CONCAT(r))),BYCOL(n,LAMBDA(c,CONCAT(c))))),
res,--UNIQUE(TOCOL(VSTACK(f(d),f(i_d),f(b_i_d)),2)),
ARRAYTOTEXT(FILTER(res,ISODD(res))))Excel solution 21 for Odd Numbers All Directions, proposed by Mihai Radu O:
=LET(
a,
A13:D16,
rc,
ROWS(
a
),
Reverse,
LAMBDA(
x,
INDEX(
x,
SEQUENCE(
rc
),
SEQUENCE(
,
rc,
rc,
-1
)
)
),
b,
BYROW(
VSTACK(
a,
TRANSPOSE(
a
),
Reverse(
a
),
Reverse(
TRANSPOSE(
a
)
)
),
CONCAT
),
ARRAYTOTEXT(
FILTER(
b,
ISODD(
RIGHT(
b
)
)
)
)
)Excel solution 22 for Odd Numbers All Directions, proposed by Abdelrahman Omer, MBA, PMP:
=LET(
a,
A2:B3,
b,
BYROW(
a,
CONCAT
),
c,
TOCOL(
BYCOL(
a,
CONCAT
)
),
d,
MAP(
VSTACK(
b,
c
),
LAMBDA(
x,
CONCAT(
MID(
x,
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
)
)
),
f,
VSTACK(
b,
c,
d
),
ARRAYTOTEXT(
FILTER(
f,
ISNUMBER(
f/MOD(
f,
2
)
)
)
)
)Excel solution 23 for Odd Numbers All Directions, proposed by RIJESH T.:
=LET(array,A18:E22,
CT,COUNTA(A18:A22),
RL,BYROW(array,LAMBDA(a,CONCAT(a))),
DN,BYROW(TRANSPOSE(array),LAMBDA(a,CONCAT(a))),
LR,BYROW(SORTBY(array,SEQUENCE(,CT),-1),LAMBDA(a,CONCAT(a))),
UP,BYROW(SORTBY(TRANSPOSE(array),SEQUENCE(,CT),-1),LAMBDA(a,CONCAT(a))),
TOROW(FILTER(TOCOL(HSTACK(RL,DN,LR,UP)),ISODD(TOCOL(HSTACK(RL,DN,LR,UP)))))
)Excel solution 24 for Odd Numbers All Directions, proposed by Nonbow Wu:
=LET(a,A13:D16, b,TRANSPOSE(a),
Rv,LAMBDA(x,SORTBY(x,-COLUMN(a))),
t,VSTACK(a,Rv(a),b,Rv(b)),
ARRAYTOTEXT(TOCOL(BYROW(t,LAMBDA(r,IFS(ISODD(TAKE(r,,-1)),CONCAT(r)))),2)))Solving the challenge of Odd Numbers All Directions with Python
Python solution 1 for Odd Numbers All Directions, proposed by Konrad Gryczan, PhD:
I'll skip loading stage
import numpy as np
import pandas as pd
# Loading part (skipped)
def odd_numbers(matrix):
all = np.concatenate((matrix, np.transpose(matrix)), axis=0)
all_rev =np.fliplr(all)
all = np.concatenate((all, all_rev), axis=0)
all = np.array([int(''.join(map(str, x))) for x in all])
return ', '.join(map(str, all[all % 2 != 0]))
print(test1 == odd_numbers(input1)) # 54 is not odd
print(test2 == odd_numbers(input2)) # True
print(test3 == odd_numbers(input3)) # 11 vs 011
print(test4 == odd_numbers(input4)) # True
print(test5 == odd_numbers(input5)) # True
Solving the challenge of Odd Numbers All Directions with Python in Excel
Python in Excel solution 1 for Odd Numbers All Directions, proposed by Anshu Bantra:
def isodd(num):
return int(num)%2 == 1 if num != None else False
def textjoin(row, lst):
lst.append(''.join(row.apply(lambda x: str(x))))
lst.append(''.join(row[::-1].apply(lambda x: str(x))))
def dfjoin(df):
row, col = df.shape
lst = []
lst.extend( textjoin(df.iloc[_,:], lst) for _ in range(0, row, 1) )
lst.extend( textjoin(df.iloc[:,_], lst) for _ in range(0, col, 1) )
return lst
df = xl("A18:E22", headers=None)
', '.join([*(filter(isodd, dfjoin(df)))])
Python in Excel solution 2 for Odd Numbers All Directions, proposed by Ümit Barış Köse, MSc:
def is_odd(num):
return num is not None and int(num) % 2 == 1
def text_join(row):
return [''.join(row.astype(str)), ''.join(row[::-1].astype(str))]
def df_join(df):
rows = [text_join(df.iloc[i, :]) for i in range(df.shape[0])]
cols = [text_join(df.iloc[:, j]) for j in range(df.shape[1])]
return [''.join(item) for sublist in rows + cols for item in sublist]
df = xl("A13:D16", headers=None)
result = ', '.join(filter(is_odd, df_join(df)))
result
Solving the challenge of Odd Numbers All Directions with R
R solution 1 for Odd Numbers All Directions, proposed by Konrad Gryczan, PhD:
Sorry guys busy morning today.
pick_odds <- function(M) {
all <- as.numeric(apply(rbind(M, t(M), M[,nrow(M):1],t(M)[,nrow(t(M)):1]) , 1, paste0, collapse = ""))
paste(all[all %% 2 == 1], collapse = ", ")
}
all.equal(pick_odds(input1), test1) # 54 shouldn't be here
all.equal(pick_odds(input2), test2) # TRUE
all.equal(pick_odds(input3), test3) # only discrepancy is 011 vs 11
all.equal(pick_odds(input4), test4) # TRUE
all.equal(pick_odds(input5), test5) # TRUE
&&