Extract uniques from filtered range

[andy62]

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.

 

[Herbert Seidenberg]

This will get you part of the results, using Pivot Table.
The Rank numbers are not used.
Here is the data I used:

Comnt Freq
Info 2
GoodS 3
OJT 2
GoodE 1
Motiv 1
GoodS 3
GoodS 3
OJT 2
Info 2

Data > Pivot Table > Multiple Consoldation Ranges
Range: Select the 10 x 2 array shown above.
Layout: DATA = Count of Value
Double click on Row button
Advanced: AutoSort > Descending
using field > Count of Value
AutoShow > ON > Top 3
Options: Uncheck Grand Totals, AutoFormat
The result will look like this:

Row Freq
GoodS 3
OJT 2
Info 2

Rank of the items is implied by their position.

 

[andy62]

Hi Herbert - Thanks for the response. I don't have much experience with
Pivot Tables, but in trying to implement your solution I'm wondering if it'll
work in my situation. The range I am trying to extract from is already
filtered using some other columns (business unit, location, department,
etc.). The remaining "duplicates" are multiples instances of survey
comments. I don't want to access any of the data that is already hidden by
the filter. Will this still work?

 

[Herbert Seidenberg]

To copy only the visible filtered data
Edit > GoTo > Special > Visible cells only
Copy > Paste