Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract uniques from filtered range
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract uniques from filtered range
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract uniques from filtered range
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract uniques from filtered range
To copy only the visible filtered data
Edit GoTo Special Visible cells only Copy Paste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy only filtered range. | Excel Discussion (Misc queries) | |||
Counting a filtered range | Excel Discussion (Misc queries) | |||
How to copy a filtered range ? | Excel Discussion (Misc queries) | |||
Counting within a filtered range | Excel Worksheet Functions | |||
paste over a filtered range | Excel Discussion (Misc queries) |