Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tricky sum problem | Excel Discussion (Misc queries) | |||
Tricky array problem…. | Excel Discussion (Misc queries) | |||
tricky problem with 14800 rows (collating figures) | New Users to Excel | |||
Input box and add rows (tricky, for me at least) | Excel Programming | |||
Really tricky excel problem | Excel Programming |