• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

Pivot Table Guru needed. How to summarize a parts qty and total cost sorted by customer name.

This question is only for a true Pivot Table Guru. I am already pretty good at Pivot Tables, but this one problem is beyond my abilities.

My current Pivot Table creates subtotals customer orders and shows the quantity of each part.

I also want it to show the price and total value (qty * price).    But, I want it displayed inthe very particular way which is shown on the attached screen shot.  

Can any Guru show me how to do this fancy stuff with Pivot Tables without ugly workarounds.

For instance, I already know a bunch of workarounds to get my desired result:
I could copy the table and manually adjust it.
I could write a macro to automate those manual steps.
I could show quantity * price as a "side by side" field using options > calculations > calculated fields.
And, I found some other workarounds here
 
But, I am hoping someone can show a more elegant solution

rberke


 ee-pivot-qty-and-total-cost.xlsx
ee-pivot-qty-and-total-cost.png
0
rberke
Asked:
rberke
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nope - PivotTables are pretty inflexible with regard to header and what rows they display.

With regard to the latter, a more elegant solution would be to write a macro to hide the rows you don't want - or why not roll your own, and do your table not as a PivotTable, but with formulas. This way, you can customise it however you want.
0
 
rberkeConsultantAuthor Commented:
I have been "rolling my with formulas" for many years, but recently I have been using pivot tables to consolidate multiple regions from multiple worksheets.

The particular layout from this question has come up a half dozen times in the last few months, which prompted me to ask this question.  

I have written a macro which does what I want.  Now, I just create the pivot table summarizing quantities,  then call my macro which copies the pivot table to another location and extends it. The resulting table has links, so that refreshes to quantities in the underlying pivot table are reflected in the copy.  (changes to prices and part numbers would require rerunning my macro).
I have attached it if anyone is interested.
ee-pivot-qty-and-total-cost-v2.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now