Automatically Update Sales Reports with New Data in Excel

Create Qty Group and Sum the Amount Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution

📌 Challenge Details and Links
Challenge Number: 69
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Grouping with Power Query

Power Query solution 1 for Grouping, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = Table.FromRows(
    List.Transform(
      List.Numbers(1, Number.RoundUp(List.Max(A[Qty]) / 5, 0), 5), 
      each {
        Text.From(_) & "-" & Text.From(_ + 4), 
        List.Sum(Table.SelectRows(A, (v) => v[Qty] < _ + 5 and v[Qty] >= _)[Amount])
      }
    ), 
    {"Qty Group", "Amount"}
  )
in
  B

Power Query solution 2 for Grouping, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  Grp = Table.FromRows(
    {{"1-5"}, {"6-10"}, {"11-15"}, {"16-20"}, {"21-25"}, {"26-30"}}, 
    {"Qty Group"}
  ), 
  Sol = Table.AddColumn(
    Grp, 
    "Amount", 
    (x) =>
      let
        a = Origen, 
        b = Table.SelectRows(
          a, 
          each [Qty]
            <= Number.From(List.Last(Text.Split(x[Qty Group], "-"))) and [Qty]
            >= Number.From(Text.Split(x[Qty Group], "-"){0})
        ), 
        c = List.Sum(b[Amount]) ?? 0
      in
        c
  )
in
  Sol

Power Query solution 3 for Grouping, proposed by Luan Rodrigues:

let
  lista = List.Split({1 .. Number.RoundUp(List.Max(Tabela1[Qty]) / 10) * 10}, 5), 
  tab = List.Transform(
    lista, 
    (x) =>
      let
        a = List.Transform(x, Text.From), 
        b = Text.Combine({List.First(a), List.Last(a)}, "-"), 
        c = List.Sum(Table.SelectRows(Tabela1, each List.ContainsAny(x, {[Qty]}))[Amount])
      in
        {b, c}
  ), 
  res = Table.FromRows(tab, {"Qty Group", "Amount"})
in
  res

Power Query solution 4 for Grouping, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddQtyRange = Table.AddColumn(
    Source, 
    "QtyRange", 
    each [
      a = List.Max(Source[Qty]), 
      b = {1 .. a}, 
      d = List.Transform(b, each _ + 4), 
      e = List.Transform(d, each Number.Mod(_, 5)), 
      f = Table.FromColumns({b, d, e}, {"Q", "L", "U"})
    ][f]
  ), 
  QtyRange = AddQtyRange{0}[QtyRange], 
  AddQtyGroup = Table.AddColumn(
    QtyRange, 
    "QtyGroup", 
    each if [U] = 0 then Text.From([Q]) & "-" & Text.From([L]) else null
  ), 
  Fill = Table.FillDown(AddQtyGroup, {"QtyGroup"}), 
  Join = Table.SelectColumns(
    Table.Join(Fill, "Q", Source, "Qty", JoinKind.LeftOuter), 
    {"QtyGroup", "Amount"}
  ), 
  ReplNull = Table.ReplaceValue(Join, null, 0, Replacer.ReplaceValue, {"Amount"}), 
  Group = Table.Group(ReplNull, {"QtyGroup"}, {{"Amount", each List.Sum([Amount])}})
in
  Group

Power Query solution 5 for Grouping, proposed by Seokho MOON:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Res = Table.FromList(
    {0 .. Number.IntegerDivide(List.Max(Source[Qty]) - 1, 5)}, 
    each {
      Text.From(_ * 5 + 1) & "-" & Text.From((_ + 1) * 5), 
      List.Sum(Table.SelectRows(Source, (x) => Number.IntegerDivide(x[Qty] - 1, 5) = _)[Amount])
        ?? 0
    }, 
    {"Qty Group", "Amount"}
  )
in
  Res

Power Query solution 6 for Grouping, proposed by Meganathan Elumalai:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.FromRows(
    List.TransformMany(
      List.Numbers(1, Number.RoundUp(List.Max(Source[Qty]) / 5, 0), 5), 
      (f) => {List.Sum(Table.SelectRows(Source, (x) => x[Qty] >= f and x[Qty] <= f + 4)[Amount])}, 
      (x, y) => {Text.From(x) & "-" & Text.From(x + 4), y}
    ), 
    {"Qty", "Amount"}
  )
in
  Result

Power Query solution 7 for Grouping, proposed by Antriksh Sharma:

let
  Source = Table, 
  Transform = List.TransformMany(
    List.Split({1 .. Number.RoundUp(List.Max(Source[Qty]) / 5) * 5}, 5), 
    (x) => {List.Sum(Table.SelectRows(Source, each List.Contains(x, [Qty]))[Amount])}, 
    (x, y) =>
      Table.FromRows(
        {{Text.From(List.First(x)) & "-" & Text.From(List.Last(x)), y}}, 
        type table [Qty Group = text, Amount = number]
      )
  ), 
  Combine = Table.Combine(Transform)
in
  Combine

Power Query solution 8 for Grouping, proposed by CA Raghunath Gundi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Qty Grp" = Table.FromList(
    {1 .. 30}, 
    Splitter.SplitByNothing(), 
    {"Number"}, 
    null, 
    ExtraValues.Error
  ), 
  Amount = Table.AddColumn(
    #"Qty Grp", 
    "Amount", 
    (a) => List.Sum(Table.SelectRows(Source, each [Qty] = a[Number])[Amount]) ?? 0
  ), 
  Groups = Table.AddColumn(Amount, "Group", each Number.RoundDown(([Number] - 1) / 5)), 
  Result = Table.Group(
    Groups, 
    {"Group"}, 
    {
      {"Qty Group", each Text.From(_[Number]{0}) & "-" & Text.From(_[Number]{4})}, 
      {"Amount", each List.Sum([Amount]), type number}
    }
  )[[Qty Group], [Amount]]
in
  Result

Power Query solution 9 for Grouping, proposed by Zain Shah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  f = (f) =>
    if f <= 5 then
      "1-5"
    else if f <= 10 then
      "6-10"
    else if f <= 15 then
      "11-15"
    else if f <= 20 then
      "16-20"
    else if f <= 25 then
      "21-25"
    else
      "26-30", 
  QtyGroup = Table.AddColumn(Source, "Qty Group", each f([Qty])), 
  Transform = List.Transform(
    {"1-5", "6-10", "11-15", "16-20", "21-25", "26-30"}, 
    each {_, List.Sum(Table.SelectRows(QtyGroup, (x) => _ = x[Qty Group])[Amount])}
  ), 
  Result = Table.FromRows(Transform, {"Qty Group", "Amount"})
in
  Result

Power Query solution 10 for Grouping, proposed by Ramon Barrull:

let
  Inici = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  nRng = 5, 
  maxValue = Number.RoundUp(List.Max(Inici[Qty]) / nRng) * nRng, 
  RngList = List.Transform(
    List.Numbers(1, maxValue / nRng, nRng), 
    each Text.From(_) & "-" & Text.From(_ + nRng - 1)
  ), 
  RngTbl = Table.FromColumns({RngList}, {"Qty Group"}), 
  addColRng = Table.AddColumn(
    Inici, 
    "Rango", 
    each 
      let
        qty = [Qty]
      in
        List.First(
          List.Select(
            RngList, 
            (r) =>
              let
                partes = Text.Split(r, "-"), 
                inicio = Number.FromText(partes{0}), 
                fin    = Number.FromText(partes{1})
              in
                qty >= inicio and qty <= fin
          )
        )
  ), 
  Group = Table.Group(addColRng, {"Rango"}, {{"Amount", each List.Sum([Amount]), type number}}), 
  Join = Table.Join(RngTbl, "Qty Group", Group, "Rango", JoinKind.LeftOuter), 
  Result = Table.ReplaceValue(Join, null, 0, Replacer.ReplaceValue, {"Amount"})[
    [Qty Group], 
    [Amount]
  ]
in
  Result

Solving the challenge of Grouping with Excel

Excel solution 1 for Grouping, proposed by Rick Rothstein:

=BYROW(
   SUMIFS(
       D4:D11,
       C4:C11,
       ">="&{1,
       6,
       11,
       16,
       21,
       26},
       C4:C11,
       "<="&{5;10;16;20;25;30})*MUNIT(
       6),
   SUM)

Excel solution 2 for Grouping, proposed by Kris Jaganah:

=LET(a,
   C4:C11,
   b,
   D4:D11,
   c,
   ROUNDUP(
       MAX(
           a)/5,
       0)*5,
   d,
   SEQUENCE(
       c/5,
       ,
       ,
       5),
   e,
   d+4,
   VSTACK({"Qty Group",
   "Amount"},
   HSTACK(d&"-"&e,
   MAP(d,
   e,
   LAMBDA(x,
   y,
   SUM(b*(a>=x)*(a<=y)))))))

Excel solution 3 for Grouping, proposed by Hussein SATOUR:

=LET(q,
   C4:C11,
   a,
   SEQUENCE(
       MAX(
           q)/5+1,
       ,
       ,
       5),
   HSTACK(a&"-"&a+4,
   MAP(a,
   a+5,
   LAMBDA(x,
   y,
   SUM(FILTER(D4:D11,
   (q>=x)*(q<=y),
   0))))))

Excel solution 4 for Grouping, proposed by Oscar Mendez Roca Farell:

=LET(q,
   C4:C11,
   s,
   SEQUENCE(
       ROUND(
           5+MAX(
               q),
           )/5,
       ,
       ,
       5),
   f,
   s&-s-4,
    HSTACK(f,
   TOCOL(BYCOL(D4:D11*(LOOKUP(
       q,
       s,
       f)=TOROW(
       f)),
   SUM))))

Excel solution 5 for Grouping, proposed by Duy Tùng:

=LET(
   c,
   C4:C11,
   a,
   CEILING(
       SEQUENCE(
           MAX(
               c)),
       5),
   b,
   CEILING(
       c,
       5),
   REDUCE(
       F3:G3,
       UNIQUE(
           a-4&-a),
       LAMBDA(
           x,
           y,
           VSTACK(
               x,
               HSTACK(
                   y,
                   SUM(
                       FILTER(
                           D4:D11,
                           b-4&-b=y,
                           0)))))))

Excel solution 6 for Grouping, proposed by Sunny Baggu:

=LET(
_a,
    SEQUENCE(
        CEILING.MATH(
            MAX(
                C4:C11),
             5) / 5,
         ,
         ,
         5),
   
_b,
    _a + 4,
   
_c,
    MAP(
_a,
   
_b,
   
LAMBDA(a,
    b,
   
SUM((C4:C11 >= a) * (C4:C11 <= b) * D4:D11))),
   
HSTACK(
    _a & "-" & _b,
     _c))

Excel solution 7 for Grouping, proposed by Pieter de B.:

=LET(
   a,
   TAKE,
   x,
   SEQUENCE(
       6,
       ,
       1,
       5),
   y,
   VSTACK(
       x*{1,
       0},
       C4:D11),
   z,
   GROUPBY(
       LOOKUP(
           a(
               y,
               ,
               1),
           x),
       a(
           y,
           ,
           -1),
       SUM,
       ,
       0),
   b,
   a(
       z,
       ,
       1),
   HSTACK(
       b&-b-4,
       a(
           z,
           ,
           -1)))

Excel solution 8 for Grouping, proposed by Hamidi Hamid:

=LET(x,
   SEQUENCE(
       6,
       ,
       1,
       5),
   y,
   x+4,
   HSTACK(x&"-"&y,
   MAP(x,
   y,
   LAMBDA(a,
   b,
   SUM(IF((C4:C11>=a)*(C4:C11<=b),
   D4:D11,
   0))))))

Excel solution 9 for Grouping, proposed by Asheesh Pahwa:

=LET(
   s,
   SEQUENCE(
       CEILING.MATH(
           MAX(
               C4:C11),
           10)),
   m,
   MOD(
       s,
       5),
   d,
   DROP(
       VSTACK(
           0,
           m),
       -1),
   I,
   IFNA(
       XMATCH(
           d,
           0),
       0),
   sc,
   SCAN(
       0,
       I,
       LAMBDA(
           x,
           y,
           x+y)),
   
   u,
   UNIQUE(
       sc),
   xl,
   XLOOKUP(
       s,
       C4:C11,
       D4:D11,
       ""),
   REDUCE(
       F3:G3,
       u,
       LAMBDA(
           x,
           y,
           VSTACK(
               x,
               LET(
                   f,
                   FILTER(
                       HSTACK(
                           s,
                           xl),
                       sc=y),
                   a,
                   SUM(
                       TAKE(
                           f,
                           ,
                           -1)),
                   t,
                   TAKE(
                       f,
                       ,
                       1),
                   HSTACK(
                       TAKE(
                           t,
                           1)&"-"&TAKE(
                           t,
                           -1),
                       a))))))

Excel solution 10 for Grouping, proposed by ferhat CK:

=LET(
   a,
   BYROW(
       IF(
           {1,
           0},
           SEQUENCE(
               6)*5-4,
           SEQUENCE(
               6)*5),
       LAMBDA(
           x,
           TEXTJOIN(
               "-",
               ,
               x))),
   b,
   XLOOKUP(
       C4:C11,
       NUMBERVALUE(
           LEFT(
               a,
               FIND(
                   "-",
                   a)-1)),
       a,
       "",
       -1),
   c,
   PIVOTBY(
       b,
       ,
       D4:D11,
       SUM),
   VSTACK(
       {"Qty Group",
       "Amount"},
       HSTACK(
           a,
           XLOOKUP(
               a,
               TAKE(
                   c,
                   ,
                   1),
               TAKE(
                   c,
                   ,
                   -1),
               0))))

Excel solution 11 for Grouping, proposed by Meganathan Elumalai:

=LET(q,
   C4:C11,
   s,
   SEQUENCE(
       CEILING(
           MAX(
               q),
           5)/5,
       ,
       1,
       5),
   HSTACK(s&-(s+4),
   MAP(s,
   s+4,
   LAMBDA(x,
   y,
   SUM(D4:D11*(q>=x)*(q<=y))))))

Excel solution 12 for Grouping, proposed by CA Raghunath Gundi:

=LET(seq,
   SEQUENCE(
       30),
   xl,
   SUMIFS(
       Table1[Amount],
       Table1[Qty],
       seq),
   grp,
   TAKE(GROUPBY(ROUNDDOWN((seq-1)/5,
   0),
   xl,
   SUM,
   0,
   0),
   ,
   -1),
   qtygrp,
   LET(
       a,
       SEQUENCE(
           6,
           ,
           1,
           5),
       b,
       SEQUENCE(
           6,
           ,
           5,
           5),
       ab,
       a&"-"&b,
       ab),
   HSTACK(
       qtygrp,
       grp))

Excel solution 13 for Grouping, proposed by Mey Tithveasna:

=LET(
   a,
   SEQUENCE(
       6,
       ,
       1,
       5),
   b,
   SEQUENCE(
       6,
       ,
       5,
       5),
   c,
   C4:C11,
   d,
   D4:D11,
   s,
   SUMIFS(
       d,
       c,
       ">="&a,
       c,
       "<="&b),
   res,
   HSTACK(
       a&"-"b,
       s),
   res)

Excel solution 14 for Grouping, proposed by Md. Shah Alam, Microsoft Certified Trainer:

=LET(
   x,
   SEQUENCE(
       6,
       ,
       1,
       5),
   y,
   SEQUENCE(
       6,
       ,
       5,
       5),
   z,
   SUMIFS(
       D4:D11,
       C4:C11,
       ">="&x,
       C4:C11,
       "<="&y),
   HSTACK(
       x&"-"&y,
       z))

Excel solution 15 for Grouping, proposed by abdelaziz allam:

=MAP(F4:F9,
   LAMBDA(a,
   SUM(FILTER(D4:D11,
   (C4:C11>=--TEXTBEFORE(
       a,
       "-"))*(C4:C11<=--TEXTAFTER(
       a,
       "-")),
   0))))

Solving the challenge of Grouping with Python

Python solution 1 for Grouping, proposed by Konrad Gryczan, PhD:

import pandas as pd
import numpy as np
path = "files/Challenge1025.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=2, nrows=9)
test = pd.read_excel(path, usecols="F:G", skiprows=2, nrows=6).rename(columns=lambda x: x.replace('.1', ''))
step = 5
min_qty = np.floor(input["Qty"].min() / step) * step
max_qty = np.ceil(input["Qty"].max() / step) * step
breaks = np.arange(min_qty, max_qty + step, step)
labels = [f"{int(breaks[i-1] + 1)}-{int(breaks[i])}" for i in range(1, len(breaks))]
input["Qty_group"] = pd.cut(input["Qty"], bins=breaks, labels=labels, right=True, include_lowest=True)
result = input.groupby("Qty_group", observed=True)["Amount"].sum().reset_index()
result.rename(columns={"Qty_group": "Qty Group"}, inplace=True)
all_groups = pd.DataFrame({"Qty Group": labels})
r2 = pd.merge(all_groups, result, on="Qty Group", how="left")
r2["Amount"] = r2["Amount"].fillna(0)
r2["Amount"] = r2["Amount"].astype("int64")
print(test.equals(r2)) # True

Python solution 2 for Grouping, proposed by Luan Rodrigues:

import pandas as pd
file = r"Challenge1025.xlsx"
df = pd.read_excel(file,usecols="B:D",skiprows=2,nrows=8)
maxi = list(range(1,(round(df['Qty'].max()/10)*10)+1))
div = [
 ['-'.join(map(str, [maxi[i], maxi[i + 4]])), 
 df[df['Qty'].isin(maxi[i:i + 5])]['Amount'].sum()] 
 for i in range(0, len(maxi), 5)
]
res = pd.DataFrame(div,columns=["Qty Group","Amount"])
print(res)

Solving the challenge of Grouping with Python in Excel

Python in Excel solution 1 for Grouping, proposed by Alejandro Campos:

df = xl("B3:D11", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
df['Qty Group'] = pd.cut(df['Qty'], [0, 5, 10, 15, 20, 25, 30], 
 labels=['1-5', '6-10', '11-15', '16-20', '21-25', '26-30'])
result = df.groupby('Qty Group')['Amount'].sum().reset_index()

Solving the challenge of Grouping with R

R solution 1 for Grouping, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "files/Challenge1025.xlsx"
input = read_excel(path, range = "B3:D11")
test = read_excel(path, range = "F3:G9")
step = 5
min = floor(min(input$Qty) / step) * step
max = ceiling(max(input$Qty)/ step) * step
breaks = seq(min, max, by = step)
labels <- c(
 paste0(breaks[1], "-", breaks[2]),
 map2_chr(breaks[-length(breaks)], breaks[-1], ~ paste0(.x + 1, "-", .y))
) %>%
 .[-1]
result = input %>%
 mutate(Qty = cut(Qty, breaks = breaks, labels = labels)) %>%
 summarise(Amount = sum(Amount), .by = Qty)
 
r2 = tibble(`Qty Group` = labels) %>%
 left_join(result, by = c("Qty Group" = "Qty")) %>%
 replace_na(list(Amount = 0))
all.equal(r2, test, check.attributes = FALSE)
#> [1] TRUE

Solving the challenge of Grouping with Power Query

Solving the challenge of Grouping with Excel

Solving the challenge of Grouping with Python

Solving the challenge of Grouping with Python in Excel

Solving the challenge of Grouping with R

Leave a Reply Cancel reply