Find Max column value in a Pivot Table - Table may grow/shrink 1

[MattGreer]

It seems that, after searching quite a bit, there's not a way to refer to the innards of a Pivot Table like you can a regular Table, by referencing the column headers and so forth. If I have understood what I've found, you have to refer with the actual row column locations.

Is it a terrible thing to simply use the MAX function from the top of the PT to the very bottom of a given worksheet? So, essentially, MAX(A:A)? Seems like a bad idea to me.

Thanks!


Thanks!!


Matt

 

[combo]

You can generate dynamic references to pivot table data with GETPIVOTDATA function.
If it is not suficient for you, since excel 2016 you can join tables in data model, use DAX to control data flow and construct measures, use multiple joined tables in single pivot table.
Some info (only MS) about this:

https://support.office.com/en-gb/ar...er-pivot-bab3fbe3-2385-485a-980b-5f64d3b0f730

https://support.office.com/en-gb/ar...er-pivot-7e4475e2-85c2-4496-9045-431412137b37

https://docs.microsoft.com/en-us/dax/dax-function-reference

https://support.office.com/en-gb/ar...eference-2378132b-d3f2-4af1-896d-48a9ee840eb2

Power query with its M language is another tool that can be used to process data. In both cases pivot table is only one of ways to present output report.

combo

 

[MattGreer]

I tried GETPIVOTDATA but, to be honest, I just couldn't figure out the syntax. With a regular table Excel it's easy (Excel does it for you of course) to refer to columns, rows, the table itself, etc. BTW I'm running Excel 365, looks like version 16.x, 64 bit.

Let's say I have a column of data in a pivot table. Pivot Table name is "MYPIVOT", and the column is called "A-PROD". The column is full of production data by month, predicted for the future. I want to find the maximum production number in that column. Each row is one day, and the value is a one-day total. Today, I might have three months of predicted data. Next week they may give me four months, or they might change the source data behind the pivot table and the date of the max value is different.

I'm very interested in any and all alternative means of summarizing this data I have. Right now the spreadsheet is 70 MB and my computer is absolutely crawling. I'm thinking of putting the source data into Access and letting Excel and Access talk to each other cause this is KILLING ME. But I digress.

Using GETPIVOTDATA, would you help me with the syntax? I couldn't find an example anywhere that picked the MAX of a column "Sum of [xyz]"

PivotTable Name: MYPIVOT

Thanks!!


Matt

 

[combo]

GETPIVOTDATA: you can select 'Generate GetPivotData' in pivot table options, you can now reference pivot table cell from outside pivot table and it will be referenced by this function. You can get only single data cell from PT, the advantage is that you reference data by pivot table field values displayed. However, this does not seem to be useful in your problem.

With power query you don't need a pivot table to get a report as in the image. The steps:
1. convert input data into table, rename it if you like,
2. from Get&Transform section in the data ribbon tab, new query, from table,
3. in a single query remove unnecessary columns, group by date, aggregate with sum (link to power query add-in environment in excel 2013, but newer interface is still similar),
4. output query result to worksheet, you will get table for future processing.
You can copy your query in query editor, continue processing, get max and output only it.
Power query is fast, I was processing in excel +200k rows, with gender/age related tables and currency exchange rates related tables, all recalculated in single seconds.

combo

 

[MattGreer]

Great suggestions combo. I have some vacation time coming up so I can take a look at this as it'll be me and google figuring this out, heh.

All that being said, and perhaps getting off the subject here, but dealing with a 70 MB spreadsheet at this point is limiting as I don't have a lot of horsepower. The solution that seems obvious to me is to have Access database back and with an Excel front end. It's been a long long time since I've done that for sure.

If I go that route (pretty certain I will), should the calculations for summarizing the data be in Excel, or Access? In this instance, I have production from PROD A, PROD B, PROD C, etc. on the same day. The pivot table summarizes all the production on a given date. Should the pivot table be generated in Excel, getting data from Access, or should I build the query in Access and just pull a static table into Excel?




Thanks!!


Matt

 

[combo]

If you have xlsx format: switch to binary (xlsb) format, the same data will shrink about 4 times.
Sometimes I use the following structure: (1) mainframe database => (2) access database with required linked tables and some support queries on the network drive with common drive mapping => (3) excel file with power queriies application, linked to network access queries and support power query queries for output to tables and pivot tables. End users only write dates in excel and refresh data, local PQ query does final filtering. Final network access and end-user excel files stay small.

combo

 

[MattGreer]

Binary is definitely helping. Thank you! And thank you for helping with the "flow of data", so to speak. Wish I could give you more than one star in this thread!

Thanks!!


Matt