# Excel BI Challenge Workout 186

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 186 —
(Solutions in any language are also welcome for Excel Challenges)

If odd numbers are less, then post odd numbers.
If even numbers are less, then post even numbers.
If both are equal, answer would be none.
Ex. 3, 8, 9, 4, 6 => It contains 2 odd numbers 3, 9 and 3 even numbers 8, 4, 6. Hence, answer would be 3, 9.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

`=MAP(A2:A7,LAMBDA(a,LET(n,TEXTSPLIT(a,", "),z,MOD(n,2),s,2*SUM(z),c,COUNT(z),TEXTJOIN(", ",,IF(s=c,"",IFERROR(FILTER(n,z=(1-(s>c))),""))))))`

NOTE: This formula is 27 characters shorter than my first one; however, for the first time in a LONG time, my formula is even shorter (by 10 characters) than Bo Rydobon’s formula! I’m betting that should spur Bo Rydobon to create an even shorter formula now. ## Linkedin Post by:John Jairo V.

Hi to all!

One option could be: `=MAP(A2:A7,LAMBDA(x,LET(n,TEXTSPLIT(x,", "),m,MOD(n,2),TEXTJOIN(", ",,IF(CHOOSE(2+SIGN(SUM(1-m)-SUM(m)),1-m,,m),n,"")))))`

Blessings!

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Text.Split([Numbers], ", "),
b = List.Transform(a, Number.From),
c = [List.Select](http://list.select/)(b, Number.IsOdd),
d = [List.Select](http://list.select/)(b, Number.IsEven),
e = if List.Count(d)>List.Count(c) then c else if List.Count(d)<List.Count(c) then d else null,
f = try Text.Combine(List.Transform(e, Text.From), ", ") otherwise ""
in
Sol
``````

``````MAP(
A2:A7,
LAMBDA(x,
IFERROR(
ARRAYTOTEXT(
LET(
m, TEXTSPLIT(x, ", ") + 0,
f, FILTER(m, m > -1),
odd, SUM(LEN(FILTER(f, ISODD(f)))),
even, SUM(LEN(FILTER(f, ISEVEN(f)))),
sol, IF(
even = odd,
"",
IF(even > odd, FILTER(f, ISODD(f)), FILTER(f, ISEVEN(f)))
),
sol
)
),
""
)
)
)
``````

My #powerquery solution:

``````let
Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Numbers", "Numbers2"),
b = _[Numbers2],
b1 = List.Transform( Text.Split( b, ", "), each Number.FromText(_)),
evenlist = [List.Select](http://list.select/)(b1, each Number.IsEven(_)),
oddlist = [List.Select](http://list.select/)(b1, each Number.IsOdd(Number.From(_))),
evencount = try List.Count( evenlist ) otherwise 0,
oddcount = try List.Count ( oddlist ) otherwise 0,
return = if evencount < oddcount then evenlist else
if oddcount < evencount then oddlist else null
][return]),
Extract = Table.TransformColumns(AddReturnList, {"Answer", each try Text.Combine(List.Transform(_, Text.From), ", ") otherwise null, type text}),
RemoveCol = Table.RemoveColumns(Extract,{"Numbers2"})
in
RemoveCol
``````

Odd and Even Numbers w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each
let
n = Text.Split([Numbers], ", "),
o = [List.Select](http://list.select/)(n, each Number.IsOdd(Number.From(_))),
oc = List.Count(o),
e = List.Difference(n, o),
ec = List.Count(e)
in
Text.Combine(if ec < oc then e else if oc < ec then o else {}, ", "))
in
Solution
``````

``````=DROP(REDUCE("",A2:A7,LAMBDA(x,y,
LET(num,--TEXTSPLIT(y,","),
eT,ISEVEN(--num),
oT,ISODD(--num),
oC,SUM(N(oT)),
eC,SUM(N(eT)),
_min,MIN(oC,eC),
_con,IFS(oC=eC,FALSE,_min=oC,oT,_min=eC,eT),
VSTACK(x,TEXTJOIN(", ",1,FILTER(num,_con,"")))))),1)

Where,

eT - Even Numbers Test
oT - Odd Numbers Test
oC - Odd Numbers Count
eC - Even Numbers Count
_min - Minimum between the Count of Even and Odd
_con, condition for filtering
``````

## LinkedIn Post by:Hussain Ali Nasser

`=IFERROR(MAP(A2:A7,LAMBDA(_range,LET(_splitrange,TEXTSPLIT(_range,,", "),_even,FILTER(_splitrange, ISEVEN(_splitrange)),_odd,FILTER(_splitrange, ISODD(_splitrange)),_evenrows,ROWS(_even),_oddrows,ROWS(_odd),TEXTJOIN(", ",,IFS(_evenrows>_oddrows,_odd,_evenrows<_oddrows,_even))))),"")`

Power Query

``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(List.Transform(Origen[Numbers], each Text.Split(_, ", ")), each
let
b = List.Transform(_, each Number.FromText(_)),
c = List.Select(b, each Number.IsOdd(_)),
d = List.RemoveItems(b, c),
e = if List.Count(c) > List.Count(d)
then d
else
if List.Count(c) < List.Count(d)
then c
else {null}
in
Text.Combine(List.Transform(e, each Text.From(_)), ", "))
in
a
``````

## LinkedIn Post by:Felipe Perez Arevalo

``````let
Source = Table1,
Custom1 =
Table.RenameColumns(
Table.TransformColumns(
Source,
{"Numbers",
each
[
l=Text.Split(_,", "),
e= [List.Select](http://list.select/)(l,each Number.IsEven(Number.From(_))),
o= [List.Select](http://list.select/)(l,each not Number.IsEven(Number.From(_))),
l1= Text.Combine(if List.Count(e)=List.Count(o) then {} else if List.Count(e)>List.Count(o) then o else e,", " )
][l1],
type text

}
in
Custom1
``````

``````let
Fonte = Tabela1,
[
odd = [List.Select](http://list.select/)(List.Transform(Text.Split([Numbers],", "), Number.From), each Number.IsOdd(_) ),
even = [List.Select](http://list.select/)(List.Transform(Text.Split([Numbers],", "), Number.From), each Number.IsEven(_)),
a = if List.Count(odd) < List.Count(even) then Text.Combine(List.Transform(odd,Text.From),", ") else if List.Count(odd) = List.Count(even) then null else
Text.Combine(List.Transform(even,Text.From),", ")
][a])
in
res
``````

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformColumns(Source,{"Numbers",each let t = Text.Split(_,", "), o = [List.Select](http://list.select/)(t, each Number.IsOdd(Number.From(_))), d = List.Count(t)-2*List.Count(o) in
Text.Combine(if d = 0 then {} else if d>0 then o else List.Difference(t,o),", ") })
in
Ans
``````

`= Table.AddColumn(Source, "Custom", each [a= try List.Transform(Text.Split([Numbers],", "), each Number.From(_)) otherwise {Number.From([Numbers])},b=[List.Select](http://list.select/)(a,Number.IsOdd), c=([List.Select](http://list.select/)(a,Number.IsEven)), d= if List.Count(b)>List.Count(c) then c else if List.Count(b)<List.Count(c) then b else {},f=Text.Combine(List.Transform(d, each Text.From(_)),",")][f])`

`=MAP(A2:A7,LAMBDA(a,LET(b,--TEXTSPLIT(a,,", "),TEXTJOIN(", ",0,FILTER(b,CHOOSE(IFERROR(2+ROUNDUP(1-COUNT(b)/2/SUM(--ISODD(b)),0),2),ISODD(b),SEQUENCE(COUNT(b),,0,0),ISEVEN(b)),"")))))`

Thank you Excel BI

`=IFERROR(MAP(A2:A7, LAMBDA(x, LET(a, --TEXTSPLIT(x,,", "),b,ISODD(a), c, SUM(b*1)/COUNT(b*1), TEXTJOIN(", ",,FILTER(a,IFS(c = 0.5, "", c<0.5, b, 1, NOT(b))))))),"")`

``````=LAMBDA(list, LET(
_split, VALUE(TRIM(TEXTSPLIT(list,,","))),
_isEven, MAP(_split,LAMBDA(x,ISEVEN(x))),
_evenCount, SUM(N(_isEven)),
_oddCount, ROWS(_isEven)-_evenCount,
_filtered, FILTER(_split,_isEven=(_evenCount<_oddCount),""),
_result, IF(_evenCount=_oddCount,"",TEXTJOIN(", ",,_filtered)),
_result
)
)(A2)
``````

``````=LET(
rng;NUMBERVALUE(TEXTSPLIT(A2;","));
evenCheck;IF(ISEVEN(rng);1;0);
oddCheck;IF(ISODD(rng);1;0);
outP;IFERROR(IFS(SUM(evenCheck)>SUM(oddCheck);TEXTJOIN(", ";;FILTER(rng;oddCheck=1));SUM(evenCheck)<SUM(oddCheck);TEXTJOIN(", ";;FILTER(rng;evenCheck=1));TRUE;"");rng);
outP)
``````

Hi all, I am loving these challenges! Here is my answer:

``````=MAP(A2:A7,LAMBDA(_a,LET(a,TEXTSPLIT(_a,", "),
_even,SUM(--ISEVEN(a)),
_odd,SUM(--ISODD(a)),
ARRAYTOTEXT(IFERROR(IF(_even<_odd,FILTER(a,ISEVEN(a)=TRUE),IF(_odd<_even,FILTER(a,ISODD(a)=TRUE),"")),"")))))
``````

``````=MAP(A2:A7, LAMBDA(X,