Subtotals several columns

[fredlo2010]

Hello,

I am trying to create a report from data and add subtotals to several columns at the same time.

I tried to do it with a pivot table which would be the fastest way to go ( I think) but the pivot sorts the data and I need the data to remain in the same format it was originally. Perhaps this can be done ?

Right now the macros does it by looping several times though the column and adding the totals to specific ranges; which is fine for a 100 line report. But things start getting really slow when the data has 2000 rows. (I am not attaching the code I have right now because its too log and complicated)

Any ideas about his?

I've attached a file with the data and the desired results.

12461

Thanks in advance for the help.

[Bob Phillips]

Why not just add a helper column of row number in the original data, pivot with that as the first element of your pivot?

[fredlo2010]

Hi Xld,


Thanks for the suggestion. I tried that but I cannot manipulate to achieve what I want.

I tried adding it to the beginning of the pivot but its not coming up right. Maybe I am doing something wrong. :(

[SamT]

First I would try to convince the boss to switch to GnuCash.
Then I would try any database.

I only see two identical loops, one on the SuperCategory column and one on the Category column, each with the SUM() function, ran on the two amounts columns.

Take advantage of the fact that Range.End(xlUp) stops at the next empty cell when figuring Sums.

Build a 2-D array, Arr(i, j), the first D with the SuperCategories and D-2 with Categories.


For i = LBound (Arr) To ...
Find the SuperCat (Arr(i, )); insert two rows, add labels and sums for the first S-Cat
For j = LBound Arr(i) to UBou...
Find the Cat, (arr(i, j)); insert two Rows and the first Cat's labels and Sums
Next j
Next i

If you already have a COA around, use that to build the two arrays.

[Bob Phillips]

How about this Fred?

12462

[fredlo2010]

Thanks for the reply Sam,

There is no way to change software. If we do in the future is for our in-house product.

The code right now uses something like that to build the report. But I was trying to stay away from the loops and the insert columns which use a lot of time. This is not the only report that's run this is just an example. In one report I will have to build about 5 of this sub reports.

I need to find a way to do this and keep the integrity of the original report.

:) :)

[fredlo2010]

How about this Fred?

12462

This works almost the way I want.

SuperCategory is in the same order but category is not. I hope I could add another sort field for the Categories.

Also where did you get the Field SortID from ? is that a calculated field? I need to know because I will be creating the pivot through VBA.


Thanks

[Bob Phillips]

Modified.

12463

[fredlo2010]

This is exactly the way I wanted it. How did you do it xls? I dont see it.

Thanks :)

[Bob Phillips]

I created custom lists Fred, the pivot will sort by custom lists (unless you turn that option off).

[SamT]

My thoughts on the matter are that by using "Find" you only have to step thru even the largest possible report by only the number of Super Cats and Cats that you have in the COA. In the test.xlsx file that would be one traversal of the sheet with 10 stops.

In any COA, there are actually very few SuperCategories, less than a dozen, or so. You mentioned that a typical report has around 2000 lines. I will give a SWAG that there are less than 100 Categories, which means that it will less than 112 sets of 4 operations on that 2000 line file. I just did a quick and dirty test: Insert 2K Rows and 3K values. It took less than 2 seconds. 2000 "Finds" took about a second. That's the equivalent of a 2000 Category report with who cares how many accounts.

It seems that this is for reports sent to outside agencies.. That would only require separate COA Arrays for each agency.

You can also treat the original like a data base and the create the output from a series of queries based on the COA.

Note that I am seeing the issue as formatting a COA while you are seeing it as formatting a list in Excel.

[fredlo2010]

Thanks for the reply Sam,

What's COA?

I my reports take about 45 seconds to run. But of course this is full of old select statements that will slow it down. I think the part that's taking the largest tow is the section of inserting rows.

Thanks

[fredlo2010]

Xld,

Thanks for the reply.

That's exactly what I am envisioning. I have created a code for this (I think this is one of my first codes for PV) but I cannnot get the the sort to work. Also any tips for improving it? I rememeber reading in some books that's better to create the table, calculate to draw it and then add the data?

Thanks

This is the code I have. Pretty much a recorded macro modified.


Sub CreatePivotReport()
Dim strSource As String
Dim pTable As PivotTable
Dim pCache As PivotCache

strSource = "Original!" & Sheets("Original").Range("A6").CurrentRegion.Address

On Error Resume Next
Application.AddCustomList ListArray:=Sheets("Lists").Range("A1:A2")
Application.AddCustomList ListArray:=Sheets("Lists").Range("B1:B7")
On Error GoTo 0

' Remove all other pivot tables
For Each pTable In ActiveSheet.PivotTables
Range(pTable.TableRange2.Address).Delete xlToLeft
Next


Set pCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strSource)
Set pTable = pCache.CreatePivotTable(Sheets("Original").Range("H6"), "myTable")

' Set up the general layout.
pTable.RepeatAllLabels xlRepeatLabels

With pTable
.PivotFields("SuperCategory").Orientation = xlRowField
.PivotFields("SuperCategory").Position = 1
.PivotFields("SuperCategory").LayoutBlankLine = True
.PivotFields("SuperCategory").LayoutSubtotalLocation = xlAtBottom


.PivotFields("Category").Orientation = xlRowField
.PivotFields("Category").Position = 2
.PivotFields("Category").LayoutBlankLine = True
.PivotFields("Category").LayoutSubtotalLocation = xlAtBottom
.PivotFields("Category").RepeatLabels = True
.PivotFields("Category").LayoutForm = xlTabular

.PivotFields("Account").Orientation = xlRowField
.PivotFields("Account").Position = 3

.AddDataField .PivotFields("Opening Balance"), " Opening Balance", xlSum
.PivotFields(" Opening Balance").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"


.AddDataField .PivotFields("Closing Balance"), " Closing Balance", xlSum
.PivotFields(" Closing Balance").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"


' General settings
.ShowTableStyleRowHeaders = False
' This is not removing the buttons.
.ShowDrillIndicators = False
End With


' This is what the recorder spit out.
'but how do I get it to work with the pivot table.
' Also how do I reffer to the order custom? right now its refereing to a number.
ActiveSheet.Range("$H$7").Sort Order1:=xlAscending, Type:=xlSortLabels, _
OrderCustom:=6, Orientation:=xlTopToBottom

ActiveSheet.Range("$H$8").Sort Order1:=xlAscending, Type:=xlSortLabels, _
OrderCustom:=7, Orientation:=xlTopToBottom

End Sub



12465

[Aussiebear]

How did you do it xls?

Bob, probably won't tell you this but I've watched him. He just throws it all together in a plastic jar, gives it all a couple of kicks around the room and there she be....... :devil2: Meanwhile for the rest of us mere mortals, its like trying to build a jigsaw out of leftovers ( you know, after the grandkids have finished losing the pieces).

[Bob Phillips]

Why bother explicitly sorting it Fred? Excel sorts the pivot table when it is built, so if you have added the custom lists, and you haven't unchecked that option, it should sort by those lists implicitly.

[SamT]

COA is the accounting industry acronym for Chart Of Accounts.

In xld's attachment, sheet "Original", Range A7:C30, is a fully qualified COA.

In the same sheet, Range H2:J34, if you remove all the "Total" Rows, is a short form COA.

.

.

.
I've been playing around with my idea, but I getting too groggy right now.

Can you share a relistic report (columns A, B ,D, & E).

PM me and I'll send you my Email if you want.

[fredlo2010]

Why bother explicitly sorting it Fred? Excel sorts the pivot table when it is built, so if you have added the custom lists, and you haven't unchecked that option, it should sort by those lists implicitly.
Got it thanks.

There is one last detail in the table that I would like to see if it could be optimized. The label heading? Can that be removed? I have highlighted it yellow in the report.

This is the code I have to far. I am going to dig in my books to try to find the section about the drawing of the table and the refresh for optimization purposes.


Sub CreatePivotReport()
Dim strSource As String
Dim pTable As PivotTable
Dim pCache As PivotCache

strSource = "Original!" & Sheets("Original").Range("A6").CurrentRegion.Address

On Error Resume Next
Application.AddCustomList ListArray:=Sheets("Lists").Range("A1:A2")
Application.AddCustomList ListArray:=Sheets("Lists").Range("B1:B7")
On Error GoTo 0

' Remove all other pivot tables
For Each pTable In ActiveSheet.PivotTables
Range(pTable.TableRange2.Address).Delete xlToLeft
Next

' Set the cache and the table.
Set pCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strSource)
Set pTable = pCache.CreatePivotTable(Sheets("Original").Range("H6"), "myTable")

' Set up the general layout.
pTable.RepeatAllLabels xlRepeatLabels

' Set layout and fields.
With pTable
.PivotFields("SuperCategory").Orientation = xlRowField
.PivotFields("SuperCategory").Position = 1
.PivotFields("SuperCategory").LayoutBlankLine = True
.PivotFields("SuperCategory").LayoutSubtotalLocation = xlAtBottom


.PivotFields("Category").Orientation = xlRowField
.PivotFields("Category").Position = 2
.PivotFields("Category").LayoutBlankLine = True
.PivotFields("Category").LayoutSubtotalLocation = xlAtBottom
.PivotFields("Category").RepeatLabels = True
.PivotFields("Category").LayoutForm = xlTabular

.PivotFields("Account").Orientation = xlRowField
.PivotFields("Account").Position = 3

.AddDataField .PivotFields("Opening Balance"), " Opening Balance", xlSum
.PivotFields(" Opening Balance").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"


.AddDataField .PivotFields("Closing Balance"), " Closing Balance", xlSum
.PivotFields(" Closing Balance").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

' General settings
.ShowTableStyleRowHeaders = False
.ShowDrillIndicators = False
End With


End Sub


The code looks so far the way I imagined it :)

This is the file with the highlights. 12466

Thanks

[fredlo2010]

Thanks for the clarifications Sam.

Unfortunately I cannot share realistic data because of sensitive data. I prepared the sample as close as the original as possible :)

I will see if I could make something better tomorrow.

I just tested the pivot table sample I processed 360,000 rows in 1.6 seconds. and the code is very minimal; that's what I had in mind. This will bring tons of performance improvements to the code :)

[fredlo2010]

Bob, probably won't tell you this but I've watched him. He just throws it all together in a plastic jar, gives it all a couple of kicks around the room and there she be....... :devil2: Meanwhile for the rest of us mere mortals, its like trying to build a jigsaw out of leftovers ( you know, after the grandkids have finished losing the pieces).

lol This was hilarious. After been stuck in traffic for one hour, with nothing else but road rage and thoughts of next Thursday Calculus 2 test...this made my day :) :) :)

[SamT]

By Realistic, I just meant long and varied enough to take a measurable time. I'll look in my archives and see what I can find. Unfortunately I know that I gave the entirety of the last Accounting system I created to the Client.

It would have been a perfect test; one Category had over 200 accounts and one SCat had, IIRC, over 50 Cats.

[Bob Phillips]

There is one last detail in the table that I would like to see if it could be optimized. The label heading? Can that be removed? I have highlighted it yellow in the report.

Are you saying that you would like to remove SuperCategory from the Pivot, yet still get it sorted by Supercategory? If you just remove SuperCategory it will still sort correctly because the Catgory Custom List covers both SuperCategory types, but of course yoiu won't get the Supercategory sub-totals.

[fredlo2010]

I wanted to remove the SuperCategory Label from the top of the group and still keep the subtotals. This is not a major thing I can loop through the blank spaces between categories which are very reduced now and then remove those labels. Or convince my bosses that those labels there are harmless and add readability to the report...which in my opinion they do.

I was playing with it last night and implicit sorting was not working is there anything I can do to make sure the table is sorted the way I want. ? Keep in mind that we use excel a lot and the same application instance might have several workbooks open and at the same time several instances of excel open. :) it gets crazy here believe me.

Thanks

[Bob Phillips]

I wanted to remove the SuperCategory Label from the top of the group and still keep the subtotals.

If you mean keep the items and subtotals but just remove the label, change it to a space.

Did you refresh?

[fredlo2010]

I mean remove what's in yellow but keep what's in orange.

And the sorting is working now... perhaps I did refresh. I am reading my book on pivots and I am finding tons of interesting things :)


(for some reason the image is not showing in the post, here is a link)
https://dl.dropboxusercontent.com/u/30987064/fdfd.PNG


Thanks

[Bob Phillips]

Oh no, you I can't see that you could get rid of the label Income and still have a label of Income Total, the total label just appends Total to the item label.

[SamT]

Fred,

I found an old COA from 1996! That's four OS'es and I don't know how many computers ago.

The attached has 9 SuperCats and 91 Cats, and just enough accounts for testing.
All the operating code is in the module "Formatting_Code."
The COA arrays are in "CompanyName."
"CompanyName2" is almost a copy. It will make it easier to double the length of the test

Worksheet "New COA" is a master I used to copy into sheet Testing.
I ran the Code on "Testing".

Running it without any formatting of any kind, (except inserting rows,) took about 1 second, embolding took about 1 second, and shifting a range and deleting a column about 1 second.

My SWAG is about 2 seconds per 100 Categories plus one second for final sheet work.

[fredlo2010]

Hi Sam,

Thanks a lot for the code I did some testing and its fast. But I opened it in a standard alone instance. It was lightening fast.

Then I opened it in another excel instance that has another workbook opened ( users will usually have 3 to 4 of this workbooks opened) and this what I got in timer for the original amount of rows.(112)

Timer:
49062.3
49073.29

Thanks

[SamT]

Gosh, I hope those are milliseconds and not days :D

I wonder why such an incredible difference. Maybe because it's an .xls file?

[fredlo2010]

:) its milliseconds


The maximum time for the longest reports which includes getting data out of the software is about 45 minutes. out of those 20 goes to the the pulling of the data and the rest more or less goes to the VBA part.

Yeah it needs to be optimized lol :)

[SamT]

The total number of Rows in the code I gave you is irrelevant. The only thing that takes time is the number of Categories, including the Super Categories.

Time this version with 1300 rows, but the same # of Categories.

[Roger Govier]

I mean remove what's in yellow but keep what's in orange.

And the sorting is working now... perhaps I did refresh. I am reading my book on pivots and I am finding tons of interesting things :)

Thanks

Hi Fred

A bit late to the party as I have only just seen this post, but you could achieve what you want with a bit of subterfuge - just format the text of the SuperCategory with a Custom Format of ";;;"

Setting the format of the .PivotFields doesn't seem to work, but adding the following to the end of your code

Range("H8").NumberFormat = ";;;"

works just fine.

Clearly if you change your PT location from "H6", then you also need to change the range as shown above