E
excelaroni
I finally found solution to a problem that has haunted me for a lon
time.
Situation: I want to include the Top 3 subjects in student'
performance report (generated via VBA) based on scores (out of 100) i
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 subjec
1, and scored 85 in three other subjects 2, 3, 6) the PT shows four row
(subject 1, 2, 3, 6), instead of 3. I understand why it does so, and fo
most situations this is acceptable.
However, for me the need to restrict the # of rows to 3 is mor
important than not displaying one of the subjects. So I wanted (subjec
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 th
Score column
1. add a title (say Rank) to the column to the right of the rightmos
column of PT. So technically this new column is NOT part of the PT. I
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 formul
to display a dynamic sequence of number starting 1. Suppose the cell i
which you are entering the formula is in 6th row, then enter th
formula: =Row()-5
3. Copy this formula down to as many rows as there are in the P
(without any filters applied)
So now regardless of how the values in the PT rows change based thi
new column will always show a sequence (rank) starting from 1 i
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 AL
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 duplicat
values
Hope this helps
time.
Situation: I want to include the Top 3 subjects in student'
performance report (generated via VBA) based on scores (out of 100) i
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 subjec
1, and scored 85 in three other subjects 2, 3, 6) the PT shows four row
(subject 1, 2, 3, 6), instead of 3. I understand why it does so, and fo
most situations this is acceptable.
However, for me the need to restrict the # of rows to 3 is mor
important than not displaying one of the subjects. So I wanted (subjec
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 th
Score column
1. add a title (say Rank) to the column to the right of the rightmos
column of PT. So technically this new column is NOT part of the PT. I
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 formul
to display a dynamic sequence of number starting 1. Suppose the cell i
which you are entering the formula is in 6th row, then enter th
formula: =Row()-5
3. Copy this formula down to as many rows as there are in the P
(without any filters applied)
So now regardless of how the values in the PT rows change based thi
new column will always show a sequence (rank) starting from 1 i
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 AL
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 duplicat
values
Hope this helps