SilverSN95
11-09-2011, 01:59 PM
The solution to this may be more of a pivot-table/formula than VBA question at the moment, but I’m hoping I can get a little help on this…
I have attached a workbook containing a table of data and pivot table demonstrating what I am trying to do. The results in the pivot table are exactly what I want, just not the way I want to get them, and here is why: My actual use for this is for a very large amount of data (>40,000 rows), and the only way I have been able to calculate two data elements are by using very expensive formulas, which you can see on the data table. These are to calculate a maximum for the aggregates of two values for each day, where all other columns are matching. It’s a bit hard to explain but I’m hoping the sample will be clear. What I’m hoping for is a much better way to calculate the values under “Max of Daily Sum A/B”. Can this be done through the pivot table, I have tried several different things and been able to get this result? Maybe a more efficient way to compute the daily aggregates?
To clarify, I could expand the rows in the pivot, then do a MAX formula on the pivot table area to find the value I need, but this will not be practical as I need the calculation to be as dynamic as possible, hence the pivot table. The pivot table in turn is being queried using GETPIVOTDATA to auto-populate a report. The source for the pivot will be a named range, which will change in values and size.
Thanks much.
I have attached a workbook containing a table of data and pivot table demonstrating what I am trying to do. The results in the pivot table are exactly what I want, just not the way I want to get them, and here is why: My actual use for this is for a very large amount of data (>40,000 rows), and the only way I have been able to calculate two data elements are by using very expensive formulas, which you can see on the data table. These are to calculate a maximum for the aggregates of two values for each day, where all other columns are matching. It’s a bit hard to explain but I’m hoping the sample will be clear. What I’m hoping for is a much better way to calculate the values under “Max of Daily Sum A/B”. Can this be done through the pivot table, I have tried several different things and been able to get this result? Maybe a more efficient way to compute the daily aggregates?
To clarify, I could expand the rows in the pivot, then do a MAX formula on the pivot table area to find the value I need, but this will not be practical as I need the calculation to be as dynamic as possible, hence the pivot table. The pivot table in turn is being queried using GETPIVOTDATA to auto-populate a report. The source for the pivot will be a named range, which will change in values and size.
Thanks much.