Rollup Colums in Excel Macro Help

when you say "rollup"
do you mean to sum distinct values?
would a sumifs() function do what you are referring to?

Yes, it appears  sumifs() will do the trick.

I am unsure of what the criteria maybe at a given time, just want to sum all the differences up and do the entire sheet.

Any idea?

This looks like a job for a pivot table. "Looks" is the wrong word, though, because you don't show us enough, really, but it does sound like it.
A pivot table could show you the volume per customer or per type, all based on the same data, at a click of a button. It should be easy to set up, but if you like step-by-step instructions you need to pst a workbook.

Thanks Faustulus!

See attached. Basically, I want to rollup Based on column R first (customer name).
and copy column R, and the sum of column A into a new sheet.

in the new sheet show column A as the customer name,
and column B as the total sum from previous sheet.

Thanks!
Book1.xlsm

I'm still struggling with the term "roll-up" which I have never heard before and can't accurately guess what it might mean.
In the attached workbook I have created the result as you have described it (or, as I have understood your description). Is that what you want? If not, please post a manual creation of the desired result.
BTW, I am surprised to see that your data have no column captions. This would be a significant feature. Please make sure that the structure of the 'Data' sheet reflects the real thing rather exactly.
Book1.xlsm

sorry to be clear basically like a sum.

so will take each customer in the original sheet, and if it's listed many times, it will sum up all the volume from column A for that customer, then paste the total volume and the customer name just once in the new page.

so in your sheet, result tab should list CUST1 once, with a total of 2550 next to it. The other cust are only listed once on the data sheet, so sum will stay 2250.

thanks.

Let's try.
First, you need to give captions to your columns, all columns, even those you don't wish to use. IN my example, I named A as "Sums", R as "Customers" and all others by the name of the column, i.e. B, C, D etc.
From the Insert Tab select 'Pivot Table'.
The dialog box that opens will show the entire range of your table preselected. Just confirm whatever there is without change. That will create a new worksheet for the pivot table and a new dialog box.
In the Pivot Table field list check the columns 'Sums' and 'Customers' and the list which you wanted appears on the left.
You can save the workbook.

that is exactly what i want, thanks! how can i automate that via vba?
I did a record macro and got the below, but would need some help to change a few things.
i.e adding a blank tab and selecting that tab (i assume it wont always be Sheet42).

Sub PivotMyTable()
    Sheets.Add
    Results.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Results!R1C1:R2811C18", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet42!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet42").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Brkr Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Volume"), "Sum of Volume", xlSum
        End Sub

Select allOpen in new window

thanks!

I'll take a look at it later in the day.

thanks! first wasn't able to run the code with:
xlPivotTableVersion14

so i changed the version number to 12:
xlPivotTableVersion12

then your workbook worked as exepected.

i copied your module into my workbook, update the pivot table version and runs up until the last part errors out:

Run-Time error '1004'
unable to get the pivotfields property of the pivotable class.

.AddDataField _
                WsT.PivotTables(PivotName). _
                PivotFields(Caps(i)), "Sum of " & Caps(i), xlSum

Select allOpen in new window

any ideas?

Ok, I sorted this out. Thank you.

It now works as expected.

Question on the pivot table output (results).

The customer column shows "Row Labels" and not the row header from which it grabbed from.

Yours does the same. How do I get it to display "Customer" (or what the row header is)
thanks!

Faustulus,
Did you see my note above?

Also for some reason it also adds a customer at the bottom called (blank) with a volume, when i do the pivot table manually it doesn't have that.

also, on both my manual and pivot table which you helped me with above, it add a blank value under customer with volume. any way to remove this?

thanks!

I'm afraid I can't deal with your requests for correctlion, let alone improvement, at this time. The earliest I might find time would be October 2, but I'm unwilling to firmly promise that this would be the first thing I'll do when I come up for air. If no other expert helps you earlier I will look at the problem as soon as I can.

The columns in the pivot table can't bear the same names as columns in the data source. Try this code to replace existing lines near the bottom of procedure 'CustomerVolumeTable',
        With Pfld
            .Caption = "_" & Caps(i)
            .Function = xlSum
        End With
where an underscore is placed before the column caption. Consider Caps(i) & "+" as an alternative. I suppose you could use a blank, like Caps(i) & " ", but I don't like leading or trailing spaces because you can't see them and, sooner or later, they come back to haunt you.
Regarding the extra customer the workbook I posted doesn't have that fault. Look at your source data. You should find the fault there. The pivot table selects the data source range as "StartCell.CurrentRegion" where StartCell is defined  as the cell containing the word "Customer" in the caption, and CurrentRegion is the table to which that cell belongs. Look for a row or rows at the bottom of your data where there is no customer name but other data. In fact, those rows need not be at the bottom. They could be anywhere within the table: Data but no customer name.
If that is a feature of your data you should create the pivot table from a filtered copy where these rows are eliminated.

Which do i replace to add:
 

 With Pfld
            .Caption = "_" & Caps(i)
            .Function = xlSum
        End With

Select allOpen in new window

Is it this?

PivotFields(Caps(i)), "Sum of " & Caps(i), xlSum 

Select allOpen in new window

Here is my current code:

Sub CustomerVolumeTable()

    Const SourceSheet As String = "GI"
    Const PivotName As String = "GI By Broker"
    Const TargetCell As String = "A1"
    
    ' Keep "Customer" in first position. Add as required.
    Const ColumnCaptions As String = "Brkr Name,Volume"
    
    Dim WsS As Worksheet                ' Source
    Dim WsT As Worksheet                ' Target (Pivot)
    Dim StartCell As Range
    Dim Caps() As String                ' Array(ColumnCaptions)
    Dim i As Long
    
    Caps = Split(ColumnCaptions, ",")
    With ThisWorkbook
        Set WsS = .Worksheets(SourceSheet)
        If Not SetStartCell(StartCell, WsS, Caps) Then Exit Sub
        
        Set WsT = GetSheet(PivotName)
        .PivotCaches.Create(SourceType:=xlDatabase, _
                            SourceData:=FullRangeName(StartCell.CurrentRegion), _
                            Version:=xlPivotTableVersion12) _
                            .CreatePivotTable _
                            TableDestination:=FullRangeName(WsT.Range(TargetCell)), _
                            TableName:=PivotName, _
                            DefaultVersion:=xlPivotTableVersion12
    End With
    
    i = LBound(Caps)
    With WsT.PivotTables(PivotName)
        With .PivotFields(Caps(i))
            .Orientation = xlRowField
            .Position = 1
        End With
    
        For i = i + 1 To UBound(Caps)
            .AddDataField _
                WsT.PivotTables(PivotName). _
                PivotFields(Caps(i)), "Sum of " & Caps(i), xlSum
        Next i
    End With
    WsT.Columns.AutoFit
    ThisWorkbook.ShowPivotTableFieldList = False
End Sub

Select allOpen in new window

Yes, you found the correct spot. Sorry. I should have been more precise.

sorry having trouble with this. can you please help me on what exactly to replace?

It's this line,
PivotFields(Caps(i)), "Sum of " & Caps(i), xlSum
More precisely, this part of the above line,
"Sum of " & Caps(i)
Here Caps(i) is the variable that contains the original column captions, values like "Volume". In this code the original caption is concatenated with "Sum of " to make up the column caption in the pivot table which you don't like.
You can't just take the same caption because that is already used. Using the same code you can concatenate that same name with something else, like,
"_" & Caps(i)
Of course, you could also use completely different captions, but that would take some extra programming. You could also use other concatenations, like
"_" & Caps(i) & "_"
Just remember that Caps(i) stands for an existing column caption. Use the ampersand to join it with anything else, and write that anything else between quotation marks.

sorry let me be more clear. the sum of volume header is fine as is. that's not the issue.

the following are the issues:
1. column A of the pivot table's header says "Row Labels" where I want it to say Customer
2. its adding an entry where column A says (blank) and has a volume# in column B.
It seems it does this b/c on the source data it's taking the last line which is the sum. How can I get the pivot table to exclude the last line?


Thanks.

Faustulus is a genius. Helpful till the very end. Highly recommended! Give Faustulus a raise!!! :)

How would you know, mate?
I do hope we didn't reach the very end yet!
:-)
CU