Group and aggregate data

[Pinaki Chatterjee]

I have a table in excel. The table has dimension as Country, City, Zip code and sales person, I want group by them by in row by sales value and QTY and in the column the Year and corresponding period should reflect. Values in the cells will be sum of rev by period and then again sum of qty by period. Also each segmentation should have sub total.

 

[Kamolga]

I have a table in excel. The table has dimension as Country, City, Zip code and sales person, I want group by them by in row by sales value and QTY and in the column the Year and corresponding period should reflect. Values in the cells will be sum of rev by period and then again sum of qty by period. Also each segmentation should have sub total.

You can do that with a pivot table and I time slicer.

 

[Pinaki Chatterjee]

I want to do it my VBA. Do you have any sample code to share where a flat table become cross tab by multi layer dimensions by column and row.

 

[Kamolga]

In VBA I increment a value through loops within loops or multiple conditions

Dim Qty as long: Qty=0
Dim dCell as Range

For each dCell in Range("Table1[sales person]")
if dCell="John" then                                                                    'you can set up "John" as a dynamic value and add conditions
   Qty=Qty+dcell.offset(0,1).value                                                'adds the quantity if the column is 1 column after sales person
End if
Next dCell

but it is way much quicker (always try to avoid loops for speed execution in VBA) to 'group by' with PowerQuery and only refresh the table through VBA

 

[Kamolga]

Or I have been doing something through a class module for a simulation but building arrays is easier.

Otherwise you can convert your table to a range, sort it and use subtotals

ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort.SortFields. _
        Add2 Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort.SortFields. _
        Add2 Key:=Range("SalesTable[Country]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort.SortFields. _
        Add2 Key:=Range("SalesTable[City]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort.SortFields. _
        Add2 Key:=Range("SalesTable[Sales Person]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("SalesTable").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True

.

My favorite option is power query because it produces a table (and I can put dynamic parameters) and just refresh from VBA but you can also create the table through VBA

ActiveWorkbook.Queries.Add Name:="SalesTable", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""SalesTable""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""City"", type text}, {""Country"", type text}, {""Sales Person"", type text}, {""Qty"", Int64.Type}, {""Total"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"" = Table.Group(#""Changed Type"", {""Country"", ""City"", ""Sales Person""}, {{""" & _
        "Quantity"", each List.Sum([Qty]), type number}, {""Sales Revenue"", each List.Sum([Total]), type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SalesTable;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SalesTable]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SalesTable_2"
        .Refresh BackgroundQuery:=False
    End With

 

[Pinaki Chatterjee]

Thanks Kamoga, I shall explore power query option. But can you share a excel with similar type of looping.

 

[Pinaki Chatterjee]

Of course if possible

 

[Pinaki Chatterjee]

Hello Kamolga, I have tried the class module but getting error. I am attaching a format for the output and also mentioned the column in table and the column name. Can you see how can I automate the same from a table downloaded from legacy system.