Help with Excel Rankings Report using Pivot Table Data

[kencasehall]

Hey everyone! Need help with this task...
My main question is: Is there a formula that can return the company with the highest total sum of multiple fields in a PivotTable? For example, somehow revising the GetPivotData function to show the company with the highest sum of revenues in Consumer Marketing + Business Marketing + Social Media Marketing (all field names within the pivot table).


Please feel free to read below for more details to help guide your question or recommend a different solution:


-The PivotTable published by a research company shows a list of hundreds of companies and their revenues in 2015 and 2016 in certain areas (functions, business units, whatever). You could think of it as having 500 companies, their 2015 revenue in that market, and their 2016 revenue in that same market. We currently view it as Company Name along Column B (the Y-axis) and each "Area" on the top the x-axis with the "Year" below (Internet 2015 | Internet 2016 | Social Media Marketing 2015 | Social Media Marketing 2016 | etc....)


-I am currently trying to show the top 20 rankings based on 2016 revenues in each area. Sometimes our definition perfectly aligns with the report (i.e. Internet = Internet) while other times it does not (i.e. Marketing = Consumer Marketing + Business Marketing + Social Media Marketing). This requires multiple areas to be considered when determining rank.


-What I am trying to create is a separate sheet that could be used in the future when the publishers create an updated version of the PivotTable. This would reduce our time required to rank everyone and then see where we rank.


My process and formulas are below, but this requires me to copy and paste each section separately and calculate.
Column B: These are just input numbers "1" "2" etc...
Columns C & D: I used =Index( ,Match() ) to find the corresponding company name to the data
Column E: I used =LARGE() to map to Column B and the data to find the corresponding value according to the rank in Column B.
Columns F-K: I used the GetPivotData function to find the corresponding revenues in certain markets.

 

[SpillerBD]

You can add a "Calculated Field" and then use Sort or "Top-10" on the calculated field.

 

[Joe4]

Cross-posted: https://www.excelforum.com/excel-ge...l-rankings-report-using-pivot-table-data.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.