On one sheet I have a range of data (columns A through L) to which I am
applying an "advanced filter". The filter covers seven of the twelve columns
of data. A column in the filtered range calculates frequency of the items
remaining after the filter is applied [using I think what is called the
"Longre idiom":
=SUMPRODUCT(SUBTOTAL(3,OFFSET($F$3:$F$10000,ROW($F $3:$F$10000)-MIN(ROW($F$3:$F$10000)),,1)),--($F$3:$F$10000=F3))].
Here is an example of just columns F through H, after filtering:

Comment Theme Freq Rank
Have the info to do job well 2 2
Good supervision 3 1
Good OJT Qualification Program 2 2
Good environment 1 4
Motivation 1 4
Good supervision 3 1
Good supervision 3 1
Good OJT Qualification Program 2 2
Have the info to do job well 2 2

The filter applies only to some of the other columns, so that I can
determine how frequent some of the comment themes are. But what I need to
do, on another worksheet, is list the "top five themes", in order of rank,
without any duplicates. That would look something like:

Comment Theme Freq Rank
Good supervision 3 1
Good OJT Qualification Program 2 2
Have the info to do job well 2 2
etc.

I am trying to adapt one of those conditional lookup formulas to find from
the filtered range the 1's, then the 2's, etc., but not quite getting it to
work. The tricky part is that, with the Rank function, there can be a few
items tied at rank = 1, then no 2's or 3's (due to the tie), then some 4's.
Here's the formula I have so far for the lookup (entered with
ctrl-shift-enter), starting in row 11 and copied down a few rows:

{=IF(ROWS(B$11:B11)<=COUNTIF('Coded Comments'!$G$3:$G$67,"<4"),INDEX('Coded
Comments'!$F$3:$F$67,SMALL(IF('Coded Comments'!$G$3:$G$67=C11,ROW('Coded
Comments'!$G$3:$G$67)-ROW('Coded Comments'!$G$3)+1),ROWS(B$11:B11))),"")}

I know this is long, but a few of you seem to specialize in this kind of
thing and, I hope, will know what I mean without having to spend a lot of
time on it. Anyone?

TIA.