Rollup Colums in Excel Macro Help

Hi,
I have been trying to lookup but having a hard time. Please could someone assist?

I'm trying to write a macro that rolls up columns based on certain criteria.

i.e
Column A:
Volumes

Column B:
Customer

Column C:
Type

I want to rollup based on customer, then type, and then volume.

So I want to show total volume based on type by customer.  

How can I do this?

Thanks!!!
eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GlowmanCommented:
when you say "rollup"
do you mean to sum distinct values?
would a sumifs() function do what you are referring to?
0
eastsidemarketAuthor Commented:
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?
0
FaustulusCommented:
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.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

eastsidemarketAuthor Commented:
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
0
FaustulusCommented:
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
0
eastsidemarketAuthor Commented:
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.
0
FaustulusCommented:
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.
0
eastsidemarketAuthor Commented:
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

Open in new window


thanks!
0
FaustulusCommented:
I'll take a look at it later in the day.
0
FaustulusCommented:
The procedure to run is 'CustomerVolumeTable', but there are a lot of constants at the top of the code which you should adjust as required. The variable names are self-explanatory, I trust. The constant 'ColumnCaptions' allows you to add more columns. However, the first item in the list is the column on which the pivot rests, which is being rolled-up, I suppose you would say.
Best practice would be to drag the entire module 'PivotMan' into your project. However, since you asked I point out that the procedure 'DelSheet' is exactly the same as the one I used in other code for you. If the two procedures would be in the same module (because you copied the new code into an existing module) VB wouldn't know which procedure to run and complain about ambiguity when compiling. In different modules one wouldn't know which procedures the other one has. But if you would remove the 'Private' from the declaration line in one of the modules both programs could use the same procedure and you could delete the other.
Same story, more or less, for the function GetSheet. I used the same name and largely the same function. But today's program needs less flexibility. So the two aren't interchangeable. But had I programmed for a single project I would have used the existing, more elaborate procedure to do the easier job required here rather than having two functions doing very similar tasks. As things are, there is no problem having the same name, both declared as Private, in different modules in the same project.
EXX-130925-Create-Pivot-Table.xlsm
0
eastsidemarketAuthor Commented:
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

Open in new window


any ideas?
0
FaustulusCommented:
The code works perfectly with me, repeatedly. Therefore I don't think I can really help you in this case. Anyway, I have re-written the code to explicitly create each object. However, I don't expect this to solve the problem.
Please modify the Excel version again in the attached code as you did before (2 locations in the code) and try to run the code in my own workbook. If it runs in my workbook but not in yours we must look for a fault in your workbook.
If it runs in neither, we must look for a fault in your Excel setup. I suspect that the required object library isn't set. The error message, if correct, is quite explicit, the PivotFields property of the PivotTable class couldn't be set. This could have one of two reasons. Either the PivotTable object wasn't found, or the object was found but the property not listed. I believe the former because the PivotFields property wasn't changed from 2007 to 2010, as far as I could find out. Therefore, if the property isn't found the library file should be corrupt.
However, I conclude that your VBA doesn't understand the PivotTable object. With the code having been re-written the same fault could now appear earlier than before.
Unfortunately, I couldn't find which library is required. In your VBE window, click on the Tools menu and open the References list. I have the following 4 object libraries checked:
- Visual Basic for Applications
- Microsoft Excel 14.0 Object Library
- OLE Automation
- Microsoft Office 14.0 Object Library
Of course, you would have 12.0 versions in place of the 14.0 I am listing, and I am not sure that you would need the OLE Automation. However, to add a library scroll down in the list and check the missing item. You may have to restart Excel to actually load it. After that Excel will remember to load the library without your help in future.
If none of the above helps I would be at the end of my tether and some one with Excel 2007 at his disposal would need to look at the problem.
As mentioned above, there is an outside chance that your Object Library is corrupt. You could get a fresh copy from your installation disk. The exact name and location of each library is supplied in the window from which I took the above list of object libraries. As you click on each item the details appear at the bottom.
EXX-130925-Create-Pivot-Table.xlsm
0
eastsidemarketAuthor Commented:
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!
0
eastsidemarketAuthor Commented:
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!
0
FaustulusCommented:
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.
0
FaustulusCommented:
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.
0
eastsidemarketAuthor Commented:
Which do i replace to add:
 
 With Pfld
            .Caption = "_" & Caps(i)
            .Function = xlSum
        End With

Open in new window


Is it this?
PivotFields(Caps(i)), "Sum of " & Caps(i), xlSum 

Open 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

Open in new window

0
FaustulusCommented:
Yes, you found the correct spot. Sorry. I should have been more precise.
0
eastsidemarketAuthor Commented:
sorry having trouble with this. can you please help me on what exactly to replace?
0
FaustulusCommented:
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.
0
eastsidemarketAuthor Commented:
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.
0
FaustulusCommented:
Both your requested improvements are incorporated in the attached workbook.
1. The pivot table's column caption is constructed as the plural of the column listed first in Const 'ColumnCaptions', and it is written into the cell specified as 'TargetCell'.
2. The data range selected for evaluation is now defined as the range of used cells connected with the first cell in the Customers (first item in 'ColumnCaptions') column, minus one row.
Please remember to change the Excel version in the code before deploying.
EXX-131005-Create-Pivot-Table.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
Faustulus is a genius. Helpful till the very end. Highly recommended! Give Faustulus a raise!!! :)
0
FaustulusCommented:
How would you know, mate?
I do hope we didn't reach the very end yet!
:-)
CU
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.