# Excel BI Challenge Workout 190

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

(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

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
``````

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

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],
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
``````

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

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

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

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
``````

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

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

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

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

## 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!

``````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
[
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
``````

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

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

``````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
``````

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

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

``````=MAP(A2:A10,LAMBDA(a,LET(d,", ",b,TEXTSPLIT(a,,d),c,VSTACK(DROP(b,1),0),j,b&d&c,
`=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)))))))`