Hi, say i use 3 columns (storeName, itemID, itemValue) as the data source of a pivot table, which shows the Max (largest) itemValue for each storeName. How can i show in the pivot table a columns that show the itemID that corresponds to the itemValue displayed?
Example
--source data
Store1, 1, 500
Store1, 2, 1000
Store2, 3, 600
--what i want to see in the pivot table
Store1, 2, 1000
Store2, 3, 600
thank you.
See attached.
Insert the Pivot Table
Drag Store and Code down to Row Area,
Drag Amount to Data area, (change Sum of ... to Max of... if desired... doesn't really matter unless you have multiple amount for same store/code combo).
Then go to PivotTable Tools|Design tab
From Report Layout, choose Tabular form.
Get rid of the Subtotal and Grand Total rows, by right clicking them and unchecking the Subtotal and Grand Total options.
Then Click on the Code drop down arrow, Select Value Filters >> Top 10
Change the 10 to 1.
Click Ok.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks