Home » Max Call Duration Pair

Max Call Duration Pair

Work our the pair of persons for whom the sum of call duration is the highest.

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

Solving the challenge of Max Call Duration Pair with Power Query

Power Query solution 1 for Max Call Duration Pair, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Table.FromRows(
      List.Transform(Table.ToRows(Source), each List.Sort(List.FirstN(_, 2)) & List.LastN(_, 1)), 
      Table.ColumnNames(Source)
    ), 
    {"Person1", "Person2"}, 
    {"Duration", each List.Sum([Duration])}
  ), 
  Ans = Table.SelectRows(Group, each [Duration] = List.Max(Group[Duration]))
in
  Ans
Power Query solution 2 for Max Call Duration Pair, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = List.Accumulate(
    List.Sort(List.Transform(Table.ToRows(Source), List.Sort), {each _{1}, each _{2}}), 
    {}, 
    (s, c) =>
      let
        l = List.Last(s, {"", "", 0})
      in
        if l{0} = c{1} and l{1} = c{2} then
          List.RemoveLastN(s) & {{c{1}, c{2}, c{0} + l{2}}}
        else
          s & {{c{1}, c{2}, c{0}}}
  ), 
  S = Table.FromRows(
    List.Select(T, each _{2} = List.Max(List.Zip(T){2})), 
    Table.ColumnNames(Source)
  )
in
  S
Power Query solution 3 for Max Call Duration Pair, proposed by Zoran Milokanović: 
let
  Source = Table.Sort(Excel.CurrentWorkbook(){[Name = "Input"]}[Content], {C, "Person1"}), 
  C = each Text.Combine(List.Sort({[Person1], [Person2]}, 0)), 
  G = Table.Group(
    Source, 
    {"Person1", "Person2"}, 
    {{"Duration", each List.Sum([Duration])}}, 
    0, 
    (c, n) => Number.From(C(n) <> C(c))
  ), 
  S = Table.SelectRows(G, each [Duration] = List.Max(G[Duration]))
in
  S
Power Query solution 4 for Max Call Duration Pair, proposed by Kris Jaganah: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Combine = Table.AddColumn(
    Source, 
    "Combine", 
    each if [Person1] < [Person2] then [Person1] & [Person2] else [Person2] & [Person1]
  ), 
  Group = Table.Group(Combine, {"Combine"}, {{"Duration", each List.Sum([Duration]), type number}}), 
  Filter = Table.SelectRows(Group, each [Duration] = List.Max(Group[Duration])), 
  Split = Table.SplitColumn(
    Filter, 
    "Combine", 
    Splitter.SplitTextByRepeatedLengths(1), 
    {"Person1", "Person2"}
  )
in
  Split
Power Query solution 5 for Max Call Duration Pair, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sort = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        a = List.Sort({[Person1]} & {[Person2]}), 
        b = Table.FromRows({a}, List.RemoveLastN(Table.ColumnNames(Source)))
      in
        b
  )[[Duration], [Custom]], 
  Expand = Table.ExpandTableColumn(Sort, "Custom", Table.ColumnNames(Sort[Custom]{0})), 
  Group = Table.Group(Expand, {"Person1", "Person2"}, {{"Duration", each List.Sum([Duration])}}), 
  Sol = Table.SelectRows(Group, each ([Duration] = List.Max(Group[Duration])))
in
  Sol
Power Query solution 6 for Max Call Duration Pair, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  st = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each Text.Combine(List.Sort(List.FirstN(Record.FieldValues(_), 2)), "|")
  ), 
  dv = Table.SplitColumn(
    st, 
    "Personalizar", 
    Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
    {"P1", "P2"}
  ), 
  gp = Table.Group(dv, {"P1", "P2"}, {{"Duration", each List.Sum([Duration]), type number}}), 
  res = Table.SelectRows(gp, each ([Duration] = List.Max(gp[Duration])))
in
  res
Power Query solution 7 for Max Call Duration Pair, proposed by Ramiro Ayala Chávez: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = Table.AddColumn(
    Origen, 
    "a", 
    each 
      if Character.ToNumber([Person1]) <= Character.ToNumber([Person2]) then
        [Person1] & [Person2]
      else
        [Person2] & [Person1]
  )[[a], [Duration]], 
  b = Table.Group(a, {"a"}, {{"Duration", each List.Sum([Duration])}}), 
  c = Table.SelectRows(b, each [Duration] = List.Max(b[Duration])), 
  Sol = Table.SplitColumn(c, "a", Splitter.SplitTextByRepeatedLengths(1), {"Person1", "Person2"})
in
  Sol
Power Query solution 8 for Max Call Duration Pair, proposed by Rafael González B.: 
let
  Source = Excel.CurrentWorkbook(){0}[Content], 
  Comb = Table.AddColumn(Source, "Pair", each Text.Combine({[Person1], [Person2]}, ""), type text), 
  TT = Table.TransformColumns(
    Comb, 
    {
      "Pair", 
      each 
        let
          a = Text.ToList(_), 
          b = List.Sort(a), 
          c = Text.Combine(b, "-")
        in
          c
    }
  ), 
  GB = Table.Group(
    TT, 
    {"Pair"}, 
    {
      {
        "Duration", 
        each _, 
        type table [Person1 = text, Person2 = text, Duration = number, Pair = text]
      }
    }
  ), 
  Sum = Table.TransformColumns(
    GB, 
    {
      "Duration", 
      each 
        let
          m = _[Duration], 
          n = List.Sum(m)
        in
          n
    }
  ), 
  Filt = Table.SelectRows(Sum, each [Duration] = List.Max(Sum[Duration])), 
  Result = Table.SplitColumn(
    Filt, 
    "Pair", 
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), 
    {"Person1", "Person2"}
  )
in
  Result
Power Query solution 9 for Max Call Duration Pair, proposed by Luke Jarych: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddKey = Table.AddColumn(
    Source, 
    "Key", 
    each Text.From(List.Max({[Person1], [Person2]}) & List.Min({[Person1], [Person2]}))
  ), 
  GroupedTable = Table.Group(
    AddKey, 
    {"Key"}, 
    {{"SumDuration", each List.Sum([Duration]), Int64.Type}}
  ), 
  a = List.Max(GroupedTable[SumDuration]), 
  Result = Table.SelectRows(GroupedTable, each [SumDuration] = a)
in
  Result
Power Query solution 10 for Max Call Duration Pair, proposed by Szabolcs Phraner: 
let
  Source = Excel.CurrentWorkbook(){[Name = "table"]}[Content], 
  Persons = Table.AddColumn(
    Source, 
    "Person", 
    each Text.Combine(List.Sort({[Person1], [Person2]}), " - "), 
    type text
  ), 
  Group = Table.Group(
    Persons, 
    {"Person"}, 
    {{"Duration", each List.Sum([Duration]), type nullable number}}
  ), 
  Split = Table.SplitColumn(
    Group, 
    "Person", 
    Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, false), 
    {"Person1", "Person2"}
  ), 
  SelectMax = Table.SelectRows(
    Split, 
    each 
      let
        max = List.Max(Split[Duration])
      in
        [Duration] = max
  )
in
  SelectMax

Solving the challenge of Max Call Duration Pair with Excel

Excel solution 1 for Max Call Duration Pair, proposed by Bo Rydobon 🇹🇭: 
=LET(a,
    A2:A13,
    b,
    B2:B13,
    p,
    IF(
        a
Excel solution 2 for Max Call Duration Pair, proposed by Rick Rothstein: 
=LET(
    p,
    MAP(
        A2:A13,
        B2:B13,
        LAMBDA(
            a,
            b,
            IF(
                a
Excel solution 3 for Max Call Duration Pair, proposed by John V.: 
=LET(a,
    A2:A13,
    b,
    B2:B13,
    o,
    HSTACK(
        IF(
            a
Excel solution 4 for Max Call Duration Pair, proposed by محمد حلمي: 
=LET(
i,BYROW(A2:B13,LAMBDA(a,CONCAT(SORT(a,,,1)&"-"))),
u,UNIQUE(i),x,MMULT(N(u=TOROW(i)),C2:C13),m,MAX(x),
TEXTSPLIT(CONCAT(FILTER(u,x=m)&m&"/"),"-","/",1))
Excel solution 5 for Max Call Duration Pair, proposed by Kris Jaganah: 
=LET(a,A2:B13,b,C2:C13,c,BYROW(a,LAMBDA(x,CONCAT(SORT(x,,,1)))),d,UNIQUE(c),e,MAP(d,LAMBDA(x,SUM((x=c)*b))),FILTER(HSTACK(LEFT(d),RIGHT(d),e),e=MAX(e)))
Excel solution 6 for Max Call Duration Pair, proposed by Kris Jaganah: 
=LET(a,
    A2:A13,
    b,
    B2:B13,
    c,
    C2:C13,
    d,
    IF(
        a
Excel solution 7 for Max Call Duration Pair, proposed by Timothée BLIOT: 
=LET(A,A2:A13,B,B2:B13,D,C2:C13,E,MAP(A,B,LAMBDA(x,y,CONCAT(SORT(VSTACK(x,y))))),F,MAP(E,LAMBDA(x,SUM(FILTER(D,E=x)))),UNIQUE(FILTER(HSTACK(A,B,F),F=MAX(F)*(LEFT(A)=LEFT(E)))))
Excel solution 8 for Max Call Duration Pair, proposed by Hussein SATOUR: 
=LET(a,
     A2:A13,
     b,
     B2:B13,
     c,
     MAP(a&b,
     LAMBDA(x,
     SUM(FILTER(C2:C13,
     (a&b=x) + (b&a=x))))),
     e,
     UNIQUE(
         REDUCE(
             ,
              UNIQUE(
                  FILTER(
                      a&b,
                       c = MAX(
                           c
                       )
                  )
              ),
              LAMBDA(
                  y,
                  z,
                   VSTACK(
                       y,
                        IF(
                            RIGHT(
                                z
                            )&LEFT(
                                z
                            ) = TAKE(
                                y,
                                 -1
                            ),
                             "",
                             z
                        )
                   )
              )
         ),
         ,
         1
     ),
     IFNA(
         HSTACK(
             LEFT(
                 e
             ),
              RIGHT(
                 e
             ),
              MAX(
                           c
                       )
         ),
          MAX(
                           c
                       )
     ))
Excel solution 9 for Max Call Duration Pair, proposed by Oscar Mendez Roca Farell: 
=LET(
    _p,
    A2:A13,
    _q,
    B2:B13,
    _m,
    MAP(
        _p,
        _q,
        C2:C13,
         LAMBDA(
             a,
              b,
              c,
              SUM(
                  MMULT(
                      N(
                          A13:a&B13:b=IF(
                              {1,
                               0},
                               a&b,
                               b&a
                          )
                      ),
                      {1;1}
                  )*C13:c
              )
         )
    ),
     FILTER(
         HSTACK(
             _p,
             _q,
             _m
         ),
         _m=MAX(
             _m
         )
     )
)
Excel solution 10 for Max Call Duration Pair, proposed by Sunny Baggu: 
=LET(
 _comb,
     DROP(
         
          REDUCE(
              
               "",
              
               SEQUENCE(
                   ROWS(
                       A2:A13
                   )
               ),
              
               LAMBDA(
                   a,
                    v,
                    VSTACK(
                        a,
                         ARRAYTOTEXT(
                             SORT(
                                 INDEX(
                                     A2:B13,
                                      v,
                                      
                                 ),
                                  ,
                                  ,
                                  1
                             )
                         )
                    )
               )
               
          ),
         
          1
          
     ),
    
 _ucomb,
     UNIQUE(
         _comb
     ),
    
 _sum,
     BYROW(_ucomb,
     LAMBDA(a,
     SUM((_comb = a) * (C2:C13)))),
    
 _max,
     MAX(
         _sum
     ),
    
 _fill,
     FILTER(
         _ucomb,
          _sum = _max
     ),
    
 HSTACK(
     TEXTSPLIT(
         TEXTJOIN(
             "-",
              ,
              _fill
         ),
          ",",
          "-"
     ),
      IF(
          SEQUENCE(
              ROWS(
                  _fill
              )
          ),
           _max
      )
 )
)
Excel solution 11 for Max Call Duration Pair, proposed by 🇵🇪 Ned Navarrete C.: 
=LET(
_a;A2:A13;
_b;B2:B13;
_c;C2:C13;
_d; IF(_a<_b;_a&_b;_b&_a);
_du; UNIQUE(_d);
_e; BYROW(_du; LAMBDA(_f; SUM(FILTER(_c;_d=_f))));
_i; FILTER(_du; _e=MAX(_e));
HSTACK( MID(_i;{1,2};1); FILTER(_e; ISNUMBER(MATCH(_du;_i;0))) )
)
Excel solution 12 for Max Call Duration Pair, proposed by Charles Roldan: 
=LET(
 Headers,
     A1:C1,
    
 Person1,
     A2:A13,
    
 Person2,
     B2:B13,
    
 Duration,
     C2:C13,
    
 Pair,
     IF(
         Person1 < Person2,
          
         Person1 & Person2,
          Perso&n2 & Person1
     ),
    
 Total,
     MMULT(--(Pair = TOROW(
         Pair
     )),
     Duration),
    
 Output,
     UNIQUE(
         FILTER(
             HSTACK(
                 
                 LEFT(
         Pair
     ),
                  RIGHT(
         Pair
     ),
                  Total
             ),
              Total = MAX(
                  Total
              )
         )
     ),
    
 VSTACK(
     Headers,
      Output
 )
)
Excel solution 13 for Max Call Duration Pair, proposed by Pieter de Bruijn: 
=LET(p,A2:B13,r,ROW(p)-1,c,N(TAKE(p,,1)>DROP(p,,1)),o,INDEX(p,r,c+1),t,INDEX(p,r,MOD(c+1,2)+1),ot,o&t,u,UNIQUE(ot),d,MMULT(N(TOROW(ot)=u),C2:C13),FILTER(HSTACK(UNIQUE(HSTACK(o,t)),d),d=MAX(d)))
Excel solution 14 for Max Call Duration Pair, proposed by Giorgi Goderdzishvili: 
=LET(
pr,
    A3:B14,
    
dr,
    C3:C14,
    
sr,
     BYROW(
         pr,
         LAMBDA(
             x,
             CONCAT(
                 SORT(
                     x,
                     1,
                     1,
                     TRUE
                 )
             )
         )
     ),
    
un,
     UNIQUE(
         sr
     ),
    
sm,
    MAP(un,
    LAMBDA(x,
     SUM((x=sr)*(dr)))),
    
flt,
     FILTER(
         un,
          sm=MAX(
              sm
          )
     ),
    
IFERROR(
    HSTACK(
        MID(
            flt,
            SEQUENCE(
                ,
                2,
                
            ),
            1
        ),
        MAX(
              sm
          )
    ),
    MAX(
              sm
          )
))
Excel solution 15 for Max Call Duration Pair, proposed by Daniel Garzia: 
=LET(l,MAP(A2:A13&B2:B13,LAMBDA(x,CONCAT(SORT(MID(x,ROW(1:2),1))))),v,MAP(l,LAMBDA(r,SUM((l=r)*C2:C13))),UNIQUE(FILTER(HSTACK(LEFT(l),RIGHT(l),v),v=MAX(v))))
Excel solution 16 for Max Call Duration Pair, proposed by Anup Kumar: 
=LET(
pairs,
     BYROW(
         A2:B13,
         LAMBDA(
             x,
             CONCAT(
                 SORT(
                     x,
                     ,
                     ,
                     TRUE
                 )
             )
         )
     ),
    
dur,
     SCAN(0,
    pairs,
    LAMBDA(a,
    b,
    SUMPRODUCT(C2:C13*(pairs=b)))),
    
stck,
     HSTACK(
         LEFT(
             pairs,
             1
         ),
         RIGHT(
             pairs,
             1
         ),
         dur
     ),
    
UNIQUE(
    FILTER(
        stck,
        TAKE(
            stck,
            ,
            -1
        )=MAX(
            dur
        )
    )
)
)
Excel solution 17 for Max Call Duration Pair, proposed by samir tobeil: 
=LET(
    a,
    A2:A13,
    b,
    B2:B13,
    c,
    C2:C13,
    x,
    IF(
        a
Excel solution 18 for Max Call Duration Pair, proposed by Amardeep Singh: 
=LET(data, A1:C13,
rng, DROP(data,1,-1),
d, DROP(TAKE(data,,-1),1),
s, BYROW(rng, LAMBDA(x, TEXTJOIN("-",,SORT(x,,,1)))),
u, UNIQUE(s),
sm, BYROW(u,LAMBDA(x,SUM(d*(s=x)))),
r, HSTACK(TEXTBEFORE(u,"-"),TEXTAFTER(u,"-"),sm),
f, FILTER(r,CHOOSECOLS(r,3)=MAX(sm)),
VSTACK(TAKE(data,1),f))
Excel solution 19 for Max Call Duration Pair, proposed by Miguel Angel Franco García: 
=LET(
    a;
    UNICOS(
        A2:B13
    );
    b;
    SUMAR.SI.CONJUNTO(
        C2:C13;
        A2:A13;
        INDICE(
            a;
            ;
            1
        );
        B2:B13;
        INDICE(
            a;
            ;
            2
        )
    );
    c;
    K.ESIMO.MAYOR(
        b;
        {1;
        2}
    );
    d;
    APILARH(
        a;
        b
    );
    resul;
    BYROW(
        b;
        LAMBDA(
            x;
            O(
                x=c;
                x=c
            )
        )
    );
    FILTRAR(
        APILARH(
        a;
        b
    );
        resul
    )
)

Solving the challenge of Max Call Duration Pair with Python in Excel

Python in Excel solution 1 for Max Call Duration Pair, proposed by Bo Rydobon 🇹🇭: 
df =xl("A1:C13", headers=True)
s =pd.DataFrame(sorted(a[:2])+[a[-1]] for a in df.values)
s.columns = df.columns
g= s.groupby(['Person1','Person2']).Duration.sum()
g[g==max(g)]
Python in Excel solution 2 for Max Call Duration Pair, proposed by John V.: 
Hi everyone!
d[g] = d[g].apply(lambda x: pd.Series(sorted(x)), axis=1)
d = d.groupby(g).sum().reset_index()
np.vstack([xl("A1:C1"), d[d[2] == d[2].max()]])
Blessings!

Solving the challenge of Max Call Duration Pair with R

R solution 1 for Max Call Duration Pair, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Call Duration.xlsx", range = "A1:C14")
test = read_excel("Call Duration.xlsx", range = "E2:G4")
result = input %>%
 rowwise() %>%
 mutate(vec = list(sort(c(Person1, Person2)))) %>%
 group_by(vec) %>%
 summarise(Duration = sum(Duration)) %>%
 ungroup() %>%
 drop_na() %>%
 mutate(Max = max(Duration),
 Person1 = map_chr(vec, ~.x[1]),
 Person2 = map_chr(vec, ~.x[2])) %>%
 filter(Duration == Max) %>%
 select(Person1, Person2, Duration) %>%
 as_tibble()
identical(result, test)

&&

Leave a Reply