amcgaughey1
asked on
Excel 2010- Group by Common Fields and Sum into Flat Tabulation with Unique Rows
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
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
Code to create a Table (assumes that header labels exist and that cell A3 is in the data or header labels):
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.
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!
PTautomationQ28125161.xlsm
Sub CreateTable()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3").CurrentRegion, , xlYes).Name = "tbSource"
End Sub
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
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
BradPTautomationQ28125161.xlsm
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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