Solved

Excel 2010- Group by Common Fields and Sum into Flat Tabulation with Unique Rows

Posted on 2013-05-11
6
243 Views
Last Modified: 2014-05-23
In Excel 2010 I need to have an automated method to produce a consolidated table of unique rows with sums on a number from another table that has many common columns (fields) but different quantities in the number field. A simple example with 2 fields to group by and 1 field to sum is as follows.

Source Tabulation
Row 1:    Knives            Kitchen Goods               6
Row 2:    Rags               Household Goods         4
Row 3:    Knives            Kitchen Goods               8
Row 4:    Rags               Household Goods         3

Target Tabulation
Row 1:    Knives            Kitchen Goods               14
Row 2:    Rags               Household Goods         7

Thank you,
Allen
0
Comment
Question by:amcgaughey1
  • 3
6 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39158131
Allen,
What you are requesting is exactly the type of report that a PivotTable produces.

Although you can automate the production of a PivotTable, it is best if you first put your source data in a Table. In so doing, your PivotTable source will automatically include any added data. You'll still need to refresh the PivotTable to see the revised counts from new data, but that is a simple manual step (right-click the PivotTable, then choose Refresh). The refresh can also be automated if necessary.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 39158185
Code to create a Table (assumes that header labels exist and that cell A3 is in the data or header labels):
Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3").CurrentRegion, , xlYes).Name = "tbSource"
End Sub

Open in new window


Code to create a PivotTable on a new worksheet. Macro assumes that the active sheet contains a Table that will become the source of data for the PivotTable. It also assumes that the desired columns of information are first, second and third in that Table--but you can change that assumption in the commented section of the code.
Sub PivotTableCreator()
    Dim PTname As String, sGood As String, sType As String, sQuantity As String, TableName As String
    Dim lst As ListObject
    With ActiveSheet
        Set lst = .ListObjects(1)
        TableName = lst.Name                                'Name of Table
        sGood = lst.HeaderRowRange.Cells(1, 1).Value        'Name of the good is found in first column of Table
        sType = lst.HeaderRowRange.Cells(1, 2).Value        'Name of type of goods is found in second column of Table
        sQuantity = lst.HeaderRowRange.Cells(1, 3).Value    'Quantity of goods is found in third column of Table
        PTname = "PivotTable1"                              'Name of PivotTable to be created
    End With
    
    Sheets.Add after:=ActiveWorkbook.Worksheets(ActiveSheet.Index)
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=TableName).CreatePivotTable _
        TableDestination:=ActiveSheet.Name & "!R3C1", TableName:=PTname
    With ActiveSheet.PivotTables(PTname)
        With .PivotFields(sGood)
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields(sType)
            .Orientation = xlRowField
            .Position = 2
        End With
        .AddDataField .PivotFields(sQuantity), "Sum of " & sQuantity, xlSum
        With .PivotFields(sGood)
            .LayoutForm = xlTabular
            .RepeatLabels = True
            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        End With
        .ColumnGrand = False
    End With
End Sub

Open in new window


Code to automatically refresh a PivotTable when the worksheet containing it is activated. This code must be installed on the code pane for the worksheet containing the PivotTable. It won't work at all if installed anywhere else!
Private Sub Worksheet_Activate()
Dim PT As PivotTable
For Each PT In Me.PivotTables
    PT.RefreshTable
Next
End Sub

Open in new window

Brad
PTautomationQ28125161.xlsm
0
 

Author Comment

by:amcgaughey1
ID: 39159204
Thank you,
This soution works well for this small and specific tabulation, but it looks as if the code would need to be revised for tables that have additional columns.
I need the solution to be able to work on any size tabulation and incorporate changes to the column headers such as revising the name of 'Goods' to 'Category'.
Thoughts?
Allen
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39159479
As posted, the code doesn't care how many columns of data you have in your source. Nor does it care what the header labels are on those columns. You didn't post a sample workbook, so I left comments in the code where you would need to make changes

You need to specify, however, either by name or number which columns are going to be used for the PivotTable. As written, the code assumes column numbers 1, 2 and 3 (the second index in .Cells in the three statements below. You will need to change those numbers to match your actual source data.
        sGood = lst.HeaderRowRange.Cells(1, 1).Value        'Name of the good is found in first column of Table
        sType = lst.HeaderRowRange.Cells(1, 2).Value        'Name of type of goods is found in second column of Table
        sQuantity = lst.HeaderRowRange.Cells(1, 3).Value    'Quantity of goods is found in third column of Table

Open in new window


Alternatively, you could specify the exact text of the exact header labels that you want to use. Then you would use that text in those same statements like this:
        sGood = "Goods"        'Exact text of header label for column containing goods
        sType = "Type"         'Exact text of header label for column containing type
        sQuantity = "Quantity"    'Exact text of header label for column containing quantity

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question