Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Solution to tricky problem of showing exactly Top N rows in Pivottable

I finally found solution to a problem that has haunted me for a long time.
Situation: I want to include the Top 3 subjects in student's performance report (generated via VBA) based on scores (out of 100) in the subject.

Of course, I started by using Top 10 filter in PivotTable (PT). However, when multiple subjects have same scores (say John scored 90 in subject 1, and scored 85 in three other subjects 2, 3, 6) the PT shows four rows (subject 1, 2, 3, 6), instead of 3. I understand why it does so, and for most situations this is acceptable.

However, for me the need to restrict the # of rows to 3 is more important than not displaying one of the subjects. So I wanted (subject 1, 2, 3) as the only 3 rows in the PT.

Heres how to you can do it:
I have 2 columns: Subject | Score

0. Sort the PT in Descending (since I want the Top 3 rows) order of the Score column
1. add a title (say Rank) to the column to the right of the rightmost column of PT. So technically this new column is NOT part of the PT. It sits right next to Score column
So now I have 3 columns out of which first two are part of the PT:
Subject | Score | Rank
2. In the cell below the title of the new column (Rank), add a formula to display a dynamic sequence of number starting 1. Suppose the cell in which you are entering the formula is in 6th row, then enter the formula: =Row()-5
3. Copy this formula down to as many rows as there are in the PT (without any filters applied)
So now regardless of how the values in the PT rows change based this new column will always show a sequence (rank) starting from 1 in increasing order
4. Select title cell of new column i.e. Rank
5. Click on the Funnel icon to add the Filter (Home Sort & Filter Filter)
6. This will automatically add the typical Autofilter dropdowns to ALL PT columns (Subject & Score) as well as the new Rank column
7. Add Number Filter in the Rank column of Less than Equal to = 3
8. Voila! you will ALWAYS see Top 3 rows even when there are duplicate values

Hope this helps!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
tricky sum problem Dave F Excel Discussion (Misc queries) 6 October 17th 06 01:35 PM
Tricky array problem…. MarkCCB Excel Discussion (Misc queries) 3 August 6th 06 11:04 AM
tricky problem with 14800 rows (collating figures) daviesnc New Users to Excel 1 April 19th 06 06:37 AM
Input box and add rows (tricky, for me at least) Matt Excel Programming 0 December 6th 05 11:19 PM
Really tricky excel problem Nigel Bigelsby Excel Programming 2 August 14th 03 08:02 PM


All times are GMT +1. The time now is 11:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"