Home » Sum Groups Separated by Blanks

Sum Groups Separated by Blanks

Groups are separated by blank rows in Problem table. You need to generate Result table where Quantity and Amount are sum of the groups.

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

Solving the challenge of Sum Groups Separated by Blanks with Power Query

_x000D_
Power Query solution 1 for Sum Groups Separated by Blanks, proposed by Omid Motamedisedeh: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Custom" = Table.SelectColumns(
    Table.ExpandRecordColumn(
      Table.TransformColumns(
        Table.AddIndexColumn(
          Table.SelectRows(
            Table.Group(
              Table.AddColumn(Source, "New", each [Quantity] = null), 
              {"New"}, 
              {{"Count", each _}}, 
              0
            ), 
            each [New] = false
          ), 
          "Group", 
          1, 
          1, 
          Int64.Type
        ), 
        {
          {"Count", each [Quantit = List.Sum(_[Quantity]), Amount = List.Sum(_[Amount])]}, 
          {"Group", each "Grooup " & Text.From(_)}
        }
      ), 
      "Count", 
      {"Quantit", "Amount"}, 
      {"Quantity", "Amount"}
    ), 
    {"Group", "Quantity", "Amount"}
  )
in
  #"Added Custom"
_x000D_ _x000D_
Power Query solution 2 for Sum Groups Separated by Blanks, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.FromRecords(
    Table.Group(
      Source, 
      "Quantity", 
      {
        "R", 
        each Record.FromList(
          List.Transform(Table.ToColumns(_), List.Sum), 
          Table.ColumnNames(Source)
        )
      }, 
      0, 
      (b, e) => Number.From(e = null)
    )[R]
  ), 
  AGroup = Table.TransformColumns(
    Table.AddIndexColumn(Table.SelectRows(Group, each ([Quantity] <> null)), "Group", 1), 
    {"Group", each "Group " & Text.From(_)}
  ), 
  Reorder = Table.ReorderColumns(
    AGroup, 
    let
      c = Table.ColumnNames(AGroup)
    in
      List.LastN(c, 1) & List.RemoveLastN(c, 1)
  )
in
  Reorder
_x000D_ _x000D_
Power Query solution 3 for Sum Groups Separated by Blanks, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  AddGroup = Table.FromRows(
    List.Select(
      List.Accumulate(
        Table.ToRows(Source), 
        {}, 
        (s, c) =>
          let
            i = if s = {} then 0 else List.Max(List.Transform(s, each _{0}))
          in
            s & {{if c{0} is null then null else if List.Last(s){0}? = null then i + 1 else i} & c}
      ), 
      each _{0} <> null
    ), 
    {"Group"} & Table.ColumnNames(Source)
  ), 
  Solution = Table.Group(
    Table.TransformColumns(AddGroup, {{"Group", each "Group " & Text.From(_)}}), 
    {"Group"}, 
    {{"Quantity", each List.Sum([Quantity])}, {"Amount", each List.Sum([Amount])}}
  )
in
  Solution
_x000D_ _x000D_
Power Query solution 4 for Sum Groups Separated by Blanks, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Group = Table.Group(
    Source, 
    "Quantity", 
    {"R", each [Quantity = List.Sum([Quantity]), Amount = List.Sum([Amount])]}, 
    0, 
    (x, y) => Number.From(y = null)
  ), 
  Select = List.Select(Group[R], each [Quantity] <> null), 
  Output = List.Transform(
    {1 .. List.Count(Select)}, 
    each [Group = Number.ToText(_, "Group 0")] & Select{_ - 1}
  ), 
  Return = Table.FromRecords(Output)
in
  Return
_x000D_ _x000D_
Power Query solution 5 for Sum Groups Separated by Blanks, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Gen = List.Generate(
    () => [x = 0, y = 0], 
    each [y] <= Table.RowCount(Source) - 1, 
    each [x = if Source[Amount]{[y]} = null then [x] + 1 else [x], y = [y] + 1], 
    each [x]
  ), 
  Tabla = Table.SelectRows(
    Table.FromColumns(Table.ToColumns(Source) & {Gen}, Table.ColumnNames(Source) & {"Group"}), 
    each ([Quantity] <> null)
  ), 
  Group = Table.Group(
    Tabla, 
    {"Group"}, 
    {
      {
        "Count", 
        (x) =>
          let
            a = List.Transform(Table.ToColumns(Table.RemoveColumns(x, "Group")), List.Sum), 
            b = Table.FromColumns({{a{0}}, {a{1}}}, List.FirstN(Table.ColumnNames(x), 2))
          in
            b
      }
    }
  )[Count], 
  Tabla2 = Table.FromColumns(
    {List.Transform({1 .. List.Count(Group)}, each "Group " & Text.From(_)), Group}, 
    {"Group", "Col2"}
  ), 
  Sol = Table.ExpandTableColumn(Tabla2, "Col2", Table.ColumnNames(Source))
in
  Sol
_x000D_ _x000D_
Power Query solution 6 for Sum Groups Separated by Blanks, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  Ind = Table.AddIndexColumn(Fonte, "Ind", 1, 1, Int64.Type), 
  add = Table.AddColumn(
    Ind, 
    "Personalizar", 
    each if [Quantity] = null then Ind{[Ind]}[Quantity] else null
  ), 
  pb = Table.FillDown(add, {"Personalizar"}), 
  fil = Table.SelectRows(pb, each ([Quantity] <> null)), 
  gp = Table.Group(
    fil, 
    {"Personalizar"}, 
    {{"Quantity", each List.Sum(_[Quantity])}, {"Amount", each List.Sum(_[Amount])}}
  )[[Quantity], [Amount]], 
  #"in" = Table.AddIndexColumn(gp, "Índice", 1, 1, Int64.Type), 
  res = Table.AddColumn(#"in", "Group", each "Group " & Text.From([Índice]))[
    [Group], 
    [Quantity], 
    [Amount]
  ]
in
  res
_x000D_ _x000D_
Power Query solution 7 for Sum Groups Separated by Blanks, proposed by Alexis Olson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Custom" = Table.AddColumn(Source, "Group", each [Quantity] = null, type logical), 
  #"Grouped Rows" = Table.Group(
    #"Added Custom", 
    {"Group"}, 
    {{"Quantity", each List.Sum([Quantity])}, {"Amount", each List.Sum([Amount])}}, 
    GroupKind.Local
  ), 
  #"Added Index" = Table.AddIndexColumn(
    Table.SelectRows(#"Grouped Rows", each [Quantity] <> null), 
    "Index", 
    1, 
    1, 
    Int64.Type
  ), 
  #"Index to Group" = Table.ReplaceValue(
    #"Added Index", 
    each [Group], 
    each "Group " & Number.ToText([Index]), 
    Replacer.ReplaceValue, 
    {"Group"}
  )
in
  #"Index to Group"
_x000D_ _x000D_
Power Query solution 8 for Sum Groups Separated by Blanks, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddCustom = Table.AddColumn(Source, "Label", each if [Quantity] = null then "Group" else null), 
  Group = Table.Group(
    AddCustom, 
    {"Label"}, 
    {
      {"Count", each Table.RowCount(_), Int64.Type}, 
      {
        "All", 
        each _, 
        type table [
          Quantity = nullable number, 
          Amount = nullable number, 
          Index = number, 
          Label = nullable text
        ]
      }
    }, 
    GroupKind.Local
  ), 
  Filter = Table.SelectRows(Group, each ([Label] = null)), 
  AddIdx = Table.RemoveColumns(
    Table.AddIndexColumn(Filter, "Idx", 1, 1, Int64.Type), 
    {"Label", "Count"}
  ), 
  AddGroup = Table.RemoveColumns(
    Table.AddColumn(
      AddIdx, 
      "Group", 
      each Text.Combine({"Group ", Text.From([Idx], "en-US")}), 
      type text
    ), 
    "Idx"
  ), 
  Expand = Table.ExpandTableColumn(AddGroup, "All", {"Quantity", "Amount"}, {"Quantity", "Amount"}), 
  Reorder = Table.ReorderColumns(Expand, {"Group", "Quantity", "Amount"}), 
  GroupSum = Table.Group(
    Reorder, 
    {"Group"}, 
    {
      {"Quantity", each List.Sum([Quantity]), type nullable number}, 
      {"Amount", each List.Sum([Amount]), type nullable number}
    }
  )
in
  GroupSum
_x000D_ _x000D_
Power Query solution 9 for Sum Groups Separated by Blanks, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData75"]}[Content], 
  Add_IsNull = Table.AddColumn(Source, "IsNull", each [Quantity] = null), 
  Group = Table.Group(Add_IsNull, {"IsNull"}, {"Values", each __fxGroupSum(_)}, GroupKind.Local), 
  __fxGroupSum = (t) =>
    Table.Group(
      t, 
      {"IsNull"}, 
      {
        {"Quantity", each List.Sum([Quantity]), type number}, 
        {"Amount", each List.Sum([Amount]), type number}
      }
    ), 
  FilterRows = Table.RemoveColumns(Table.SelectRows(Group, each ([IsNull] = false)), {"IsNull"}), 
  Add_GroupName = Table.FromColumns({__GrpNames} & Table.ToColumns(FilterRows), {"Group", "Values"}), 
  __GrpNames = List.Transform({1 .. Table.RowCount(FilterRows)}, each "Group" & Text.From(_)), 
  Expand = Table.ExpandTableColumn(Add_GroupName, "Values", {"Quantity", "Amount"})
in
  Expand
_x000D_ _x000D_
Power Query solution 10 for Sum Groups Separated by Blanks, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  getRecs = List.Accumulate(
    Table.ToRecords(Source), 
    {[lastQuant = 0, Group = 0, Quantity = 0, Amount = 0]}, 
    (state, current) =>
      let
        lastR = List.Last(state)
      in
        if lastR[Group] = 0 or (current[Quantity] = null and lastR[lastQuant] <> null) then
          state
            & {
              [
                lastQuant = current[Quantity], 
                Group     = lastR[Group] + 1, 
                Quantity  = current[Quantity], 
                Amount    = current[Amount]
              ]
            }
        else
          List.RemoveLastN(state, 1)
            & {
              [
                lastQuant = current[Quantity], 
                Group     = lastR[Group], 
                Quantity  = List.Sum({lastR[Quantity], current[Quantity]}), 
                Amount    = List.Sum({lastR[Amount], current[Amount]})
              ]
            }
  ), 
  fromRecs = Table.Skip(Table.FromRecords(getRecs, {"Group", "Quantity", "Amount"})), 
  groupName = Table.TransformColumns(fromRecs, {{"Group", each "Group " & Text.From(_)}})
in
  groupName
_x000D_ _x000D_
Power Query solution 11 for Sum Groups Separated by Blanks, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"Quantity"}, 
    {
      {
        "Count", 
        each Table.FromColumns(
          {
            {List.Sum(Table.SelectRows(_, each ([Quantity] <> null))[Quantity])}, 
            {List.Sum(Table.SelectRows(_, each ([Quantity] <> null))[Amount])}
          }, 
          {"Quantity", "Amount"}
        )
      }
    }, 
    0, 
    (c, n) => Number.From(n[Quantity] = null)
  )[[Count]], 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Count", 
    {"Quantity", "Amount"}, 
    {"Quantity", "Amount"}
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Quantity] <> null)), 
  #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Group", 1, 1, Int64.Type), 
  #"Added Prefix" = Table.TransformColumns(
    #"Added Index1", 
    {{"Group", each "Group" & Text.From(_, "en-IN"), type text}}
  ), 
  #"Reordered Columns" = Table.ReorderColumns(#"Added Prefix", {"Group", "Quantity", "Amount"})
in
  #"Reordered Columns"
_x000D_ _x000D_
Power Query solution 12 for Sum Groups Separated by Blanks, proposed by Melissa de Korte: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddIsNull = Table.AddColumn(Source, "IsNull", each [Quantity] = null), 
  GroupRows = Table.SelectRows(
    Table.Group(
      AddIsNull, 
      {"IsNull"}, 
      {
        {"Quantity", each List.Sum([Quantity]), type nullable number}, 
        {"Amount", each List.Sum([Amount]), type nullable number}
      }, 
      GroupKind.Local, 
      (a, b) => if b[IsNull] then 1 else 0
    )[[Quantity], [Amount]], 
    each [Amount] <> null
  ), 
  AddGroup = Table.ReorderColumns(
    Table.ReplaceValue(
      Table.AddIndexColumn(GroupRows, "Group", 1, 1, Int64.Type), 
      each [Group], 
      each "Group " & Text.From([Group]), 
      Replacer.ReplaceValue, 
      {"Group"}
    ), 
    {"Group"} & Table.ColumnNames(Source)
  )
in
  AddGroup
_x000D_ _x000D_
Power Query solution 13 for Sum Groups Separated by Blanks, proposed by Obi E, MPH: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content], 
  #"Added Conditional Column" = Table.AddColumn(
    Source, 
    "Custom", 
    each if [Quantity] = null then null else "Not Null"
  ), 
  #"Grouped Rows" = Table.Group(
    #"Added Conditional Column", 
    {"Custom"}, 
    {
      {"Quantity", each List.Sum([Quantity]), type nullable number}, 
      {"Amount", each List.Sum([Amount]), type nullable number}
    }, 
    GroupKind.Local
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Custom] = "Not Null")), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Filtered Rows", 
    "Not Null", 
    "Group", 
    Replacer.ReplaceText, 
    {"Custom"}
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type), 
  #"Merged Columns" = Table.CombineColumns(
    Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-GB"), 
    {"Custom", "Index"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Group"
  ), 
  #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns", {"Group", "Quantity", "Amount"})
in
  #"Reordered Columns"
_x000D_ _x000D_
Power Query solution 14 for Sum Groups Separated by Blanks, proposed by Gilbert Cadman Appiah-Ofori: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Quantity", Int64.Type}, {"Amount", Int64.Type}}
  ), 
  AddedIndexCol = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  AddedCustomToBeginNull = Table.AddColumn(
    AddedIndexCol, 
    "Custom", 
    each 
      if [Quantity] <> null and AddedIndexCol[Quantity]{[Index] + 1} = null then
        [Index]
      else
        null
  ), 
  FilledUpAddedCustomIndex = Table.FillUp(AddedCustomToBeginNull, {"Custom"}), 
  GroupedRowsPerIndex = Table.Group(
    FilledUpAddedCustomIndex, 
    {"Custom"}, 
    {
      {"Quantity", each List.Sum([Quantity]), type nullable number}, 
      {"Amount", each List.Sum([Amount]), type nullable number}
    }
  ), 
  FilteredNullRowsFromGroups = Table.SelectRows(GroupedRowsPerIndex, each ([Custom] <> null)), 
  AddedIndexToGroups = Table.AddIndexColumn(FilteredNullRowsFromGroups, "Index", 1, 1, Int64.Type), 
  GeneratedGroupNames = Table.AddColumn(
    AddedIndexToGroups, 
    "Group", 
    each "Group " & Number.ToText([Index])
  ), 
  RemovedHelperColumns = Table.SelectColumns(GeneratedGroupNames, {"Group", "Quantity", "Amount"})
in
  RemovedHelperColumns
_x000D_

Solving the challenge of Sum Groups Separated by Blanks with Excel

_x000D_
Excel solution 1 for Sum Groups Separated by Blanks, proposed by Bo Rydobon 🇹🇭: 
=LET(z,TRANSPOSE(SCAN(0,TRANSPOSE(VSTACK(A2:B21)),LAMBDA(a,v,IF(v,a)+v))),y,FILTER(z,TAKE(VSTACK(DROP(z=0,1),0)*z,,1)),
VSTACK(HSTACK("Group",A1:B1),HSTACK("Group "&SEQUENCE(ROWS(y)),y)))
_x000D_ _x000D_
Excel solution 2 for Sum Groups Separated by Blanks, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:B20,q,REDUCE(VSTACK(A1:B1,{0,0}),SEQUENCE(ROWS(z)),LAMBDA(a,n,LET(v,INDEX(z,n,),p,TAKE(a,-1),IF(SUM(v),VSTACK(DROP(a,-1),p+v),IF(SUM(p),VSTACK(a,v),a))))),
HSTACK("Group "&TEXT(SEQUENCE(ROWS(q),,0),"#"),q))
_x000D_ _x000D_
Excel solution 3 for Sum Groups Separated by Blanks, proposed by Rick Rothstein: 
=LET(f,LAMBDA(a,BYROW(0&TEXTSPLIT(CONCAT(a&"+0"),,"+0+0&",1),LAMBDA(r,SUM(0+TEXTSPLIT(@r,"+"))))),VSTACK(HSTACK("Group",A1:B1),HSTACK("Group "&SEQUENCE(COUNT(f(A2:A20))),f(A2:A20),f(B2:B20))))
_x000D_ _x000D_
Excel solution 4 for Sum Groups Separated by Blanks, proposed by Rick Rothstein: 
=LET(fg,LAMBDA(a,TEXTSPLIT(TRIM(SUBSTITUTE(CONCAT(IF(a=""," ",a&"+")),"+ "," "))&0,," ")),fc,LAMBDA(x,BYROW(fg(x),LAMBDA(s,SUM(0+TEXTSPLIT(@s,"+"))))),ra,fc(A2:A20),VSTACK({"Group","Quantity","Amount"},HSTACK("Group"&SEQUENCE(COUNT(ra)),ra,fc(B2:B20))))
_x000D_ _x000D_
Excel solution 5 for Sum Groups Separated by Blanks, proposed by محمد حلمي: 
=0,1),0)*r

/////


rows(v)/2 

2 = Columns 

//////


=LET(r,SCAN(,TOCOL(A2:B21,,1),LAMBDA(a,d,IF(d,a+d,))),v,FILTER(r,VSTACK(DROP(r=0,1),0)*r),n,ROWS(v)/2,VSTACK(HSTACK("Group",A1:B1),HSTACK("Group "&SEQUENCE(n),WRAPCOLS(v,n))))
_x000D_ _x000D_
Excel solution 6 for Sum Groups Separated by Blanks, proposed by محمد حلمي: 
=LET(l,LAMBDA(x,LET(v,SCAN(,x,LAMBDA(a,d,IF(d,a+d))),n,IF(DROP(VSTACK(v,0),1),,v),
FILTER(n,n))),v,l(A2:A20),VSTACK(HSTACK("Group",A1:B1),HSTACK("Group "&SEQUENCE(ROWS(v)),v,l(B2:B20))))
_x000D_ _x000D_
Excel solution 7 for Sum Groups Separated by Blanks, proposed by محمد حلمي: 
=LET(
o,SCAN(0,A2:A20,LAMBDA(a,d,IF(d>0,a,a+1))),
r,DROP(REDUCE(0,UNIQUE(o),LAMBDA(a,d,LET(
v,FILTER(A2:B20,o=d),VSTACK(a,HSTACK(SUM(TAKE(v,,1)),
SUM(DROP(v,,1))))))),1),
u,FILTER(r,TAKE(r,,1)),
VSTACK(HSTACK("Group",A1:B1),
HSTACK("Group "&SEQUENCE(ROWS(u)),u)))
_x000D_ _x000D_
Excel solution 8 for Sum Groups Separated by Blanks, proposed by 🇰🇷 Taeyong Shin: 
=LET(n,SIGN(A2:A20),GROUPBY(TEXT(VSTACK(0,SCAN(0,n>DROP(VSTACK(0,n),-1),SUM)),"!Group #"),A1:B20,SUM,3,0))
_x000D_ _x000D_
Excel solution 9 for Sum Groups Separated by Blanks, proposed by Oscar Mendez Roca Farell: 
=LET(_r, DROP( REDUCE("", SEQUENCE(2), LAMBDA(j, y, HSTACK(j, LET(_m, INDEX(A2:B21, ,y),_n, DROP(_m,-1), FILTER( SCAN(0,_n, LAMBDA(i, x, SUM(i+x)*(x>0))), IFERROR((_n>0)/(DROP(_m,1)=0), )))))), ,1), VSTACK(HSTACK("Group", A1:B1), HSTACK("Group "&SEQUENCE(ROWS(_r)), _r)))
_x000D_ _x000D_
Excel solution 10 for Sum Groups Separated by Blanks, proposed by Duy Tùng: 
=GROUPBY(VSTACK("Group","Group "&SCAN(0,B2:B20,LAMBDA(x,v,IFS(v="",x,AND(OFFSET(v,-1,)<"",v<""),x,1,x+1)))),A1:B20,SUM,3,0)
_x000D_ _x000D_
Excel solution 11 for Sum Groups Separated by Blanks, proposed by Sunny Baggu: 
=LET(
 _input, A2:B20,
 _qty, DROP(_input, , -1),
 _amt, DROP(_input, , 1),
 _e1, LAMBDA(arr, SCAN(0, arr, LAMBDA(a, v, IF(v, a) + v))),
 _runtot, HSTACK(_e1(_qty), _e1(_amt)),
 _cond, N(_qty <> 0),
 _cri, VSTACK(DROP(_cond, 1) < DROP(_cond, -1), 1),
 _res, FILTER(_runtot, _cri),
 VSTACK(HSTACK("Group", A1:B1), HSTACK("Group" & SEQUENCE(ROWS(_res)), _res))
)
_x000D_ _x000D_
Excel solution 12 for Sum Groups Separated by Blanks, proposed by Caroline Blake: 
=LET(a,IF(A2:A20="",";",A2:A20),
b,BYROW(a,LAMBDA(a,CONCAT(a,","))),
c,IFERROR(VALUE(IFNA(TEXTSPLIT(CONCAT(b),",",";"),0)),0),
d,BYROW(c,LAMBDA(c,SUM(c))),
e,IF(B2:B20="",";",B2:B20),
f,BYROW(e,LAMBDA(e,CONCAT(e,","))),
g,IFERROR(VALUE(IFNA(TEXTSPLIT(CONCAT(f),",",";"),0)),0),
h,BYROW(g,LAMBDA(g,SUM(g))),
VSTACK(HSTACK("Group","Quantity","Amount"),HSTACK("Group "&SEQUENCE(5),FILTER(d,d>0),FILTER(h,h>0))))
_x000D_

Solving the challenge of Sum Groups Separated by Blanks with Python in Excel

_x000D_
Python in Excel solution 1 for Sum Groups Separated by Blanks, proposed by Alejandro Campos: 
data = xl("A2:B20").to_numpy()
result = pd.DataFrame(data, columns=["Quantity", "Amount"]).fillna(-1) 
 .assign(Group=lambda df: (df['Quantity'] == -1).cumsum()) 
 .query('Quantity != -1').groupby('Group').sum().reset_index() 
 .assign(Group=lambda df: "Group " + (df.index + 1).astype(str)) 
 [['Group', 'Quantity', 'Amount']]
result
_x000D_ &&

Leave a Reply