Excel BI Challenge Workout 190

EnterpriseDNA · May 19, 2023, 9:28am

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

Find the unique pairs of numbers whose sum is the maximum. Pairs are sequential from left to right, they should not be made randomly.
Ex. 5, 4, 0, 8, 3, 5 - There are 5 pairs here
Pairs are 5, 4 | 4, 0 | 0, 8 | 8, 3 | 3, 5
In case of answers having multiple pairs, separate them by semi colon. Order of digits in pairs don’t matter. Hence 3, 8 and 8, 3 are same.

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/dmtxx-Uu

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

Excel BI’s LinkedIn Post

EnterpriseDNA · May 19, 2023, 9:29am

LinkedIn Post by:
Zoran Milokanović

Finding the unique pairs w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each
let
p = List.Skip(List.Accumulate(List.Transform(Text.Split([Numbers], ", "), each Number.From(_)), {}, (s, c) => let t = {List.Last(s){1}?} & {c} in s & (if List.ContainsAny(s, {t, List.Reverse(t)}) then {} else {t})))
in
Text.Combine(List.Transform([List.Select](http://list.select/)(p, each List.Sum(_) = List.Max(List.Transform(p, each List.Sum(_)))), (r) => Text.Combine(List.Transform(r, each Text.From(_)), ", ")), "; ")
)
in
Solution

EnterpriseDNA · May 19, 2023, 9:30am

LinkedIn post by:
Stevenson Yu

A very convoluted solution, but hey, it works without reordering the numbers, sooo… (also, VLOOKUP rules)

=MAP(A2:A10, LAMBDA(X,
LET(A,--(TEXTSPLIT(X,,", ")),
B, DROP(A,-1),
C, DROP(A,1),
D, IF(B>C,C,B),
E, IF(B>C,B,C),
F, HSTACK(D,E),
G, HSTACK(D&"|"&E,B&", "&C),
H, UNIQUE(FILTER(HSTACK(D,E),D+E=MAX(D+E))),
I, BYROW(H, LAMBDA(H, TEXTJOIN("|",,H))),
J, VLOOKUP(I,G,2,0),
TEXTJOIN("; ",,J))))

EnterpriseDNA · May 19, 2023, 9:31am

LinkedIn Post by:
Alejandro Simón

Adjunto mi qury…

Me costó mucho esta query y no estoy seguro que sea totalmente dinámica, pero ahí les va:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Custom", each
let
a = Text.Split([Numbers], ", "),
b = List.Transform(a, Number.From),
c = List.Transform({0..List.Count(b)-2}, each {b{_}, b{_+1}}),
d = List.Max(List.Transform(c, each List.Sum(_))),
e = List.Distinct([List.Select](http://list.select/)(c, each List.Sum(_) = d)),
f = Text.Combine(if List.Count(e)<2 then List.Transform( {0..List.Count(e)-1}, each Text.Combine(List.Transform(e{_}, Text.From), ", ")) else List.Transform( {0..List.Count(e)-2}, each if List.RemoveItems(e{_}, e{_+1}) = {} then List.Transform(e{0}, Text.From) else List.Transform({0..List.Count(e)-1}, each Text.Combine(List.Transform(e{_}, Text.From), ", "))){0}, "; ")
in f)
in
Sol

EnterpriseDNA · May 19, 2023, 9:32am

LinkedIn Post by:
Brian Julius

My #powerquery solution. More difficult than anticipated due to 8, 3, 8 scenario.

https://gist.github.com/bjulius/67434f6ba6a56b80c379d6888442f159

EnterpriseDNA · May 19, 2023, 9:33am

LinkedIn post by:
محمد حلمي

=MAP(A2:A10,LAMBDA(a,LET(e,UNIQUE(TEXTSPLIT(CONCAT(BYROW(IFERROR(INDEX(TEXTSPLIT(a,,","),SEQUENCE(10)+{0,1}),),LAMBDA(a,CONCAT(SORT(a,,,1)&" ")))&"-")," ","-",1)),r,TAKE(e,,1)+DROP(e,,1),TEXTJOIN("; ",,BYROW(FILTER(e,r=MAX(r)),LAMBDA(a,TEXTJOIN(", ",,a)))))))

EnterpriseDNA · May 19, 2023, 9:34am

LinkedIn post by:
Bo Rydobon

M-Code

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  ANS = Table.AddColumn(Source, "Ans", each let s = Text.Split([Numbers],", "),t= Table.Distinct(Table.FromRows(List.Transform({0..List.Count(s)-2},(n)=> 
    let a =s{n}, b=s{n+1} in {a&", "&b, Number.From(a)+Number.From(b), Text.Combine(List.Sort({a,b})) })),"Column3")  
    in Text.Combine(Table.SelectRows(t,each [Column2] = List.Max(t[Column2]))[Column1],", ") )
in
  ANS

EnterpriseDNA · May 19, 2023, 9:35am

LinkedIn post by:
Sunny Baggu

=MAP(
    A2:A10,
    LAMBDA(a,
        LET(
            _ts, TEXTSPLIT(a, , ", "),
            _sum, DROP(_ts, -1) + DROP(_ts, 1),
            _maxsum, MAX(_sum),
            _fnum, UNIQUE(FILTER(HSTACK(DROP(_ts, -1), DROP(_ts, 1)), _sum = _maxsum)),
            _ufnum, UNIQUE(
                MAKEARRAY(
                    ROWS(_fnum),
                    COLUMNS(_fnum),
                    LAMBDA(r, c, INDEX(SORT(INDEX(_fnum, r, ), , , 1), c))
                )
            ),
            _res, TEXTJOIN("; ", TRUE, BYROW(_ufnum, LAMBDA(a, ARRAYTOTEXT(a)))),
            _res
        )
    )
)

EnterpriseDNA · May 19, 2023, 9:35am

LinkedIn post by:
Sunny Baggu

=MAP(
    A2:A10,
    LAMBDA(a,
        LET(
            _tsplit, TEXTSPLIT(a, , ",") + 0,
            _d1, DROP(_tsplit, -1),
            _d2, DROP(_tsplit, 1),
            _sortnum, DROP(
                REDUCE(
                    "",
                    SEQUENCE(ROWS(_d1)),
                    LAMBDA(a, v,
                        VSTACK(
                            a,
                            HSTACK(
                                MAX(INDEX(_d1, v, 1), INDEX(_d2, v, 1)),
                                MIN(INDEX(_d1, v, 1), INDEX(_d2, v, 1))
                            )
                        )
                    )
                ),
                1
            ),
            _sum, BYROW(_sortnum, LAMBDA(a, SUM(a))),
            _pairs, BYROW(_sortnum, LAMBDA(a, ARRAYTOTEXT(a))),
            TEXTJOIN("; ", , UNIQUE(FILTER(_pairs, _sum = MAX(_sum))))
        )
    )
)

EnterpriseDNA · May 19, 2023, 9:36am

LinkedIn post by:
Sunny Baggu

=MAP(
    A2:A10,
    LAMBDA(a,
        LET(
            _ts, TEXTSPLIT(a, , ", "),
            _e1, LAMBDA(x, INDEX(_ts, x, 1)),
            _rsum, MAKEARRAY(ROWS(_ts) - 1, 1, LAMBDA(r, c, _e1(r) + _e1(r + 1))),
            _maxsum, MAX(_rsum),
            _rpairs, MAKEARRAY(
                ROWS(_ts) - 1,
                1,
                LAMBDA(r, c, TEXTJOIN(", ", , (SORT(CHOOSEROWS(_ts, r, r + 1)))))
            ),
            TEXTJOIN("; ", , UNIQUE(FILTER(_rpairs, _rsum = _maxsum)))
        )
    )
)

EnterpriseDNA · May 19, 2023, 9:37am

LinkedIn Post by:
Kris Jaganah

=MAP(A2:A10,LAMBDA(z,LET(a,--TEXTSPLIT(z,,", "),b,VSTACK(DROP(a,1),TAKE(a,1)),c,DROP(a,-1),d,DROP(b,-1),e,FILTER(HSTACK(c,d),c+d=LARGE(c+d,1)),f,BYROW(e,LAMBDA(x,TEXTJOIN(", ",1,x))),TEXTJOIN("; ",1,UNIQUE(IF((TAKE(e,1,1)=TAKE(e,-1,-1))*(ROWS(e)>1),DROP(f,-1),f))))))

EnterpriseDNA · May 19, 2023, 9:38am

LinkedIn Post by:
John Jairo V.

Hi to all!

One option could be:

=MAP(A2:A10,LAMBDA(x,LET(s,", ",n,TEXTSPLIT(x,,s),m,DROP(VSTACK(n,0),1),TEXTJOIN("; ",,UNIQUE(FILTER(IF(n<m,n&s&m,m&s&n),n+m=MAX(n+m)))))))

Blessings!

EnterpriseDNA · May 19, 2023, 9:38am

LinkedIn post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 dup = Table.DuplicateColumn(Fonte, "Numbers", "copy"),
 div = Table.ExpandListColumn(Table.TransformColumns(dup, {{"copy", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "copy"),
 gp = Table.Group(div, {"Numbers"}, {{"Contagem", each 
[
a = Table.AddIndexColumn(_,"Ind",0,1),
b = Table.SelectRows(Table.AddColumn(a,"tab",each try a{[Ind]+1}[copy] otherwise null),each [tab] <> null),
c = Table.AddColumn(b, "dup", each List.Sum(List.Transform({[copy],[tab]}, each Number.From(_)))),
d = Table.SelectRows(c, each List.Max(c[dup]) = [dup]),
e = Table.Distinct(Table.AddColumn(d, "duplas", each Text.Combine({[copy], [tab]}, ", ")), "duplas"),
f = Table.Distinct(Table.AddColumn(e, "res",each Text.Combine(e[duplas],"; ")), "res")
][f]
}}),
 exp = Table.ExpandTableColumn(gp, "Contagem", {"res"}, {"res"}),
 res = Table.AddColumn(exp, "result", each [
a = Text.Split([Numbers],", "),
b = List.Reverse(Text.Split([Numbers],", ")),
c = if a = b then Text.Combine(List.FirstN( Text.Split([Numbers],", "),2),", ") else [res]
][c])[result]
in
 res

EnterpriseDNA · May 19, 2023, 9:39am

LinkedIn post by:
Julien Lacaze

Did not removed the 8,3;3,8 solution :

=MAP(A2:A10,LAMBDA(array,
LET(data,array,split,TEXTSPLIT(data,","),
arr,MAKEARRAY(COLUMNS(split)-1,2,LAMBDA(r,c,NUMBERVALUE(CHOOSECOLS(split,r+c-1)))),
suma,BYROW(arr,LAMBDA(a,SUM(a))),
filt,UNIQUE(FILTER(arr,suma=MAX(suma))),
rowa,BYROW(filt,LAMBDA(arrr,TEXTJOIN(",",TRUE,arrr))),TEXTJOIN(";",TRUE,rowa))))

EnterpriseDNA · May 19, 2023, 9:43am

LinkedIn Post by:
Tolga Demirci

=MAP(A2:A10;LAMBDA(a;LET(r;INDEX(TEXTSPLIT(a;;", ");SEQUENCE(LEN(TEXTJOIN(;;TEXTSPLIT(a;;", ")))));e;INDEX(TEXTSPLIT(a;;", ");SEQUENCE(LEN(TEXTJOIN(;;TEXTSPLIT(a;;", "))))+1);w;TEXTSPLIT(a;;", ");q;INDEX(TEXTSPLIT(a;;", ");SEQUENCE(LEN(TEXTJOIN(;;TEXTSPLIT(a;;", ")))));TEXTJOIN("; ";;UNIQUE(LET(p;IFERROR(MAP(INDEX(q;ROW(INDIRECT("A1:"&"A"&COUNTA(r))))+INDEX(e;ROW(INDIRECT("A1:"&"A"&COUNTA(e))));INDEX(w;SEQUENCE(LEN(TEXTJOIN(;;w))))&", "&INDEX(w;SEQUENCE(LEN(TEXTJOIN(;;w)))+1);LAMBDA(m;n;XLOOKUP(MAX(IFERROR(INDEX(q;ROW(INDIRECT("A1:"&"A"&COUNTA(q))))+INDEX(INDEX(w;SEQUENCE(LEN(TEXTJOIN(;;w)))+1);ROW(INDIRECT("A1:"&"A"&COUNTA(INDEX(w;SEQUENCE(LEN(TEXTJOIN(;;w)))+1)))));""));m;n)));"");FILTER(p;p<>"")))))))

EnterpriseDNA · May 19, 2023, 9:44am

LinkedIn Post by:
Omid Motamedisedeh

let
 Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
 Custom1 = List.Transform(Source[Numbers], (ox)=> 
 let
 A=Text.Split(ox,", "),
 B=List.Transform({1..List.Count(A)-1}, each Number.From(A{_-1})+Number.From(A{_})),
 C=List.PositionOf(B,List.Max(B)),
 D=A{C} &", "& A{C+1}
 
 in
 D)
 
in
 Custom1

EnterpriseDNA · May 19, 2023, 9:45am

LinkedIn Post by:
Rick Rothstein

Seems kind of long, but it works…

=MAP(A2:A10,LAMBDA(a,TEXTJOIN("; ",,UNIQUE(MAP(LET(f,DROP(TEXTSPLIT(a,", "),,-1),s,DROP(TEXTSPLIT(a,", "),,1),FILTER(f&", "&s,f+s=MAX(f+s))),LAMBDA(x,TEXTJOIN(", ",,SORT(TEXTSPLIT(x,", "),,,1)))),1))))

EnterpriseDNA · May 19, 2023, 9:46am

LinkedIn Post by:
Bo Rydobon

=MAP(A2:A10,LAMBDA(a,LET(d,", ",b,TEXTSPLIT(a,,d),c,VSTACK(DROP(b,1),0),j,b&d&c,
m,MAP(j,LAMBDA(m,CONCAT(SORT(TEXTSPLIT(m,,d))))),TEXTJOIN("; ",,FILTER(j,(XMATCH(m,m)=SEQUENCE(ROWS(j)))*(b+c=MAX(b+c)))))))

EnterpriseDNA · May 19, 2023, 9:46am

LinkedIn Post by:
Bo Rydobon

This one only catch 8, 3 ,8 same number in next sequence
But 8, 3, 3, 8 will miss

=MAP(A2:A10,LAMBDA(a,LET(d,", ",b,TEXTSPLIT(a,,d),c,VSTACK(DROP(b,1),0),j,b&d&c,
TEXTJOIN("; ",,FILTER(j,(j<>DROP(VSTACK(0,c&d&b),-1))*(b+c=MAX(c+b)))))))

EnterpriseDNA · May 19, 2023, 9:47am

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=MAP(A2:A10, LAMBDA(x, LET(a, --TEXTSPLIT(x, ,", "), b, VSTACK(DROP(a,1),0), TEXTJOIN("; ",,UNIQUE(FILTER(a&", "&b, a+b=MAX(a+b)))))))

Excel BI Challenge Workout 190

LinkedIn Post by: Zoran Milokanović

LinkedIn post by: Stevenson Yu

LinkedIn Post by: Alejandro Simón

LinkedIn Post by: Brian Julius

LinkedIn post by: محمد حلمي

LinkedIn post by: Bo Rydobon

LinkedIn post by: Sunny Baggu

LinkedIn post by: Sunny Baggu

LinkedIn post by: Sunny Baggu

LinkedIn Post by: Kris Jaganah

LinkedIn Post by: John Jairo V.

LinkedIn post by: Luan Rodrigues

LinkedIn post by: Julien Lacaze

LinkedIn Post by: Tolga Demirci

LinkedIn Post by: Omid Motamedisedeh

LinkedIn Post by: Rick Rothstein

LinkedIn Post by: Bo Rydobon

LinkedIn Post by: Bo Rydobon

LinkedIn Post by: Hussein SATOUR

LinkedIn Post by:
Zoran Milokanović

LinkedIn post by:
Stevenson Yu

LinkedIn Post by:
Alejandro Simón

LinkedIn Post by:
Brian Julius

LinkedIn post by:
محمد حلمي

LinkedIn post by:
Bo Rydobon

LinkedIn post by:
Sunny Baggu

LinkedIn post by:
Sunny Baggu

LinkedIn post by:
Sunny Baggu

LinkedIn Post by:
Kris Jaganah

LinkedIn Post by:
John Jairo V.

LinkedIn post by:
Luan Rodrigues

LinkedIn post by:
Julien Lacaze

LinkedIn Post by:
Tolga Demirci

LinkedIn Post by:
Omid Motamedisedeh

LinkedIn Post by:
Rick Rothstein

LinkedIn Post by:
Bo Rydobon

LinkedIn Post by:
Bo Rydobon

LinkedIn Post by:
Hussein SATOUR