Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with nested data subtotals
I have a worksheet range containing attributes in columns A, B, and C -- with
data to be subtotaled in column D. The attribute columns are sorted so they are grouped together appropriately left to right. I want to insert subtotals both at column A and B -- with a being the primary group, and subtotals for column B nested inside that. I also want, in both cases, for the actual subtotal row to be ABOVE each grouping vs. below. Here is the code I am using: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=False The problem happens when it creates/inserts the subtotals for the 2nd iteration in column B. In each case, the first 'subtotal' row for column B appears ABOVE the subtotal row in column A, and in some cases, the last subtotal row for column B is duplicated with both a subotal at the top & bottom. If I edit the code to allow the summary data to fall BELOW the group, it summarizes everything correctly. In other words, it is my attempt to keep each summary at the top of each group that seems to be causing my issue---but this is what I REALLY want. By the way, I cannot use a pivot table due to other things I will be doing with this data. Any suggestions on what I can do -- or is there something wrong with my code that I am missing? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with nested data subtotals
Not sure of your exact setup, but I put 'Group' in A1 and 'Amount' in B1. I
have A, B, C in ColumnA and some randome numbers in ColumnB. Data Subtotals Uncheck the Summary Below Data box, and OK. Now I have all Subtotals ABOVE the Numbers, with the Grand Total in Cell B2. Not sure if it helps, but here is the recorded code: Sub Macro1() Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "jday" wrote: I have a worksheet range containing attributes in columns A, B, and C -- with data to be subtotaled in column D. The attribute columns are sorted so they are grouped together appropriately left to right. I want to insert subtotals both at column A and B -- with a being the primary group, and subtotals for column B nested inside that. I also want, in both cases, for the actual subtotal row to be ABOVE each grouping vs. below. Here is the code I am using: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=False The problem happens when it creates/inserts the subtotals for the 2nd iteration in column B. In each case, the first 'subtotal' row for column B appears ABOVE the subtotal row in column A, and in some cases, the last subtotal row for column B is duplicated with both a subotal at the top & bottom. If I edit the code to allow the summary data to fall BELOW the group, it summarizes everything correctly. In other words, it is my attempt to keep each summary at the top of each group that seems to be causing my issue---but this is what I REALLY want. By the way, I cannot use a pivot table due to other things I will be doing with this data. Any suggestions on what I can do -- or is there something wrong with my code that I am missing? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with nested data subtotals
There is no problem when you only need one row subtotaled. My problem is
that I need to insert ONE subtotal using column A as the group, then a SECOND subtotal (nested) using column B as the group. For example, here is what the data might look like in columns A-C: Brand SubBrand Volume ------- ------------ ---------- Brand1 AAA 100 Brand1 BBB 200 Brand1 BBB 300 Brand1 CCC 200 Brand1 CCC 100 Brand2 XXX 200 Brand2 XXX 300 Brand2 ZZZ 400 I want to insert one subtotal at the Brand level, and another at the SubBrand level. I have no problem understanding the steps to perform this action---the problem is that some of the subtotals for SubBrand actually appear ABOVE the line for the Brand total when all is said and done. Can't understand why and no matter how I arrange the data, the result is the same!! ARRGGHH!! Driving me nuts "ryguy7272" wrote: Not sure of your exact setup, but I put 'Group' in A1 and 'Amount' in B1. I have A, B, C in ColumnA and some randome numbers in ColumnB. Data Subtotals Uncheck the Summary Below Data box, and OK. Now I have all Subtotals ABOVE the Numbers, with the Grand Total in Cell B2. Not sure if it helps, but here is the recorded code: Sub Macro1() Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "jday" wrote: I have a worksheet range containing attributes in columns A, B, and C -- with data to be subtotaled in column D. The attribute columns are sorted so they are grouped together appropriately left to right. I want to insert subtotals both at column A and B -- with a being the primary group, and subtotals for column B nested inside that. I also want, in both cases, for the actual subtotal row to be ABOVE each grouping vs. below. Here is the code I am using: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=False The problem happens when it creates/inserts the subtotals for the 2nd iteration in column B. In each case, the first 'subtotal' row for column B appears ABOVE the subtotal row in column A, and in some cases, the last subtotal row for column B is duplicated with both a subotal at the top & bottom. If I edit the code to allow the summary data to fall BELOW the group, it summarizes everything correctly. In other words, it is my attempt to keep each summary at the top of each group that seems to be causing my issue---but this is what I REALLY want. By the way, I cannot use a pivot table due to other things I will be doing with this data. Any suggestions on what I can do -- or is there something wrong with my code that I am missing? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with nested data subtotals
That seems weird to arrange the data that way. I guess you tried a pivot
table already and it didn't work for you? I tied it and at least it made sense...to me anyway. I put Brand in rows and SubBrand just to the right of it, then added Volume to data. Then swapped the SubBrand and Brand; seems ok to me. You have some Subtotal options in the pivot table. I'd recommend exploring those options and see if you can get the results you need. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "jday" wrote: There is no problem when you only need one row subtotaled. My problem is that I need to insert ONE subtotal using column A as the group, then a SECOND subtotal (nested) using column B as the group. For example, here is what the data might look like in columns A-C: Brand SubBrand Volume ------- ------------ ---------- Brand1 AAA 100 Brand1 BBB 200 Brand1 BBB 300 Brand1 CCC 200 Brand1 CCC 100 Brand2 XXX 200 Brand2 XXX 300 Brand2 ZZZ 400 I want to insert one subtotal at the Brand level, and another at the SubBrand level. I have no problem understanding the steps to perform this action---the problem is that some of the subtotals for SubBrand actually appear ABOVE the line for the Brand total when all is said and done. Can't understand why and no matter how I arrange the data, the result is the same!! ARRGGHH!! Driving me nuts "ryguy7272" wrote: Not sure of your exact setup, but I put 'Group' in A1 and 'Amount' in B1. I have A, B, C in ColumnA and some randome numbers in ColumnB. Data Subtotals Uncheck the Summary Below Data box, and OK. Now I have all Subtotals ABOVE the Numbers, with the Grand Total in Cell B2. Not sure if it helps, but here is the recorded code: Sub Macro1() Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "jday" wrote: I have a worksheet range containing attributes in columns A, B, and C -- with data to be subtotaled in column D. The attribute columns are sorted so they are grouped together appropriately left to right. I want to insert subtotals both at column A and B -- with a being the primary group, and subtotals for column B nested inside that. I also want, in both cases, for the actual subtotal row to be ABOVE each grouping vs. below. Here is the code I am using: Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=False Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=False The problem happens when it creates/inserts the subtotals for the 2nd iteration in column B. In each case, the first 'subtotal' row for column B appears ABOVE the subtotal row in column A, and in some cases, the last subtotal row for column B is duplicated with both a subotal at the top & bottom. If I edit the code to allow the summary data to fall BELOW the group, it summarizes everything correctly. In other words, it is my attempt to keep each summary at the top of each group that seems to be causing my issue---but this is what I REALLY want. By the way, I cannot use a pivot table due to other things I will be doing with this data. Any suggestions on what I can do -- or is there something wrong with my code that I am missing? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |