Home » Find Max Sum Pairs

Find Max Sum Pairs

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.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 190
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Find Max Sum Pairs with Power Query

Power Query solution 1 for Find Max Sum Pairs, proposed 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
Power Query solution 2 for Find Max Sum Pairs, proposed by Bo Rydobon 🇹🇭: 
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
Power Query solution 3 for Find Max Sum Pairs, proposed by Zoran Milokanović: 
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(p, each List.Sum(_) = List.Max(List.Transform(p, each List.Sum(_)))), 
            (r) => Text.Combine(List.Transform(r, each Text.From(_)), ", ")
          ), 
          "; "
        )
  )
in
  Solution
Power Query solution 4 for Find Max Sum Pairs, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
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(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
Power Query solution 5 for Find Max Sum Pairs, proposed 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

Solving the challenge of Find Max Sum Pairs with Excel

Excel solution 1 for Find Max Sum Pairs, proposed 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)))))))
Excel solution 2 for Find Max Sum Pairs, proposed by Bo Rydobon 🇹🇭: 
=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)))))))
Excel solution 3 for Find Max Sum Pairs, proposed by Rick Rothstein: 
=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))))
Excel solution 4 for Find Max Sum Pairs, proposed by Rick Rothstein: 
=MAP(A2:A11,LAMBDA(a,LET(d,", ",b,TEXTSPLIT(a,,d),c,VSTACK(DROP(b,1),0),j,IF(bDROP(VSTACK(0,c&d&b),-1))*(b+c=MAX(c+b))))))))
Excel solution 5 for Find Max Sum Pairs, proposed by John V.: 
=MAP(A2:A10,LAMBDA(x,LET(s,", ",n,TEXTSPLIT(x,,s),m,DROP(VSTACK(n,0),1),TEXTJOIN("; ",,UNIQUE(FILTER(IF(n
Excel solution 6 for Find Max Sum Pairs, proposed 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)))))))
Excel solution 7 for Find Max Sum Pairs, proposed 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))))))
Excel solution 8 for Find Max Sum Pairs, proposed by Julian Poeltl: 
=MAP(A2:A10,LAMBDA(N,LET(SP,TEXTSPLIT(N,", "),C,IFERROR(SP+DROP(SP,,1),0),R,FILTER(SP&", "&DROP(SP,,1),C=MAX(C)),A,MAP(R,LAMBDA(A,TEXTJOIN(",",,SORT(TEXTSPLIT(A,", "),,,1)))),X,XMATCH(A,A)=SEQUENCE(,COLUMNS(A)),TEXTJOIN("; ",,FILTER(R,X)))))
Excel solution 9 for Find Max Sum Pairs, proposed by Timothée BLIOT: 
=MAP(A2:A10, LAMBDA(z, LET(A, TEXTSPLIT(z,", "), B, TOROW(VSTACK(MAP(SEQUENCE(COLUMNS(A)-1), LAMBDA(x, INDEX(A,,x)+INDEX(A,,x+1) )),{0})),
C, MAP(SEQUENCE(,COLUMNS(B)), LAMBDA(y, IF(INDEX(B,,y)=MAX(B), ARRAYTOTEXT(SORT(VSTACK(INDEX(A,,y),INDEX(A,,y+1)),,-1) ),"") )),
TEXTJOIN("; ",1,UNIQUE(TOCOL(FILTER(C,C<>"")) )))))
Excel solution 10 for Find Max Sum Pairs, proposed by Hussein SATOUR: 
=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 solution 11 for Find Max Sum Pairs, proposed 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
 )
 )
)
Excel solution 12 for Find Max Sum Pairs, proposed 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)))
 )
 )
)
Excel solution 13 for Find Max Sum Pairs, proposed 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))))
 )
 )
)
Excel solution 14 for Find Max Sum Pairs, proposed by Md. Zohurul Islam: 
=MAP(A2:A10,LAMBDA(x,LET(
p,TEXTSPLIT(x,,", ")+0,
sq,SEQUENCE(COUNT(p)-1)+1,
q,UNIQUE(DROP(REDUCE("",sq,LAMBDA(x,y,LET(a,SORT(TAKE(TAKE(p,y),-2)),b,SUM(a),c,HSTACK(ARRAYTOTEXT(a),b),VSTACK(x,c)))),1)),
mx,MAX(DROP(q,,1)),
s,TEXTJOIN("; ",,FILTER(TAKE(q,,1),DROP(q,,1)=mx)),
s)))
Excel solution 15 for Find Max Sum Pairs, proposed by Charles Roldan: 
=MAP(
    A2:A10,
     LAMBDA(
         Numbers,
          LET(
              Array,
               --TEXTSPLIT(
                   Numbers,
                    ", "
               ),
               
              PairMatrix,
               INDEX(
                   Array,
                    SEQUENCE(
                        COUNTA(
                            Array
                        ) - 1
                    ) + {0,
                   1}
               ),
               
              PairSum,
               MMULT(
                   PairMatrix,
                    {1;1}
               ),
               TEXTJOIN(
                   "; ",
                    ,
                    UNIQUE(
                        FILTER(
                            BYROW(
                                PairMatrix,
                                 LAMBDA(
                                     x,
                                      TEXTJOIN(
                                          ", ",
                                           ,
                                           x
                                      )
                                 )
                            ),
                             
                            PairSum = MAX(
                                PairSum
                            )
                        )
                    )
               )
          )
     )
)
Excel solution 16 for Find Max Sum Pairs, proposed by Charles Roldan: 
=MAP(A2:A10, LAMBDA(Numbers, LET(Array, --TEXTSPLIT(Numbers, ", "), 
PairMatrix, INDEX(Array, SEQUENCE(COUNTA(Array) - 1) + {0,1}), 
PairSum, MMULT(PairMatrix, {1;1}), TEXTJOIN("; ", , UNIQUE(FILTER(BYROW(PairMatrix, LAMBDA(x, TEXTJOIN(", ", , x))), 
PairSum = MAX(PairSum)))))))
Excel solution 17 for Find Max Sum Pairs, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=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<>"")))))))
Excel solution 18 for Find Max Sum Pairs, proposed by Julien Lacaze: 
=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))))
Excel solution 19 for Find Max Sum Pairs, proposed by Stevenson Yu: 
=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))))

Solving the challenge of Find Max Sum Pairs with Python in Excel

Python in Excel solution 1 for Find Max Sum Pairs, proposed by Alejandro Campos: 
df = xl("A1:A10", headers=True)
df['Processed'] = df['Numbers'].apply(lambda s: "; ".join(
sorted({f"{a}, {b}" for a, b in zip(n, np.append(n[1:], 0)) if a + b == np.max(
n + np.append(n[1:], 0))})) if len(n := np.array(
list(map(int, s.split(', '))))) > 0 else "")
df

&&

Leave a Reply