Excel BI Challenge Workout 186

[EnterpriseDNA]

— 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.

Post answers in Comment.

(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)

Download Practice File - https://lnkd.in/djqQHBvf

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

Excel BI LinkedIn Post

Excel BI on LinkedIn: #excel #excelchallenge #powerquerychallenge...

[EnterpriseDNA]

Linkedin post by:
Rick Rothstein

=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.

[EnterpriseDNA]

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!

[EnterpriseDNA]

LinkedIn Post by:
Alejandro Simón

Adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Answer", each let
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 f)[[Answer]]
in
Sol

[EnterpriseDNA]

LinkedIn Post by:
Muhammad Rayan S.

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
                )
            ),
            ""
        )
    )
)

[EnterpriseDNA]

LinkedIn Post by:
Brian Julius

My #powerquery solution:

let
Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Numbers", "Numbers2"),
AddReturnList = Table.AddColumn(Source, "Answer", each [
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

[EnterpriseDNA]

LinkedIn post by
Zoran Milokanović

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

[EnterpriseDNA]

LinkedIn Post by:
Quadri Olayinka Atharu

=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

[EnterpriseDNA]

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))))),"")

[EnterpriseDNA]

LinkedIn post by:
Guillermo Arroyo

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

[EnterpriseDNA]

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

}
),{"Numbers","Answer"})
in
Custom1

[EnterpriseDNA]

LinkedIn post by:
Luan Rodrigues

let
Fonte = Tabela1,
res = Table.AddColumn(Fonte, "Personalizar", each
[
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

[EnterpriseDNA]

LinkedIn Post by:
Bo Rydobon

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

[EnterpriseDNA]

LinkedIn Post by:
Omid Motamedisedeh

= 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])

[EnterpriseDNA]

LinkedIn Post by:
Guillermo Arroyo

=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)),"")))))

[EnterpriseDNA]

LinkedIn Post by:
Hussein SATOUR

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))))))),"")

[EnterpriseDNA]

LinkedIn Post by:
Erik Oehm

=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)

[EnterpriseDNA]

LinkedIn post by
Lorenzo Foti

=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)

[EnterpriseDNA]

LinkedIn post by:
Caroline Blake

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),"")),"")))))

[EnterpriseDNA]

LinkedIn post by:
Stevenson Yu

=MAP(A2:A7, LAMBDA(X,
LET(A, TEXTSPLIT(X, ", "),
B, FILTER(A, ISODD(A)),
C, FILTER(A, ISEVEN(A)),
D, COUNTA(B)-COUNTA(C),
E, TEXTJOIN(", ",,IFS(D=0, "", D>0, C, 1, B)),
E)))