Home » Top Headers by Total

Top Headers by Total

First row is column headers and first column is row headers. In the grid, B2:P16, find the row headers / column headers where the total is maximum. You need to list down top 3. Highest total is 1027 which is for c11. Second highest is 1005 which is for r2. Third highest is 996 which is for r7 as well as c5.

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

Solving the challenge of Top Headers by Total with Power Query

Power Query solution 1 for Top Headers by Total, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Tab = Table.FromColumns(
    {
      List.Transform(Table.ToRows(Source), each List.Sum(List.Skip(_)))
        & List.Transform(List.Skip(Table.ToColumns(Source)), List.Sum), 
      Source[Column1] & List.Skip(Table.ColumnNames(Source))
    }, 
    {"Total", "H"}
  ), 
  Ans = Table.ReorderColumns(
    Table.FirstN(
      Table.Sort(Table.Group(Tab, "Total", {"Header", each Text.Combine([H], ", ")}), {"Total", 1}), 
      3
    ), 
    {"Header", "Total"}
  )
in
  Ans
Power Query solution 2 for Top Headers by Total, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Prep = List.Transform(
    Table.ToRows(Source) & List.Skip(Table.ToColumns(Table.DemoteHeaders(Source))), 
    each {_{0}, List.Sum(List.Skip(_))}
  ), 
  S = Table.FromRows(
    List.Transform(
      List.FirstN(List.Sort(List.Distinct(List.Transform(Prep, each _{1})), 1), 3), 
      each {Text.Combine(List.Transform(List.Select(Prep, (p) => p{1} = _), (h) => h{0}), ", "), _}
    ), 
    {"Headers", "Total"}
  )
in
  S
Power Query solution 3 for Top Headers by Total, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Cols = List.Transform(List.Skip(Table.ToColumns(Source)), each {_{0}} & {List.Sum(List.Skip(_))}), 
  Rows = List.Transform(List.Skip(Table.ToRows(Source)), each {_{0}} & {List.Sum(List.Skip(_))}), 
  Tabla = Table.FromRows(Cols & Rows, {"Headers", "Total"}), 
  Sol = Table.Sort(
    Table.Group(
      Table.SelectRows(Tabla, each [Total] >= List.Last(List.MaxN(List.Distinct(Tabla[Total]), 3))), 
      {"Total"}, 
      {{"Headers", each Text.Combine([Headers], ", ")}}
    )[[Headers], [Total]], 
    {"Total", Order.Descending}
  )
in
  Sol
Power Query solution 4 for Top Headers by Total, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  col = [
    col_a = List.Transform(List.RemoveFirstN(Table.ToColumns(Fonte), 1), each List.Sum(_)), 
    col_b = List.RemoveFirstN(Table.ColumnNames(Fonte)), 
    col_c = List.Zip({col_b, col_a}), 
    col_d = List.Select(
      col_c, 
      each List.ContainsAny({_{1}}, List.MaxN(List.Transform(col_c, each _{1}), 3))
    ), 
    lin_a = List.Transform(Table.ToRows(Fonte), each List.Sum(List.RemoveFirstN(_, 1))), 
    lin_b = Table.ToColumns(Fonte){0}, 
    lin_c = List.Zip({lin_b, lin_a}), 
    lin_d = List.Select(
      lin_c, 
      each List.ContainsAny({_{1}}, List.MaxN(List.Transform(lin_c, each _{1}), 3))
    ), 
    a = Table.FromRows(List.Combine({col_d, lin_d}))
  ][a], 
  max = List.MaxN(col[Column2], 3), 
  fil = Table.SelectRows(col, each List.Contains(max, [Column2])), 
  rs = Table.Sort(
    Table.Group(fil, {"Column2"}, {{"Contagem", each Text.Combine(_[Column1], ", ")}})[
      [Contagem], 
      [Column2]
    ], 
    {"Column2", Order.Descending}
  )
in
  rs

Solving the challenge of Top Headers by Total with Excel

Excel solution 1 for Top Headers by Total, proposed by Bo Rydobon 🇹🇭: 
=LET(z,B2:P16,m,MMULT(VSTACK(z,TRANSPOSE(z)),ROW(z)^0),REDUCE(R2:S2,LARGE(UNIQUE(m),{1;2;3}),LAMBDA(a,v,
VSTACK(a,HSTACK(ARRAYTOTEXT(FILTER(VSTACK(A2:A16,TOCOL(B1:P1)),m=v)),v)))))
Excel solution 2 for Top Headers by Total, proposed by Rick Rothstein: 
=LET(g,VSTACK(A2:P16,TRANSPOSE(B1:P16)),s,SORT(HSTACK(TAKE(g,,1),BYROW(g,LAMBDA(r,SUM(r)))),2,-1),v,TAKE(s,,-1),f,FILTER(s,v>=CHOOSEROWS(v,3)),f)
Excel solution 3 for Top Headers by Total, proposed by John V.: 
=LET(n,B2:P16,b,MMULT(VSTACK(n,TRANSPOSE(n)),ROW(n)^0),TAKE(SORT(HSTACK(MAP(b,LAMBDA(x,TEXTJOIN(", ",,REPT(VSTACK(A2:A16,TOCOL(B1:P1)),b=x)))),b),2,-1),3))
Excel solution 4 for Top Headers by Total, proposed by محمد حلمي: 
=LET(
b,B2:P16,
m,MMULT(B2:P2^0,HSTACK(TRANSPOSE(b),b)),
i,LARGE(m,{1;2;3}),
HSTACK(
BYROW(REPT(HSTACK(TOROW(A2:A16),B1:P1),i=m),
LAMBDA(a,TEXTJOIN(", ",,a))),i))
Excel solution 5 for Top Headers by Total, proposed by Julian Poeltl: 
=LET(Q,TOCOL(HSTACK(BYCOL(B2:P16,LAMBDA(A,SUM(A))),BYROW(B2:P16,LAMBDA(A,SUM(A)))),3),H,TOCOL(HSTACK(B1:P1,A2:A16),3),REDUCE(HSTACK("Headers","Total"),LARGE(Q,SEQUENCE(3)),LAMBDA(A,B,VSTACK(A,HSTACK(TEXTJOIN(", ",,FILTER(H,Q=B)),B)))))
Excel solution 6 for Top Headers by Total, proposed by Timothée BLIOT: 
=LET(A,VSTACK(DROP(A1:P16,1),DROP(TRANSPOSE(A1:P16),1)),
B,BYROW(DROP(A,,1),LAMBDA(x,SUM(x))),D,SORT(FILTER(HSTACK(TAKE(A,,1),B),B>=LARGE(B,3)),2,-1),E,MAP(UNIQUE(DROP(D,,1)),LAMBDA(y,ARRAYTOTEXT(FILTER(TAKE(D,,1),DROP(D,,1)=y)))),HSTACK(E,UNIQUE(DROP(D,,1))))
Excel solution 7 for Top Headers by Total, proposed by Hussein SATOUR: 
=LET(m,B2:P16,c,B1:P1,r,A2:A16,
a, VSTACK(BYROW(m, LAMBDA(x, SUM(x))), TOCOL(BYCOL(m, LAMBDA(x, SUM(x))))),
b, LARGE(UNIQUE(a), 4),
d, FILTER(VSTACK(r, TOCOL(c)), a > b),
e, FILTER(a, a > b),
f, SORT(UNIQUE(e),1,-1),
HSTACK(MAP(f, LAMBDA(x, TEXTJOIN(", ",, FILTER(d, e = x)))), f))
Excel solution 8 for Top Headers by Total, proposed by Oscar Mendez Roca Farell: 
=LET(_d, B2:P16,_f, BYROW(_d,LAMBDA(r,SUM(r))),_c, BYCOL(_d,LAMBDA(c,SUM(c))),_s, SEQUENCE(ROWS(_d)),_m, VSTACK(HSTACK("r"&_s,_f), HSTACK("c"&_s, TOCOL(_c))),_n, INDEX(_m,,2), REDUCE({"Headers","Total"}, LARGE(_n,{1, 2, 3}), LAMBDA(i, x, LET(_f,FILTER(_m,_n=x), VSTACK(i, HSTACK(ARRAYTOTEXT(INDEX(_f, ,1)), UNIQUE(INDEX(_f, ,2))))))))
Excel solution 9 for Top Headers by Total, proposed by Sunny Baggu: 
=LET(
 _r, SEQUENCE(ROWS(A2:A16)),
 _c, SEQUENCE(COLUMNS(B1:P1)),
 _rsum, MAP(_r, LAMBDA(r, SUM(CHOOSEROWS(B2:P16, r)))),
 _csum, MAP(_c, LAMBDA(c, SUM(CHOOSECOLS(B2:P16, c)))),
 _col1, VSTACK(A2:A16, TOCOL(B1:P1)),
 _col2, VSTACK(_rsum, _csum),
 _val3, LARGE(_col2, SEQUENCE(3)),
 MAP(_val3, LAMBDA(x, ARRAYTOTEXT(FILTER(_col1, _col2 = x))))
)
Excel solution 10 for Top Headers by Total, proposed by Sunny Baggu: 
=LET(
 _col, BYCOL(
 SEQUENCE(, COLUMNS(B2:P16)),
 LAMBDA(c, SUM(CHOOSECOLS(B2:P16, c)))
 ),
 _row, BYROW(
 SEQUENCE(ROWS(B2:P16)),
 LAMBDA(r, SUM(CHOOSEROWS(B2:P16, r)))
 ),
 _val, LARGE(VSTACK(_row, TOCOL(_col)), SEQUENCE(3)),
 _c, XLOOKUP(_val, _col, B1:P1, ""),
 _r, XLOOKUP(_val, _row, A2:A16, ""),
 MAP(_c, _r, LAMBDA(a, b, TEXTJOIN(", ", TRUE, a, b)))
)
Excel solution 11 for Top Headers by Total, proposed by Sunny Baggu: 
=LET(
 _rows, "r" & SEQUENCE(ROWS(A2:A16)),
 _vrow, BYROW(B2:P16, LAMBDA(r, SUM(r))),
 _cols, TOCOL("c" & SEQUENCE(, COLUMNS(B1:P1))),
 _vcol, TOCOL(BYCOL(B2:P16, LAMBDA(c, SUM(c)))),
 _tbl, SORT(VSTACK(HSTACK(_rows, _vrow), HSTACK(_cols, _vcol)), 2, -1),
 _val3, AGGREGATE(14, , TAKE(_tbl, , -1), 3),
 _val, UNIQUE(TAKE(FILTER(_tbl, TAKE(_tbl, , -1) >= _val3), , -1)),
 DROP(
 REDUCE("", _val, LAMBDA(a, v, VSTACK(a, ARRAYTOTEXT(FILTER(TAKE(_tbl, , 1), TAKE(_tbl, , -1) = v))))),
 1
 )
)
Excel solution 12 for Top Headers by Total, proposed by Pieter de B.: 
=LET(a,B2:P16,s,ROW(a)^0,b,VSTACK(MMULT(a,s),MMULT(TRANSPOSE(a),s)),f,SORT(UNIQUE(FILTER(b,b>=LARGE(b,3))),,-1),HSTACK(MAP(f,LAMBDA(x,ARRAYTOTEXT(TOCOL(IF(1/(b=x),VSTACK(A2:A16,TOCOL(B1:P1))),3)))),f))

else:
=LET(a,B2:P17,r,SEQUENCE(ROWS(a)),c,SEQUENCE(COLUMNS(a)),b,VSTACK(MMULT(a,c^0),MMULT(TRANSPOSE(a),r^0)),f,SORT(UNIQUE(FILTER(b,b>=LARGE(b,3))),,-1),HSTACK(MAP(f,LAMBDA(x,ARRAYTOTEXT(TOCOL(IF(1/(b=x),VSTACK("r"&r,"c"&c)),3)))),f))
Excel solution 13 for Top Headers by Total, proposed by Charles Roldan: 
=LET(Data, LAMBDA(x, HSTACK(DROP(TRANSPOSE(x), , 1), DROP(x, , 1)))(A1:P16), Totals, BYCOL(DROP(Data, 1), LAMBDA(x, SUM(x))), 
Tops, LARGE(UNIQUE(Totals, 1), SEQUENCE(3)), 
_Who, LAMBDA(n, ARRAYTOTEXT(FILTER(TAKE(Data, 1), Totals = n))), 
HSTACK(MAP(Tops, _Who), Tops))
Excel solution 14 for Top Headers by Total, proposed by JvdV –: 
=LET(v,ROW(1:15),x,B2:P16,y,MMULT(VSTACK(x,TRANSPOSE(x)),v^0),z,TAKE(UNIQUE(SORT(y,,-1)),3),HSTACK(MAP(z,LAMBDA(q,TEXTJOIN(", ",,FILTER(TOCOL({"r","c"}&v,,1),y=q)))),z))
Excel solution 15 for Top Headers by Total, proposed by Nicolas Micot: 
=LET(values;B2:P16;colHeaders;TRANSPOSE(B1:P1);rowHeaders;A2:A16;colTot;TRANSPOSE(BYCOL(values;LAMBDA(val;SOMME(val))));rowTot;BYROW(values;LAMBDA(val;SOMME(val)));headers;ASSEMB.V(rowHeaders;colHeaders);totals;ASSEMB.V(rowTot;colTot);sortedTotals;TRIER(UNIQUE(totals);;-1);filteredHeaders;BYROW(sortedTotals;LAMBDA(sT;JOINDRE.TEXTE(", ";VRAI;FILTRE(headers;totals=sT;""))));headersAndTotals;ASSEMB.H(filteredHeaders;sortedTotals);CHOISIRLIGNES(headersAndTotals;SEQUENCE(3)))
Excel solution 16 for Top Headers by Total, proposed by Ziad A.: 
=SORTN({A2:A,BYROW(B2:P,LAMBDA(r,SUM(r)));TRANSPOSE({B1:P1;BYCOL(B2:P,LAMBDA(c,SUM(c)))})},3,3,2,)
Excel solution 17 for Top Headers by Total, proposed by Abhishek Kumar Jain: 
=LET(a,TOCOL(B1:P1),b,A2:A16,c,TOCOL(BYCOL(B2:P16,LAMBDA(x,SUM(x)))),d,BYROW(B2:P16,LAMBDA(x,SUM(x))),e,SORT(HSTACK(VSTACK(a,b),VSTACK(c,d)),2,-1),FILTER(e,INDEX(e,,2)>=LARGE(UNIQUE(INDEX(e,,2)),3)))
Excel solution 18 for Top Headers by Total, proposed by Daniel Garzia: 
=LET(d,B2:P16,v,VSTACK(BYROW(d,LAMBDA(r,SUM(r))),TRANSPOSE(BYCOL(d,LAMBDA(x,SUM(x))))),t,SORT(UNIQUE(FILTER(v,v>LARGE(UNIQUE(v),4))),,-1),HSTACK(MAP(t,LAMBDA(l,TEXTJOIN(", ",,FILTER(VSTACK(A2:A16,TRANSPOSE(B1:P1)),v=l)))),t))
Excel solution 19 for Top Headers by Total, proposed by Quadri Olayinka Atharu: 
=LET(a,B2:P16,
_totals,VSTACK(BYROW(a,LAMBDA(x,SUM(x))),
TOCOL(BYCOL(a,LAMBDA(x,SUM(x))))),
_h,VSTACK(A2:A16,TOCOL(B1:P1)),
_uT,UNIQUE(_totals),
_uH,MAP(_uT,LAMBDA(x,TEXTJOIN(", ",1,FILTER(_h,_totals=x)))),
_r,TAKE(SORT(HSTACK(_uH,_uT),2,-1),3),
VSTACK({"Headers","Total"},_r))
Excel solution 20 for Top Headers by Total, proposed by Quadri Olayinka Atharu: 
=LET(a,B2:P16,
rTotal,BYROW(a,LAMBDA(x,SUM(x))),
cTotal,BYCOL(a,LAMBDA(x,SUM(x))),
_r,A2:A16,_c,B1:P1,
_h,VSTACK(_r,TOCOL(_c)),
_totals,VSTACK(rTotal,TOCOL(cTotal)),
_top3,LARGE(_totals,SEQUENCE(3)),
_t,MAP(_top3,LAMBDA(y,TEXTJOIN(", ",1,FILTER(_h,_totals=y)))),
VSTACK({"Headers","Total"},HSTACK(_t,_top3)))
Excel solution 21 for Top Headers by Total, proposed by Anup Kumar: 
=LET(rng,B2:P16,rhd,A2:A16,chd,TRANSPOSE(B1:P1),
rwS,BYROW(rng,LAMBDA(rw,SUM(rw))),
clS,TOCOL(BYCOL(rng,LAMBDA(cl,SUM(cl)))),
allS,VSTACK(HSTACK(rhd,rwS),HSTACK(chd,clS)),
topN,LARGE(TAKE(allS,,-1),{1;2;3}),
HSTACK(BYROW(topN,LAMBDA(num,ARRAYTOTEXT(FILTER(TAKE(allS,,1),DROP(allS,,1)=num)))),topN)
)
Excel solution 22 for Top Headers by Total, proposed by Amr Tawfik CMA®,FMVA,Lean Coach: 
=TAKE(SORT(HSTACK(VSTACK(A2:A16,TOCOL(B1:P1)),VSTACK(BYROW(B2:P16,LAMBDA(x,SUM(x))),TOCOL(BYCOL(B2:P16,LAMBDA(x,SUM(x)))))),2,-1),4,2)

&&&

Leave a Reply