Extract Results From Input

Generate the result table from problem table.

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

Solving the challenge of Extract Results From Input with Power Query

Power Query solution 1 for Extract Results From Input, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(
    Table.FillDown(Source, {"Company"}), 
    {"Company", "Dept"}, 
    "A", 
    "V"
  ), 
  RC = Table.TransformColumns(
    Unpivot, 
    {"A", each "Total " & (if Text.Contains(_, "R") then "Revenue" else "Cost")}
  ), 
  Group = Table.Combine(
    Table.Group(
      RC, 
      "Company", 
      {
        "T", 
        each Table.AddColumn(
          Table.Pivot(Table.RemoveColumns(_, "Dept"), List.Distinct([A]), "A", "V", List.Sum), 
          "Total Profit", 
          each [Total Revenue] - [Total Cost]
        )
      }
    )[T]
  ), 
  Total = Table.FromColumns(
    List.Transform(Table.ToColumns(Group), each _ & {try List.Sum(_) otherwise "Grand Total"}), 
    Table.ColumnNames(Group)
  )
in
  Total

Power Query solution 2 for Extract Results From Input, proposed by Zoran Milokanović:

let
  Source = Table.UnpivotOtherColumns(
    Table.FillDown(Excel.CurrentWorkbook(){[Name = "Input"]}[Content], {"Company"}), 
    {"Company", "Dept"}, 
    "A", 
    "V"
  ), 
  Grouping = Table.Group(
    Source, 
    {"Company"}, 
    {
      {"Total Revenue", each List.Sum(List.Alternate(Table.Sort(_, {"Dept", "A"})[V], 1, 1, 0))}, 
      {"Total Cost", each List.Sum(List.Alternate(Table.Sort(_, {"Dept", "A"})[V], 1, 1, 1))}, 
      {
        "Total Profit", 
        each 
          let
            t = Table.Sort(_, {"Dept", "A"})[V]
          in
            List.Sum(
              List.Transform(List.Positions(t), each if Number.Mod(_, 2) = 0 then - t{_} else t{_})
            )
      }
    }
  ), 
  Solution = Table.FromColumns(
    List.Transform(
      Table.ToColumns(Grouping), 
      each _ & {if Value.Is(_{0}, type number) then List.Sum(_) else "Grand Total"}
    ), 
    Table.ColumnNames(Grouping)
  )
in
  Solution

Power Query solution 3 for Extract Results From Input, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fill = Table.FillDown(Source, {"Company"}), 
  Unpivot = Table.UnpivotOtherColumns(Fill, {"Company", "Dept"}, "Attribute", "Value"), 
  Rev_cost = Table.AddColumn(
    Unpivot, 
    "Revenue / Cost", 
    each if Text.Contains([Attribute], "Rev") then "Total Revenue" else "Total Cost"
  ), 
  Group = Table.Group(
    Rev_cost, 
    {"Company", "Revenue / Cost"}, 
    {{"US$", each List.Sum([Value]), type number}}
  ), 
  Index = Table.AddIndexColumn(Group, "Index", 1, 1, Int64.Type), 
  Pivot = Table.Pivot(
    Index, 
    List.Distinct(Index[#"Revenue / Cost"]), 
    "Revenue / Cost", 
    "US$", 
    List.Sum
  ), 
  Up = Table.FillUp(Pivot, {"Total Cost"}), 
  Filter = Table.SelectRows(Up, each ([Total Revenue] <> null)), 
  Profit = Table.AddColumn(Filter, "Total Profit", each [Total Revenue] - [Total Cost]), 
  Cols = Table.ColumnNames(Profit), 
  Group1 = Table.Group(
    Profit, 
    {}, 
    List.Zip(
      {
        Cols, 
        {each "Grand Total"}
          & List.Transform(List.Skip(Cols), (x) => each List.Sum(Table.Column(_, x)))
      }
    )
  ), 
  Append = Table.Combine({Profit, Group1}), 
  #"Sorted Rows" = Table.Sort(Append, {{"Index", Order.Ascending}}), 
  #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"Index"})
in
  #"Removed Columns"

Power Query solution 4 for Extract Results From Input, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fill = Table.FillDown(Source, {"Company"}), 
  Unpivot = Table.UnpivotOtherColumns(Fill, {"Company", "Dept"}, "Attribute", "Value"), 
  Rev_cost = Table.AddColumn(
    Unpivot, 
    "Revenue / Cost", 
    each if Text.Contains([Attribute], "Rev") then "Total Revenue" else "Total Cost"
  ), 
  Group = Table.Group(
    Rev_cost, 
    {"Company", "Revenue / Cost"}, 
    {{"US$", each List.Sum([Value]), type number}}
  ), 
  Pivot = Table.Pivot(
    Group, 
    List.Distinct(Group[#"Revenue / Cost"]), 
    "Revenue / Cost", 
    "US$", 
    List.Sum
  ), 
  Profit = Table.AddColumn(Pivot, "Total Profit", each [Total Revenue] - [Total Cost]), 
  Type = Table.TransformColumnTypes(Profit, {{"Total Profit", type number}}), 
  Sorted = Table.Sort(Type, {{"Total Profit", Order.Descending}}), 
  Cols = Table.ColumnNames(Sorted), 
  Group1 = Table.Group(
    Sorted, 
    {}, 
    List.Zip(
      {
        Cols, 
        {each "Grand Total"}
          & List.Transform(List.Skip(Cols), (x) => each List.Sum(Table.Column(_, x)))
      }
    )
  ), 
  Append = Table.Combine({Sorted, Group1})
in
  Append

Power Query solution 5 for Extract Results From Input, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  FD = Table.FillDown(Source, {"Company"}), 
  Group = Table.Group(
    FD, 
    {"Company"}, 
    {
      {
        "All", 
        each [
          a = Table.RemoveColumns(_, {"Company", "Dept"}), 
          b = Table.SelectColumns(
            a, 
            List.Select(Table.ColumnNames(a), each Text.Contains(_, "Rev"))
          ), 
          c = List.Sum(List.Combine(Table.ToColumns(b))), 
          d = Table.SelectColumns(
            a, 
            List.Select(Table.ColumnNames(a), each Text.Contains(_, "Cost"))
          ), 
          e = List.Sum(List.Combine(Table.ToColumns(d))), 
          f = c - e
        ][[c], [e], [f]]
      }
    }
  ), 
  Expand = Table.ExpandRecordColumn(
    Group, 
    "All", 
    {"c", "e", "f"}, 
    {"Total Revenue", "Total Cost", "Total Profit"}
  ), 
  Sol = Table.FromRows(
    Table.ToRows(Expand)
      & {{"Grand Total"} & List.Transform(List.Skip(Table.ToColumns(Expand)), List.Sum)}, 
    Table.ColumnNames(Expand)
  )
in
  Sol

Power Query solution 6 for Extract Results From Input, proposed by Luan Rodrigues:

let
 Fonte = Tabela1,
 pb = Table.FillDown(Fonte,{"Company"}),
 rev = Table.RemoveColumns(pb,{"Dept"}),
 out = Table.UnpivotOtherColumns(rev, {"Company"}, "Atributo", "Valor"),
 ext = Table.TransformColumns(out, {{"Atributo", each Text.Start(Text.AfterDelimiter(_, " "),3), type text}}),
 sub = Table.ReplaceValue(ext,each [Atributo],
each if [Atributo] = "Rev" then "Total Revenue" else "Total Cost",Replacer.ReplaceText,{"Atributo"}),
 pv = Table.Pivot(sub, List.Distinct(sub[Atributo]), "Atributo", "Valor", List.Sum),
 add = Table.AddColumn(pv, "Total Profit", each [Total Revenue]-[Total Cost]),
 cls = Table.Sort(add,each List.PositionOf(pb[Company],[Company] ) ),
 total = cls & hashtag#table({"Company","Total Revenue","Total Cost","Total Profit"},{{"Gran Total",List.Sum(cls[Total Revenue]),List.Sum(cls[Total Cost]),List.Sum(cls[Total Profit])}})
in
 total

Power Query solution 7 for Extract Results From Input, proposed by Brian Julius:

let
  Source = Table.FillDown(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], {"Company"}), 
  UnpivotOther = Table.RemoveColumns(
    Table.UnpivotOtherColumns(Source, {"Company", "Dept"}, "Attribute", "Value"), 
    "Dept"
  ), 
  Split = Table.SplitColumn(
    UnpivotOther, 
    "Attribute", 
    Splitter.SplitTextByPositions({5}), 
    {"Attribute.1"}
  ), 
  ReplaceRev = Table.ReplaceValue(Split, "Rev", "Revenue", Replacer.ReplaceValue, {"Attribute.1"}), 
  GroupSum = Table.Group(
    ReplaceRev, 
    {"Company", "Attribute.1"}, 
    {{"Total", each List.Sum([Value]), type number}}
  ), 
  Pivot = Table.Pivot(GroupSum, List.Distinct(GroupSum[Attribute.1]), "Attribute.1", "Total"), 
  Rename = Table.RenameColumns(Pivot, {{"Revenue", "Total Revenue"}, {"Cost", "Total Cost"}}), 
  Subtract = Table.AddColumn(
    Rename, 
    "Total Profit", 
    each [Total Revenue] - [Total Cost], 
    type number
  ), 
  UnpivotOther2 = Table.UnpivotOtherColumns(Subtract, {"Company"}, "Attribute", "Value"), 
  GroupSum2 = Table.AddColumn(
    Table.Group(UnpivotOther2, {"Attribute"}, {{"Value", each List.Sum([Value]), type number}}), 
    "Company", 
    each "Grand Total"
  ), 
  Pivot2 = Table.Pivot(GroupSum2, List.Distinct(GroupSum2[Attribute]), "Attribute", "Value"), 
  AppendGT = Table.Combine({Subtract, Pivot2})
in
  AppendGT

Power Query solution 8 for Extract Results From Input, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData85"]}[Content], 
  //-- Sum all table values  
  fxTSum = (t as table) => List.Sum(Table.ToList(t, List.Sum)), 
  //-- Sum all table values from a list of Columns Names only 
  fxTCSum = (t as table, cn as list) => fxTSum(Table.SelectColumns(t, cn)), 
  CN = Table.ColumnNames(Source), 
  CN_Rev = List.Select(CN, each Text.Contains(_, "Rev")), 
  CN_Cost = List.Select(CN, each Text.Contains(_, "Cost")), 
  Fill_Company = Table.FillDown(Source, {"Company"}), 
  Group = Table.Group(
    Fill_Company, 
    {"Company"}, 
    {{"TR", each fxTCSum(_, CN_Rev)}, {"TC", each fxTCSum(_, CN_Cost)}}
  ), 
  Append_GT = Table.Combine(
    {
      Group, 
      Table.FromRecords(
        {[Company = "Grand Total", TR = List.Sum(Group[TR]), TC = List.Sum(Group[TC])]}
      )
    }
  ), 
  Add_TP = Table.AddColumn(Append_GT, "TP", each [TR] - [TC]), 
  Rename = Table.RenameColumns(
    Add_TP, 
    {{"TR", "Total Revenue"}, {"TC", "Total Cost"}, {"TP", "Total Profit"}}
  )
in
  Rename

Power Query solution 9 for Extract Results From Input, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  fillCompany = Table.FillDown(Source, {"Company"}), 
  removeDept = Table.RemoveColumns(fillCompany, {"Dept"}), 
  Unpivot = Table.UnpivotOtherColumns(removeDept, {"Company"}, "Attribute", "Value"), 
  cleanAttribute = Table.TransformColumns(
    Unpivot, 
    {{"Attribute", each Text.Start(Text.AfterDelimiter(_, " "), 3), type text}}
  ), 
  Pivot = Table.Pivot(
    cleanAttribute, 
    List.Distinct(cleanAttribute[Attribute]), 
    "Attribute", 
    "Value", 
    List.Sum
  ), 
  addProfit = Table.AddColumn(Pivot, "Prof", each [Rev] - [Cos]), 
  stupidTotalRow = Table.FromRows(
    Table.ToRows(addProfit)
      & {
        {"Grand Total"}
          & List.Transform(
            List.Skip(Table.ColumnNames(addProfit)), 
            each List.Sum(Table.Column(addProfit, _))
          )
      }, 
    {"Company", "Total Revenue", "Total Cost", "Total Profit"}
  )
in
  stupidTotalRow

Power Query solution 10 for Extract Results From Input, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Filled Down" = Table.FillDown(Source, {"Company"}), 
  #"Removed Columns" = Table.RemoveColumns(#"Filled Down", {"Dept"}), 
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
    #"Removed Columns", 
    {"Company"}, 
    "Attribute", 
    "Value"
  ), 
  #"Extracted Text After Delimiter" = Table.TransformColumns(
    #"Unpivoted Other Columns", 
    {
      {
        "Attribute", 
        each if Text.AfterDelimiter(_, " ") = "Rev" then "Revenue" else Text.AfterDelimiter(_, " ")
      }
    }
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Extracted Text After Delimiter", 
    List.Distinct(#"Extracted Text After Delimiter"[Attribute]), 
    "Attribute", 
    "Value", 
    List.Sum
  ), 
  #"Inserted Subtraction" = Table.AddColumn(
    #"Pivoted Column", 
    "Profit", 
    each [Revenue] - [Cost], 
    type number
  ), 
  Custom2 = Table.ToRows(#"Inserted Subtraction"), 
  Custom1 = Table.Profile(
    #"Inserted Subtraction", 
    {{"New", each true, each try List.Sum(_) otherwise "Total"}}
  )[New], 
  Custom3 = Table.FromRows(Custom2 & {Custom1}, Table.ColumnNames(#"Inserted Subtraction"))
in
  Custom3

Power Query solution 11 for Extract Results From Input, proposed by Gráinne Duggan:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fill = Table.RemoveColumns(Table.FillDown(Source, {"Company"}), {"Dept"}), 
  Unpivot = Table.UnpivotOtherColumns(Fill, {"Company"}, "Attribute", "Value"), 
  RevCost = Table.TransformColumns(
    Unpivot, 
    {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}
  ), 
  ReplcRev = Table.ReplaceValue(RevCost, "Rev", "Revenue", Replacer.ReplaceValue, {"Attribute"}), 
  PivCol = Table.Pivot(ReplcRev, List.Distinct(ReplcRev[Attribute]), "Attribute", "Value", List.Sum), 
  calcProfit = Table.AddColumn(#"PivCol", "Total Profit", each [Revenue] - [Cost], Currency.Type), 
  TotRevCalc = List.Sum(calcProfit[Revenue]), 
  TotCostCalc = List.Sum(calcProfit[Cost]), 
  TotProfitCalc = List.Sum(calcProfit[Total Profit]), 
  TotLineRecord = Record.ToTable(
    Record.FromList(
      {"Grand Total", TotRevCalc, TotCostCalc, TotProfitCalc}, 
      {"Company", "Revenue", "Cost", "Total Profit"}
    )
  ), 
  GrandTot = Table.Pivot(TotLineRecord, List.Distinct(TotLineRecord[Name]), "Name", "Value"), 
  AppendQry = Table.Combine({calcProfit, GrandTot}), 
  Type = Table.TransformColumnTypes(
    AppendQry, 
    {
      {"Company", type text}, 
      {"Revenue", Currency.Type}, 
      {"Cost", Currency.Type}, 
      {"Total Profit", Currency.Type}
    }
  )
in
  Type

Power Query solution 12 for Extract Results From Input, proposed by Roy Wilson:

let
Source=Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FdDrp=
let
FDown=Table.FillDown(Source,{"Company"}),
DpCol=Table.RemoveColumns(FDown,"Dept")
in DpCol,
Piv=let
Unpivoted=Table.UnpivotOtherColumns(FdDrp,{"Company"},"Attribute","Value"),
AddCondCol=Table.AddColumn(Unpivoted,"Type",eachifText.Contains([Attribute],"Revenue")then"Rev"elseifText.Contains([Attribute],"Rev")then"Rev"else"Cost"),
Grp=Table.Group(AddCondCol,{"Company","Type"},{{"Total",eachList.Sum([Value]),typenumber}}),
Repiv=Table.Pivot(Grp,List.Distinct(Grp[Type]),"Type","Total",List.Sum),
SetOrd=Table.Sort(Repiv,{{"Company",Order.Descending}})
in SetOrd,
Totals=
let
TotCol=Table.AddColumn(Piv,"TotalProfit",each[Rev]-[Cost],Int64.Type),
RenameCols=Table.RenameColumns(TotCol,{{"Cost","TotalCost"},{"Rev","TotalRevenue"}}),
typeChange=Table.TransformColumnTypes(RenameCols,{{"TotalRevenue",Int64.Type},{"TotalCost",Int64.Type}}),
ColNmes=Table.ColumnNames(typeChange),
MakeRecord=List.Accumulate(ColNmes,[],(s,c)=>Record.AddField(s,c,tryList.Sum(Table.Column(RenameCols,c))otherwise"Total")),
AddTotRow=Table.InsertRows(typeChange,Table.RowCount(typeChange),{MakeRecord})
in
AddTotRow
in
Totals

Power Query solution 13 for Extract Results From Input, proposed by Serbay K.:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fill = Table.FillDown(Source, {"Company"}), 
  AddRevenue = Table.AddColumn(
    Fill, 
    "Revenue", 
    each List.Sum(
      List.Transform(
        List.Select(Record.FieldNames(_), each Text.Contains(_, "Rev")), 
        (name) => Record.Field(_, name)
      )
    ), 
    type number
  ), 
  AddCost = Table.AddColumn(
    AddRevenue, 
    "Cost", 
    each List.Sum(
      List.Transform(
        List.Select(Record.FieldNames(_), each Text.Contains(_, "Cost")), 
        (name) => Record.Field(_, name)
      )
    ), 
    type number
  ), 
  AddProfit = Table.AddColumn(
    AddCost, 
    "Profit", 
    each try [Revenue] - [Cost] otherwise Error.Record, 
    type number
  ), 
  Group = Table.Group(
    AddProfit, 
    {"Company"}, 
    {
      {"Total Revenue", each List.Sum([Revenue]), type number}, 
      {"Total Cost", each List.Sum([Cost]), type number}, 
      {"Total Profit", each List.Sum([Profit]), type number}
    }
  ), 
  AddTotal = Table.Group(
    Group, 
    {}, 
    {
      {"Company", each "Grand Total"}, 
      {"Total Revenue", each List.Sum([Total Revenue]), type number}, 
      {"Total Cost", each List.Sum([Total Cost]), type number}, 
      {"Total Profit", each List.Sum([Total Profit]), type number}
    }
  ), 
  Output = Table.Combine({Group, AddTotal})
in
  Output

Power Query solution 14 for Extract Results From Input, proposed by Mohand Tahar IGGUI:

let
  Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
  #"Title 1" = Table.TransformColumnTypes(
    Source, 
    {
      {"Company", type text}, 
      {"Dept", type text}, 
      {"2019 Revenue", Int64.Type}, 
      {"2019 Cost", Int64.Type}, 
      {"2020 Cost", Int64.Type}, 
      {"2020 Rev", Int64.Type}, 
      {"2021 Cost", Int64.Type}, 
      {"2021 Rev", Int64.Type}, 
      {"2022 Rev", Int64.Type}, 
      {"2022 Cost", Int64.Type}
    }
  ), 
  #"title 2" = Table.FillDown(#"Title 1", {"Company"}), 
  #"title 3" = Table.RemoveColumns(#"title 2", {"Dept"}), 
  #"title 4" = Table.AddColumn(
    #"title 3", 
    "Total revenue", 
    each List.Sum({[2019 Revenue], [2020 Rev], [2021 Rev], [2022 Rev]}), 
    Int64.Type
  ), 
  #"title 5" = Table.AddColumn(
    #"title 4", 
    "Total Cost", 
    each List.Sum({[2019 Cost], [2020 Cost], [2021 Cost], [2022 Cost]}), 
    Int64.Type
  ), 
  #"title 6" = Table.Group(
    #"title 5", 
    {"Company"}, 
    {
      {"Total Revenue", each List.Sum([Total revenue]), type number}, 
      {"Total Cost", each List.Sum([Total Cost]), type number}
    }
  ), 
  #"title 7" = Table.AddColumn(#"title 6", "Total Profit", each [Total Revenue] - [Total Cost]), 
  #"title 8" = Table.TransformColumnTypes(#"title 7", {{"Total Profit", type number}})
in
  #"title 8"

Solving the challenge of Extract Results From Input with Excel

Excel solution 1 for Extract Results From Input, proposed by Bo Rydobon 🇹🇭:

=LET(a,SCAN(,A2:A15,LAMBDA(a,v,IF(v=0,a,v))),u,UNIQUE(a),r,"R"=TOCOL(MID(C1:J1,6,1)),
HSTACK(VSTACK(A1,u,"Grand Total"),VSTACK("Total "&{"Revenue","Cost","Profit"},
MMULT(MMULT(--IFNA(VSTACK(TOROW(a)=u,1),1),C2:J15),HSTACK(--r,1-r,2*r-1)))))

Excel solution 2 for Extract Results From Input, proposed by Bo Rydobon 🇹🇭:

=LET(a,SCAN(,A2:A15,LAMBDA(a,v,IF(v=0,a,v))),u,UNIQUE(a),
m,MMULT(MMULT(MMULT(N(TOROW(a)=u),C2:J15),N({"R","C"}=TOCOL(MID(C1:J1,6,1)))),{1,0,1;0,1,-1}),
VSTACK(HSTACK(A1,"Total "&{"Revenue","Cost","Profit"}),
HSTACK(u,m),HSTACK("Grand Total",MMULT(TOROW(N(u))+1,m))))

Excel solution 3 for Extract Results From Input, proposed by محمد حلمي:

=LET(
v,SCAN(,A2:A15,LAMBDA(a,b,IF(b="",a,b))),
u,UNIQUE(v),
r,LAMBDA(x,MAP(u,LAMBDA(a,
SUM(TOCOL(IF(FIND(x,C1:J1)*(v=a),C2:J15),2))))),
e,r("e"),
l,r("o"),
w,"Total ",
VSTACK(
HSTACK(A1,w&"Revenue",w&"Cost",w&"Profit"),
HSTACK(u,e,l,e-l),
HSTACK("Grand Total",SUM(e),SUM(l),SUM(e-l))))

Excel solution 4 for Extract Results From Input, proposed by 🇰🇷 Taeyong Shin:

=LET(F,LAMBDA(x,TOCOL(IF(C2:J15,x))),p,PIVOTBY(F(SCAN(,A2:A15,LAMBDA(a,v,IF(v>"",v,a)))),F(REGEXREPLACE(C1:J1,"(d+)|(Rev)$","${1:+Total$2:Revenue}")),F(C2:J15),SUM,,,-1,0,-1),t,MMULT(N(DROP(p,,1)),{1;-1}),HSTACK(p,IF(t,t,"TotalProfit")))

Excel solution 5 for Extract Results From Input, proposed by 🇰🇷 Taeyong Shin:

=LET(
 c, A2:A15,h, C1:J1, n, ROW(c),
 com, XLOOKUP(n, (c <> "") * n, c, , -1), ucom, UNIQUE(com),
 Fx, LAMBDA(x, MMULT(MMULT(--(ucom = TOROW(com)), ISNUMBER(FIND(x, h)) * C2:J15), TOCOL(N(+h) + 1))),
 d, HSTACK(ucom, Fx("R"), Fx("C"), Fx("R") - Fx("C")),
 VSTACK(d, HSTACK("Grand Total", MMULT({1,1,1}, DROP(d, , 1))))
)

Excel solution 6 for Extract Results From Input, proposed by Oscar Mendez Roca Farell:

=LET(_m, MAP(A2:A15, LAMBDA(a, LOOKUP(2, 1/(LEN(A2:a)>1), A2:a))),_r, REDUCE(HSTACK(A1, "Total "&{"Revenue","Cost","Profit"}), UNIQUE(_m), LAMBDA(i, x, VSTACK(i, LET(_s, TOROW(BYROW(IFERROR(FIND({"*R";"*C"}, C1:J1), ), LAMBDA(r, SUM(FILTER(FILTER(C2:J15, _m=x), r))))), HSTACK(x,_s,SUM(_s*{1,-1})))))), VSTACK(_r, IFERROR((1/BYCOL(_r, LAMBDA(c, SUM(c))))^-1, "Grand Total")))

Excel solution 7 for Extract Results From Input, proposed by Sunny Baggu:

=LET(
 _comp, SCAN("", A2:A15, LAMBDA(a, v, IF(LEN(v) > 1, v, a))),
 _ucomp, UNIQUE(_comp),
 _e1, LAMBDA(a, FILTER(C2:J15, ISERR(SEARCH(a, C1:J1)))),
 _e2, LAMBDA(arr,
 MAP(_ucomp, LAMBDA(x, SUM(FILTER(arr, _comp = x))))
 ),
 _totalrev, _e2(_e1("cost")),
 _totalcost, _e2(_e1("rev")),
 _profit, _totalrev - _totalcost,
 VSTACK(
 {"Company", "Total Revenue", "Total Cost", "Total Profit"},
 HSTACK(_ucomp, _totalrev, _totalcost, _profit),
 HSTACK(
 "Grand Total",
 SUM(_totalrev),
 SUM(_totalcost),
 SUM(_profit)
 )
 )
)

Excel solution 8 for Extract Results From Input, proposed by Sunny Baggu:

=LET(
 _fcomp, SCAN("", A1:A15, LAMBDA(a, v, IF(LEN(v) > 1, v, a))),
 _comp, UNIQUE(_fcomp),
 _col2, REDUCE(
 "Total Revenue",
 DROP(_comp, 1),
 LAMBDA(a, v,
 VSTACK(
 a,
 SUM(
 FILTER(
 FILTER(C2:J15, DROP(_fcomp, 1) = v),
 ISNUMBER(SEARCH("rev", C1:J1)) * SEQUENCE(, COLUMNS(C1:J1))
 )
 )
 )
 )
 ),
 _col3, REDUCE(
 "Total Cost",
 DROP(_comp, 1),
 LAMBDA(a, v,
 VSTACK(
 a,
 SUM(
 FILTER(
 FILTER(C2:J15, DROP(_fcomp, 1) = v),
 ISNUMBER(SEARCH("cost", C1:J1)) * SEQUENCE(, COLUMNS(C1:J1))
 )
 )
 )
 )
 ),
 _col4, IFERROR(_col2 - _col3, "Total Profit"),
 _tbl, HSTACK(_comp, _col2, _col3, _col4),
 VSTACK(_tbl, HSTACK("Grand Total", SUM(DROP(_col2, 1)), SUM(DROP(_col3, 1)), SUM(DROP(_col4, 1))))
)

Excel solution 9 for Extract Results From Input, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(t;LET(r;SEQUENCE(COUNTA(B2:B15));UNIQUE(DROP(TEXTSPLIT(CONCAT(MAP(LET(x;UNIQUE(A2:A15);FILTER(x;x<>0));FILTER(r;A2:A15<>0);VSTACK(DROP(FILTER(r;A2:A15<>0);1);MAX(r)+1)-FILTER(r;A2:A15<>0);LAMBDA(a;b;c;TEXTJOIN(";";;SEQUENCE(;c;b)&","&a)&";")));",";";";TRUE;0;"");;1)));HSTACK(t;MAP(t;LAMBDA(p;SUM(IFERROR(MAP(LET(m;FILTER(SEQUENCE(COUNTA(B2:B15));A2:A15<>0);DROP(TEXTSPLIT(CONCAT(MAP(LET(x;UNIQUE(A2:A15);FILTER(x;x<>0));m;LET(r;FILTER(SEQUENCE(COUNTA(B2:B15));A2:A15<>0);VSTACK(DROP(r;1);MAX(SEQUENCE(COUNTA(B2:B15)))+1)-r);LAMBDA(a;b;c;TEXTJOIN(";";;SEQUENCE(;c;b)&","&a)&";")));",";";";TRUE;0;"");;1));TAKE(MAP(C2:J15;LAMBDA(x;SUMIF(C1:J1;"*Rev*";x)));;1);LAMBDA(o;i;XLOOKUP(p;o;i)));""))));MAP(t;LAMBDA(p;SUM(IFERROR(MAP(LET(m;FILTER(SEQUENCE(COUNTA(B2:B15));A2:A15<>0);DROP(TEXTSPLIT(CONCAT(MAP(LET(x;UNIQUE(A2:A15);FILTER(x;x<>0));m;LET(r;FILTER(SEQUENCE(COUNTA(B2:B15));A2:A15<>0);VSTACK(DROP(r;1);MAX(SEQUENCE(COUNTA(B2:B15)))+1)-r);LAMBDA(a;b;c;TEXTJOIN(";";;SEQUENCE(;c;b)&","&a)&";")));",";";";TRUE;0;"");;1));TAKE(MAP(C2:J15;LAMBDA(x;SUMIF(C1:J1;"*Cost*";x)));;1);LAMBDA(o;i;XLOOKUP(p;o;i)));""))))))

Solving the challenge of Extract Results From Input with Python in Excel

Python in Excel solution 1 for Extract Results From Input, proposed by Alejandro Campos:

df = xl("A1:J15", headers=True).ffill()
df['Total Revenue'], df['Total Cost'] = df.filter(like='Rev')
 .sum(1), df.filter(like='Cost').sum(1)
df['Total Profit'] = df['Total Revenue'] - df['Total Cost']
result = df.groupby('Company')[['Total Revenue', 'Total Cost', 'Total Profit']]
 .sum().reset_index()
grand_total = result.sum(numeric_only=True).to_frame().T.assign(Company='Grand Total')
result = pd.concat([result, grand_total], ignore_index=True)
order = {c: i for i, c in enumerate(['MSFT', 'Berkshire', 'Exxon', 'Grand Total'])}
result['Order'] = result['Company'].map(order).fillna(len(order))
result = result.sort_values('Order').drop('Order', axis=1).reset_index(drop=True)
result

Solving the challenge of Extract Results From Input with Power Query

Solving the challenge of Extract Results From Input with Excel

Solving the challenge of Extract Results From Input with Python in Excel

Leave a Reply Cancel reply