If duplicates, count duplicates, match to column and calculate sum

0
votes

sample data:

Column A       Column B       Column C        Column D
Example 1      5              Example 1       13
Example 1      2              Example 2       8
Example 1      4
Example 1      2
Example 2      2
Example 2      4
Example 3      2

bad pseudo:

  • Loops through Column A checking for duplicates
  • If duplicate found, increase duplicate counter and store in array?
  • Then write 'Example #' to Column C
  • Use counter to jump between rows in Column B and sum up values and output in Column D

english:

  • Add up all values for each 'Example #'
  • Output the 'Example #' to specified Column/Row
  • Sum values and output to specified Column/Row

Is there an easier way to do it?

1
vbaexcel
you can do it with a pivot without any VBA. If you really want to do it with VBA please also post the real code you have and explain why it isn't working. (on large datasets pivot table will always will be much more faster than any VBA code). – Máté Juhász
For future reference see Why Not Images of Code and Sample Data. – user4039065
see COUNTIF function and SUMIF function. – user4039065
use code block formatting for fixed-width font (4 spaces at start of each line, or use the {} button in the editor). – aucuparia
I understand the issue for images, Jeeped. As aucuparia pointed out, I wasn't aware of how to best present fixed-width material. Additionally, SumIf and CountIf would work if I were physically searching for a specific query. I'm attempting to make it populate on its own based on the current material added. I'll post back the code I have. – KVos

1 Answers

0
votes

PivotTable created by VBA (via Record Macro): 

Sub MacroPT()
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C2", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Sheet1!R1C3", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets("Sheet1").Select
    Cells(1, 3).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Column A")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Column B")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Column B"), "Count of Column B", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Column B")
        .Caption = "Sum of Column B"
        .Function = xlSum
    End With
    ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Column C"
    Range("D1").Select
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
        "Sum of Column B").Caption = "Column D"
    Range("C3").Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = False
        .RowGrand = False
    End With
End Sub

For Example 2 results in 6, not 8.