Separate out odd and even numbers and give their min and max separated by dash.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 263
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Odd Even Min Max Split with Power Query
Power Query solution 1 for Odd Even Min Max Split, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
s = (t, f) =>
let
s = List.Select(List.Transform(Text.Split(t, ", "), Number.From), each Number.IsOdd(_) = f)
in
Text.Combine(List.Transform({List.Min(s), List.Max(s)}, Text.From), "-"),
Odd = Table.AddColumn(Source, "Odd Min Max", each s([Numbers], true)),
Even = Table.AddColumn(Odd, "Even Min Max", each s([Numbers], false))
in
EvenPower Query solution 2 for Odd Even Min Max Split, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Record = Table.AddColumn(
Source,
"R",
each [
S = Text.Split([Numbers], ", "),
N = List.Transform(S, (f) => [t = f, n = Number.From(f)]),
Sort = List.Sort(N, (f) => f[n]),
O = List.Select(Sort, (f) => Number.IsOdd(f[n])),
E = List.Select(Sort, (f) => Number.IsEven(f[n])),
R = [
Number = [Numbers],
Odd Min Max = List.First(O)[t]? & "-" & List.Last(O)[t]?,
Even Min Max = List.First(E)[t]? & "-" & List.Last(E)[t]?
]
][R]
),
Return = Table.FromRecords(Record[R])
in
ReturnPower Query solution 3 for Odd Even Min Max Split, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"Custom",
each [
a = Text.Split([Numbers], ", "),
b = List.Transform(a, Number.From),
c = {List.Select(b, each Number.IsOdd(_))} & {List.Select(b, each Number.IsEven(_))},
d = List.Transform(
List.Transform(c, each {Text.From(List.Min(_))} & {Text.From(List.Max(_))}),
(x) => Text.Combine(x, "-")
),
e = Table.FromRows({d}, {"Odd Min Max", "Even Min Max"})
][e]
)[Custom]
)
in
SolPower Query solution 4 for Odd Even Min Max Split, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc = Table.AddColumn(
Source,
"Custom",
each [
a = Text.Split([Numbers], ", "),
b = List.Transform(a, Number.From),
c = List.Select(b, each Number.IsEven(_)),
d = Text.Combine(List.Transform({List.Min(c)} & {List.Max(c)}, Text.From), "-"),
e = List.Select(b, each Number.IsOdd(_)),
f = Text.Combine(List.Transform({List.Min(e)} & {List.Max(e)}, Text.From), "-")
][[f], [d]]
)[[Custom]],
Sol = Table.ExpandRecordColumn(Calc, "Custom", {"f", "d"}, {"Odd Min Max", "Even Min Max"})
in
SolPower Query solution 5 for Odd Even Min Max Split, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"Personalizar",
each [
Odd = List.Combine(
List.Transform(
Text.Split([Numbers], ", "),
(x) => List.Select({Number.From(x)}, each Number.IsOdd(_))
)
),
#"Odd Min Max" = Text.Combine(List.Transform({List.Min(Odd), List.Max(Odd)}, Text.From), "-"),
Even = List.Combine(
List.Transform(
Text.Split([Numbers], ", "),
(x) => List.Select({Number.From(x)}, each Number.IsEven(_))
)
),
#"Even Min Max" = Text.Combine(
List.Transform({List.Min(Even), List.Max(Even)}, Text.From),
"-"
)
][[#"Odd Min Max"], [#"Even Min Max"]]
),
res = Table.ExpandRecordColumn(add, "Personalizar", Record.FieldNames(add[Personalizar]{0}))
in
resPower Query solution 6 for Odd Even Min Max Split, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddOdds = Table.AddColumn(
Source,
"OddMinMax",
each [
a = List.Transform(Text.Split([Numbers], ", "), each Number.From(_)),
odds = List.Select(a, each Number.IsOdd(_)),
evens = List.Select(a, each Number.IsEven(_)),
b = Text.From(List.Min(odds)) & "-" & Text.From(List.Max(odds)) ?? "",
c = Text.From(List.Min(evens)) & "-" & Text.From(List.Max(evens)) ?? "",
d = b & "%" & c
][d]
),
SplitCols = Table.SplitColumn(
AddOdds,
"OddMinMax",
Splitter.SplitTextByEachDelimiter({"%"}, QuoteStyle.Csv, false),
{"OddMinMax", "EvenMinMax"}
)
in
SplitColsPower Query solution 7 for Odd Even Min Max Split, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddOdds = Table.AddColumn(
Source,
"OddMinMax",
each [
a = Text.Split([Numbers], ", "),
b = List.Transform(a, each Number.From(_)),
c = List.Select(b, each Number.IsOdd(_)),
d = Text.From(List.Min(c)),
e = Text.From(List.Max(c)),
f = d & "-" & e
][f]
),
AddEvens = Table.AddColumn(
AddOdds,
"EvenMinMax",
each [
a = Text.Split([Numbers], ", "),
b = List.Transform(a, each Number.From(_)),
c = List.Select(b, each Number.IsEven(_)),
d = Text.From(List.Min(c)),
e = Text.From(List.Max(c)),
f = d & "-" & e
][f]
)
in
AddEvensPower Query solution 8 for Odd Even Min Max Split, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
C1 = Table.AddColumn(
Source,
"Odd Min Max",
each
let
a = Text.Split([Numbers], ", "),
c = List.Transform(a, each Number.From(_)),
e = List.Select(c, each Number.IsOdd(_)),
g = List.Min(e),
i = List.Max(e),
k = Text.Combine({Text.From(g), Text.From(i)}, "-")
in
k
),
C2 = Table.AddColumn(
C1,
"Even Min Max",
each
let
a = Text.Split([Numbers], ", "),
c = List.Transform(a, each Number.From(_)),
e = List.Select(c, each Number.IsEven(_)),
g = List.Min(e),
i = List.Max(e),
k = Text.Combine({Text.From(g), Text.From(i)}, "-")
in
k
)
in
C2Power Query solution 9 for Odd Even Min Max Split, proposed by Chris A.:
let
NumberList = Text.Split(numbers, delimiter),
Selected = List.Select(NumberList, each Number.Mod(Number.From(_),divisor)=remainder),
Return = try List.Min(Selected) & "-" & List.Max(Selected) otherwise null
in
Return
Added column, invoked custom function with divisor = 2, remainder=1 (for odd) and then divisor = 2 and remainder = 0 (for even)
Great challenge, ty!
Power Query solution 10 for Odd Even Min Max Split, proposed by Kalyan Kumar Reddy Kethireddy:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"LVCxEcQwCFvFl9oFAmzILLnff41H2IWPiEhC8H0P5njngMyRc/gc+vzm9+iqnhXSekXRKMxecbWI8KZ5yYwvTtt2/cpD9dWUVWovlcetp4122W0p9KOS07AJV/dwsjCc0lY7jjZkVJywMKEwGQ6R4PdLv3jpn9bhgwOBaOZxhoiAxKqSp3LrxutivTVu/26OMB5MlKex7cpAy7xklsph+qZx4R19Jg/rpdwq9e8P",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Numbers = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Numbers", type text}}),
Odd = Table.AddColumn(
#"Changed Type",
"Odd Min Max",
each [
a = Text.Split([Numbers], ","),
b = List.Transform(a, each Number.From(_)),
c = List.Select(b, each Number.IsOdd(_)),
d = Text.From(List.Min(c)) & "-" & Text.From(List.Max(c))
][d]
),
Even = Table.AddColumn(
Odd,
"Even Min Max",
each [
a = Text.Split([Numbers], ","),
b = List.Transform(a, each Number.From(_)),
c = List.Select(b, each Number.IsEven(_)),
d = Text.From(List.Min(c)) & "-" & Text.From(List.Max(c))
][d]
)
in
EvenSolving the challenge of Odd Even Min Max Split with Excel
Excel solution 1 for Odd Even Min Max Split, proposed by Bo Rydobon 🇹🇭:
=TEXTSPLIT(
TEXTJOIN(
"|",
0,
MAP(
A2:A10,
LAMBDA(
a,
LET(
t,
SORT(
--TEXTSPLIT(
a,
,
","
)
),
m,
MOD(
t,
2
),
o,
FILTER(
t,
m
),
e,
FILTER(
t,
m-1
),
IFERROR(
MIN(
o
)&-MAX(
o
),
)&"_"&IFERROR(
MIN(
e
)&-MAX(
e
),
)
)
)
)
),
"_",
"|"
)Excel solution 2 for Odd Even Min Max Split, proposed by Rick Rothstein:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A2:A10,
LAMBDA(
a,
LET(
n,
TEXTSPLIT(
a,
", "
),
o,
IF(
MOD(
n,
2
),
0+n
),
e,
IF(
MOD(
n,
2
)=0,
0+n
),
SUBSTITUTE(
MIN(
o
)&"-"&MAX(
o
)&"/"&MIN(
e
)&"-"&MAX(
e
),
"0-0",
""
)
)
)
)
),
"/",
"|"
)Excel solution 3 for Odd Even Min Max Split, proposed by John V.:
=LET(g,LAMBDA(p,MAP(A2:A10,LAMBDA(x,LET(n,-TEXTSPLIT(x,","),f,FILTER(n,MOD(n,2)=p),IFERROR(-MAX(f)&MIN(f),""))))),HSTACK(g(1),g(0)))Excel solution 4 for Odd Even Min Max Split, proposed by محمد حلمي:
=LET(p,LAMBDA(e,MAP(A2:A10,LAMBDA(c,LET(
x,TEXTSPLIT(c,",")+0,v,FILTER(x,e-MOD(x,2)),
IFERROR(MIN(v)&-MAX(v),""))))),HSTACK(p(0),p(1)))Excel solution 5 for Odd Even Min Max Split, proposed by 🇰🇷 Taeyong Shin:
=LET(
num,
A2:A10,
_f1,
LAMBDA(
a,
VSTACK(
MIN(
a
),
MAX(
a
)
)
),
_f2,
LAMBDA(
b,
x,
IF(
x = 1,
ISODD(
b
),
ISEVEN(
b
)
)
),
SUBSTITUTE(MAP(IF(
{1,
1},
num
),
IFNA(
{1,
2},
num
),
LAMBDA(m,
x,
LET(
n,
--TEXTSPLIT(
m,
,
", "
),
LAMBDA(f_1,
LAMBDA(_f2,
TEXTJOIN("-",
,
f_1(IF(_f2(n,
x),
n)))
)
)(_f1)(_f2)
)
)),
"0-0",
)
)Excel solution 6 for Odd Even Min Max Split, proposed by Kris Jaganah:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
"#",
,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
b,
IF(
MOD(
a,
2
),
a,
""
),
c,
IF(
MOD(
a,
2
)=0,
a,
""
),
MIN(
b
)&-MAX(
b
)&","&MIN(
c
)&-MAX(
c
)
)
)
)
),
",",
"#"
),
IF(
a="00",
"",
a
)
)Excel solution 7 for Odd Even Min Max Split, proposed by Timothée BLIOT:
=REDUCE(
{"Odd Min Max",
"Even Min Max"},
A2:A10,
LAMBDA(
ac,
v,
VSTACK(
ac,
LET(
A,
TEXTSPLIT(
v,
", "
)*1,
B,
MAP(
A,
LAMBDA(
x,
ISODD(
x
)
)
),
HSTACK(
IFERROR(
TEXTJOIN(
"-",
,
MIN(
FILTER(
A,
B
)
),
MAX(
FILTER(
A,
B
)
)
),
""
),
IFERROR(
TEXTJOIN(
"-",
,
MIN(
FILTER(
A,
NOT&(
B
)
)
),
MAX(
FILTER(
A,
NOT(
B
)
)
)
),
""
)
)
)
)
)
)Excel solution 8 for Odd Even Min Max Split, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
b,
FILTER(
a,
ISODD(
a
)
),
c,
FILTER(
a,
ISEVEN(
a
)
),
IFERROR(
MIN(
b
)&"-"&MAX(
b
)&"/",
" "
) & IFERROR(
MIN(
c
)&"-"&MAX(
c
)&";",
" "&";"
)
)
)
)
),
"/",
";",
1,
,
""
)Excel solution 9 for Odd Even Min Max Split, proposed by Oscar Mendez Roca Farell:
=REDUCE(
{"Odd";"Even"}&" Min Max",
A2:A10,
LAMBDA(
i,
x,
LET(
_n,
--TEXTSPLIT(
x,
,
", "
),
VSTACK(
i,
BYCOL(
_n/N(
MOD(
_n,
2
)={1;0}
),
LAMBDA(
c,
IFERROR(
CONCAT(
AGGREGATE(
{15,
14},
6,
c,
1
)*{1,
-1}
),
""
)
)
)
)
)
)
)Excel solution 10 for Odd Even Min Max Split, proposed by Sunny Baggu:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
s,
v,
VSTACK(
s,
LET(
_t,
TEXTSPLIT(
v,
,
", "
) + 0,
_e,
IFERROR(
SORT(
TOCOL(
MAP(
_t,
LAMBDA(
a,
IF(
ISEVEN(
a
),
a,
NA()
)
)
),
3
)
),
""
),
_o,
IFERROR(
SORT(
TOCOL(
MAP(
_t,
LAMBDA(
a,
IF(
ISODD(
a
),
a,
NA()
)
)
),
3
)
),
""
),
_odd,
TAKE(
_o,
1
) & "-" & TAKE(
_o,
-1
),
_even,
TAKE(
_e,
1
) & "-" & TAKE(
_e,
-1
),
HSTACK(
IF(
LEN(
_odd
) > 1,
_odd,
""
),
IF(
LEN(
_even
) > 1,
_even,
""
)
)
)
)
)
),
1
)Excel solution 11 for Odd Even Min Max Split, proposed by Sunny Baggu:
=IFNA(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
A2:A10,
LAMBDA(n,
LET(
_ts,
SORT(
TEXTSPLIT(
n,
,
", "
) + 0
),
_o,
MOD(
_ts,
2
),
_e,
-(_o - 1),
_e1,
LAMBDA(
x,
TEXTJOIN(
"-",
,
XLOOKUP(
1,
x,
_ts,
"",
,
{1,
-1}
)
)
),
_r,
TEXTJOIN("/",
,
_e1(_o),
_e1(_e)),
_r
)
)
)
),
"/",
","
),
""
)Excel solution 12 for Odd Even Min Max Split, proposed by Abdallah Ally:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
y,
", "
),
b,
--FILTER(
a,
ISODD(
a
)
),
c,
--FILTER(
a,
ISEVEN(
a
)
),
TRIM(
EXPAND(
IFERROR(
MIN(
b
)&"-"&MAX(
b
),
" "
),
1,
2,
IFERROR(
MIN(
c
)&"-"&MAX(
c
),
" "
)
)
)
)
)
)
),
1
)Excel solution 13 for Odd Even Min Max Split, proposed by Abdallah Ally:
=LET(a,TEXTSPLIT(A2,", "),b,--FILTER(a,ISODD(a)),c,--FILTER(a,ISEVEN(a)),TRIM(TEXTSPLIT(TEXTJOIN("_",TRUE,IFERROR(MIN(b)&"-"&MAX(b)," "),IFERROR(MIN(c)&"-"&MAX(c)," ")),"_")))Excel solution 14 for Odd Even Min Max Split, proposed by Anshu Bantra:
=LET(
list, --TEXTSPLIT(SUBSTITUTE(List, " ", ""), , ","),
odd_list, FILTER(list, ISODD(list)),
even_list, FILTER(list, ISEVEN(list)),
HSTACK(
IFERROR(CONCAT(MIN(odd_list), "-", MAX(odd_list)), ""),
IFERROR(CONCAT(MIN(even_list), "-", MAX(even_list)), "")
)
)Excel solution 15 for Odd Even Min Max Split, proposed by Pieter de B.:
=LET(
a,
A2:A10,
IFERROR(
MAKEARRAY(
ROWS(
a
),
2,
LAMBDA(
r,
c,
LET(
n,
--TEXTSPLIT(
INDEX(
a,
r
),
","
),
e,
TOROW(
n/ISEVEN(
n
),
2
),
o,
TOROW(
n/ISODD(
n
),
2
),
CHOOSE(
ISODD(
c
)+1,
MIN(
e
)&-MAX(
e
),
MIN(
o
)&-MAX(
o
)
)
)
)
),
""
)
)Excel solution 16 for Odd Even Min Max Split, proposed by JvdV –:
=MAKEARRAY(
9,
2,
LAMBDA(
r,
c,
LET(
z,
-TEXTSPLIT(
INDEX(
A2:A10,
r
),
","
),
f,
FILTER(
z,
c-1-MOD(
z,
2
),
0
),
IF(
@f,
-MAX(
f
)&MIN(
f
),
""
)
)
)
)Excel solution 17 for Odd Even Min Max Split, proposed by Julien Lacaze:
=TEXTSPLIT(
TEXTJOIN(
"@",
0,
MAP(
A2:A10,
LAMBDA(
d,
LET(
s,
--TEXTSPLIT(
d,
", "
),
o,
FILTER(
s,
ISODD(
s
)
),
e,
FILTER(
s,
ISEVEN(
s
)
),
IFERROR(
MIN(
o
)&"-"&MAX(
o
),
""
)&"|"
&IFERROR(
MIN(
e
)&"-"&MAX(
e
),
""
)
)
)
)
)
,
"|",
"@"
)Excel solution 18 for Odd Even Min Max Split, proposed by Nicolas Micot:
=SIERREUR(
LET(
_nombres;
FRACTIONNER.TEXTE(
A2;
;
", "
)+0;
_odds;
FILTRE(
_nombres;
MOD(
_nombres;
2
)>0;
""
);
MIN(
_odds
) & "-" & MAX(
_odds
)
);
""
)
even min max:
=SIERREUR(
LET(
_nombres;
FRACTIONNER.TEXTE(
A2;
;
", "
)+0;
_evens;
FILTRE(
_nombres;
MOD(
_nombres;
2
)=0;
""
);
MIN(
_evens
) & "-" & MAX(
_evens
)
);
""
)Excel solution 19 for Odd Even Min Max Split, proposed by Daniel Garzia:
=LET(
f,
LAMBDA(
r,
LET(
n,
--TEXTSPLIT(
r,
,
", "
),
t,
ISODD(
n
),
o,
FILTER(
n,
t
),
e,
FILTER(
n,
NOT(
t
)
),
HSTACK(
IFERROR(
MIN(
o
)&"-"&MAX(
o
),
""
),
IFERROR(
MIN(
e
)&"-"&MAX(
e
),
""
)
)
)
),
REDUCE(
HSTACK(
"Odd Min Max",
"Even Min Max"
),
A2:A10,
LAMBDA(
a,
b,
VSTACK(
a,
f(
b
)
)
)
)
)Excel solution 20 for Odd Even Min Max Split, proposed by Md Ismail Hosen:
=LET(
Numbers,
A2:A10,
fx_One,
LAMBDA(
NumberCommaDelimited,
LET(
SortedData,
TRANSPOSE(
TEXTSPLIT(
NumberCommaDelimited,
", "
) * 1
),
IsEven,
ISEVEN(
SortedData
),
OnlyOdd,
SORT(
FILTER(
SortedData,
NOT(
IsEven
)
)
),
OnlyEven,
SORT(
FILTER(
SortedData,
IsEven
)
),
OddPart,
IFERROR(
TAKE(
OnlyOdd,
1
) & "-" & TAKE(
OnlyOdd,
-1
),
""
),
EvenPart,
IFERROR(
& TAKE(
OnlyEven,
1
) & "-" & TAKE(
OnlyEven,
-1
),
""
),
Result,
HSTACK(
OddPart,
EvenPart
),
Result
)
),
Result,
REDUCE(
{"Odd Min Max",
"Even Min Max"},
Numbers,
LAMBDA(
a,
v,
VSTACK(
a,
fx_One(
v
)
)
)
),
Result
)Excel solution 21 for Odd Even Min Max Split, proposed by Amardeep Singh:
=REDUCE(
{"Odd Min Max",
"Even Min Max"},
A2:A10,
LAMBDA(
a,
v,
LET(
d,
--TEXTSPLIT(
v,
", "
),
e,
FILTER(
d,
ISEVEN(
d
)
),
o,
FILTER(
d,
ISODD(
d
)
),
VSTACK(
a,
HSTACK(
IFERROR(
MIN(
o
)&"-"&MAX(
o
),
""
),
IFERROR(
MIN(
e
)&"-"&MAX(
e
),
""
)
)
)
)
)
)Excel solution 22 for Odd Even Min Max Split, proposed by Mungunbayar Bat-Ochir:
=LET(
header;
B1:C1;
data;
BYROW(
A2:A11;
LAMBDA(
row;
LET(
nums;
TEXTSPLIT(
row;
;
", "
)*1;
bool;
MOD(
nums;
2
)=0;
odd;
FILTER(
nums;
NOT(
bool
);
NA()
);
even;
FILTER(
nums;
bool;
NA()
);
IFERROR(
MIN(
odd
)&"-"&MAX(
odd
);
""
)&"#"&IFERROR(
MIN(
even
)&"-"&MAX(
even
);
""
)
)
)
);
VSTACK(
header;
TEXTSPLIT(
TEXTJOIN(
"|";
FALSE;
data
);
"#";
"|";
FALSE
)
)
)Excel solution 23 for Odd Even Min Max Split, proposed by Miguel Angel Franco García:
=LET(
a;
BYCOL(
DIVIDIRTEXTO(
A2;
","
);
LAMBDA(
x;
SI(
ES.IMPAR(
x
);
x;
""
)
)
);
b;
ABS(
FILTRAR(
a;
a<>""
)
);
c;
SI.ERROR(
APILARH(
MIN(
b
);
MAX(
b
)
);
""
);
UNIRCADENAS(
"-";
VERDADERO;
c
)
)
=LET(
a;
BYCOL(
DIVIDIRTEXTO(
A2;
","
);
LAMBDA(
x;
SI(
ES.PAR(
x
);
x;
""
)
)
);
b;
ABS(
FILTRAR(
a;
a<>""
)
);
c;
SI.ERROR(
APILARH(
MIN(
b
);
MAX(
b
)
);
""
);
UNIRCADENAS(
"-";
VERDADERO;
c
)
)Excel solution 24 for Odd Even Min Max Split, proposed by Deepak Dalal:
=IFNA(LET(_rng,A2:A10,_fn,LAMBDA(a,LET(_num,TEXTSPLIT(a,,","),_even,FILTER(_num,ISEVEN(_num),""),_odd,FILTER(_num,ISODD(_num),""),HSTACK(TAKE(IF(_odd="","",MIN(--_odd)&"-"&MAX(--_odd)),1),TAKE(IF(_even="","",MIN(--_even)&"-"&MAX(--_even)),1)))),REDUCE("",_rng,LAMBDA(a,v,VSTACK(a,_fn(v))))), "")
I really need to learn to create a function using Lambda, to this idea to loop through the range from Md Ismail Hosen/ Thank you.
Although the trick by, Bo Rydobon 🇹🇭 to create a single column results (easy to loop in Map and Lmabda and then split is also super smart and super cool)Excel solution 25 for Odd Even Min Max Split, proposed by Crispo Mwangi:
=MIN(
IF(
ISODD(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)=0,
"",
ISODD(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)
)
&-
MAX(
ISODD(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)
to get the Even min-max
=MIN(
IF(
ISEVEN(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)=0,
"",
ISEVEN(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)
)&-
MAX(
ISEVEN(
FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)*FILTERXML(
""&SUBSTITUTE(
A2,
",",
""
)&"",
"//a"
)
)Solving the challenge of Odd Even Min Max Split with SQL
SQL solution 1 for Odd Even Min Max Split, proposed by Zoran Milokanović:
SELECT /* Microsoft SQL Server 2019 */
F.NUMBERS
FROM
(
SELECT
D.NUMBERS
FROM DATA D
CROSS APPLY STRING_SPLIT(D.NUMBERS, ',')
) F
GROUP BY
F.NUMBERS
;
&