none
Looking for Suggestions RRS feed

  • Question

  • Hello -

    I am looking to see if anyone might have a suggestion or two on how to accomplish something. I have a column containing some data that I would like to possibly summarize and total.

    In the screen shot below, the column containing "Property Code" will contain various data, usually a 3 digit code. The codes may be duplicated, or not. Also to the right is a column, "Total" , that contains a dollar amount.

    What I am looking to do is summarize all of the duplicated codes, and then total the amounts associated with those codes. I think to start with it would be best to maybe group and total these on a separate sheet; a summary sheet if you would.

    The sheet would reflect the total amount(s) for code "ma1", then the next code "mv3", etc...if there is only one code (not duplicated), this code would also need to reflect on the summary sheet with the amount totaling for that code.

    I thought of a Pivot Table, but can't figure out how to incorporate the totals into the pivot table?

    Anyhow, I hope I explained good enough. If anyone might have some ideas on how/if this can be accomplished, your assistance will be greatly appreciated. Should you need further clarification, please let me know.

    Below is a screenshot of the data and layout. The highlighted sections and totals reflecting to the right in red are for display/example purposes only. The red totals do not actually do anything...just text ;-)

    Thanks in advance for your time and consideration :-)

     

    Wednesday, July 25, 2018 9:45 PM

Answers

  • Hi GMY_LFE and thank you for your response. Your example is exactly what I was looking for. The example actually showed me where my problem lied. It dawned on me after viewing your example, that all three of my columns displayed, actually contained merged cells...several of them. This was throwing everything off and making it difficult to bring in the totals.

    So with a little reformatting of the form, I was able to isolate these columns into single columns which enabled me to create the pivot table I was looking for.

    That being said, I took things a little further with the below :-) All is well again. Works like a charm!

    Again, thank you for your quick response. Your time is much appreciated.

    Here is my final solution (that can probably be s a little streamlined, but works great!):

    Option Explicit

    Public Sub CreatePivot()

        Application.DisplayAlerts = False
        
        On Error Resume Next

        Worksheets("PropSums").Delete
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = "PropSums"
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Mileage Report!R11C8:R26C9", Version:=6).CreatePivotTable _
            TableDestination:="PropSums!R1C1", TableName:="PivotTable", DefaultVersion _
            :=6
        Sheets("PropSums").Select
        Cells(3, 1).Select
                With ActiveSheet.PivotTables("PivotTable")
                    .ColumnGrand = True
                    .HasAutoFormat = True
                    .DisplayErrorString = False
                    .DisplayNullString = True
                    .EnableDrilldown = True
                    .ErrorString = ""
                    .MergeLabels = False
                    .NullString = ""
                    .PageFieldOrder = 2
                    .PageFieldWrapCount = 0
                    .PreserveFormatting = True
                    .RowGrand = True
                    .SaveData = True
                    .PrintTitles = False
                    .RepeatItemsOnEachPrintedPage = True
                    .TotalsAnnotation = False
                    .CompactRowIndent = 1
                    .InGridDropZones = False
                    .DisplayFieldCaptions = True
                    .DisplayMemberPropertyTooltips = False
                    .DisplayContextTooltips = True
                    .ShowDrillIndicators = True
                    .PrintDrillIndicators = False
                    .AllowMultipleFilters = False
                    .SortUsingCustomLists = True
                    .FieldListSortAscending = False
                    .ShowValuesRow = False
                    .CalculatedMembersInFilters = False
                    .RowAxisLayout xlCompactRow
                    .CompactLayoutRowHeader = "Property"
                End With
                With ActiveSheet.PivotTables("PivotTable").PivotCache
                    .RefreshOnFileOpen = False
                    .MissingItemsLimit = xlMissingItemsDefault
                End With
        ActiveSheet.PivotTables("PivotTable").RepeatAllLabels xlRepeatLabels
                With ActiveSheet.PivotTables("PivotTable").PivotFields("CODE")
                    .Orientation = xlRowField
                    .Position = 1
                End With
        ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
            "PivotTable").PivotFields("Total"), "Sum of Total", xlSum
                With ActiveSheet.PivotTables("PivotTable").PivotFields("Sum of Total")
                    .NumberFormat = "#,##0.00"
                End With
                
        ActiveSheet.Range("D2").Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
               "'" & Sheet1.Name & "'!" & Cells(2, 4).Address, TextToDisplay:="Back <<"
        
        ActiveWindow.DisplayGridlines = False
        Cells.Select
        Selection.Locked = True
        ActiveSheet.Range("C1").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Sheets("Mileage Report").Select
        
    End Sub

    • Marked as answer by rstreets2 Thursday, July 26, 2018 9:40 PM
    Thursday, July 26, 2018 9:40 PM

All replies

  • Do you want to get a PivotTable as shown in the following picture?

    If yes, you can refer to this picture and add fields to corresponding area:

    If my understanding is incorrect please provide a sample about the result you need.

    Thursday, July 26, 2018 7:27 AM
  • Hi GMY_LFE and thank you for your response. Your example is exactly what I was looking for. The example actually showed me where my problem lied. It dawned on me after viewing your example, that all three of my columns displayed, actually contained merged cells...several of them. This was throwing everything off and making it difficult to bring in the totals.

    So with a little reformatting of the form, I was able to isolate these columns into single columns which enabled me to create the pivot table I was looking for.

    That being said, I took things a little further with the below :-) All is well again. Works like a charm!

    Again, thank you for your quick response. Your time is much appreciated.

    Here is my final solution (that can probably be s a little streamlined, but works great!):

    Option Explicit

    Public Sub CreatePivot()

        Application.DisplayAlerts = False
        
        On Error Resume Next

        Worksheets("PropSums").Delete
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = "PropSums"
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Mileage Report!R11C8:R26C9", Version:=6).CreatePivotTable _
            TableDestination:="PropSums!R1C1", TableName:="PivotTable", DefaultVersion _
            :=6
        Sheets("PropSums").Select
        Cells(3, 1).Select
                With ActiveSheet.PivotTables("PivotTable")
                    .ColumnGrand = True
                    .HasAutoFormat = True
                    .DisplayErrorString = False
                    .DisplayNullString = True
                    .EnableDrilldown = True
                    .ErrorString = ""
                    .MergeLabels = False
                    .NullString = ""
                    .PageFieldOrder = 2
                    .PageFieldWrapCount = 0
                    .PreserveFormatting = True
                    .RowGrand = True
                    .SaveData = True
                    .PrintTitles = False
                    .RepeatItemsOnEachPrintedPage = True
                    .TotalsAnnotation = False
                    .CompactRowIndent = 1
                    .InGridDropZones = False
                    .DisplayFieldCaptions = True
                    .DisplayMemberPropertyTooltips = False
                    .DisplayContextTooltips = True
                    .ShowDrillIndicators = True
                    .PrintDrillIndicators = False
                    .AllowMultipleFilters = False
                    .SortUsingCustomLists = True
                    .FieldListSortAscending = False
                    .ShowValuesRow = False
                    .CalculatedMembersInFilters = False
                    .RowAxisLayout xlCompactRow
                    .CompactLayoutRowHeader = "Property"
                End With
                With ActiveSheet.PivotTables("PivotTable").PivotCache
                    .RefreshOnFileOpen = False
                    .MissingItemsLimit = xlMissingItemsDefault
                End With
        ActiveSheet.PivotTables("PivotTable").RepeatAllLabels xlRepeatLabels
                With ActiveSheet.PivotTables("PivotTable").PivotFields("CODE")
                    .Orientation = xlRowField
                    .Position = 1
                End With
        ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables( _
            "PivotTable").PivotFields("Total"), "Sum of Total", xlSum
                With ActiveSheet.PivotTables("PivotTable").PivotFields("Sum of Total")
                    .NumberFormat = "#,##0.00"
                End With
                
        ActiveSheet.Range("D2").Select
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
               "'" & Sheet1.Name & "'!" & Cells(2, 4).Address, TextToDisplay:="Back <<"
        
        ActiveWindow.DisplayGridlines = False
        Cells.Select
        Selection.Locked = True
        ActiveSheet.Range("C1").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Sheets("Mileage Report").Select
        
    End Sub

    • Marked as answer by rstreets2 Thursday, July 26, 2018 9:40 PM
    Thursday, July 26, 2018 9:40 PM