PDA

View Full Version : Extracting Unique Highest Values



LOSS1574
10-06-2008, 11:21 AM
I need help extracting the top highest value records in an already filtered table. The below formula works great however it lists duplicate values. How can I alter the formula to remove the duplicates and list only the unique values?


=IF(ROWS($C$2:C2042)<=$C$2,LARGE(IF(SUBTOTAL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042),ROWS($C$2:C2)),"")


Column C2: C2042: contains product names
Column D2: D2042 contains product values
Both are already auto filtered on the same sheet. I would like to extract the top 5 highest unique values from column D and list them somewhere else on the sheet or another sheet.

I know using an advanced filter or pivot table would do the job however because of the format and other information listed in the table I'd like to use a formula to do the job.

Thanks for your help.

LOSS1574
10-06-2008, 01:01 PM
I need help extracting the top highest value records in an already filtered table. The below formula works great however it lists duplicate values. How can I alter the formula to remove the duplicates and list only the unique values?

=IF(ROWS($C$2:C2042)<=$C$2,LARGE(IF(SUBTOTAL(2,OFFSET($D$2:$D$2042,ROW($D$2:$D$2042)-ROW($D$2),,1)),$D$2:$D$2042),ROWS($C$2:C2)),"")

Column C2: C2042: contains product names
Column D2: D2042 contains product values
Both are already auto filtered on the same sheet. I would like to extract the top 5 highest unique values from column D and list them somewhere else on the sheet or another sheet.

I know using an advanced filter or pivot table would do the job however because of the format and other information listed in the table I'd like to use a formula to do the job.

Thanks for your help.





Attached a sample file maybe this will help understand what I'm trying to accomplish

Bob Phillips
10-06-2008, 02:31 PM
That formula doesn't work with the workbook you posted. Would you like to re-post, with the WORKING formula inserted?

LOSS1574
10-07-2008, 05:36 AM
No problem, I've attached the sample with the working formula. Please keep in mind all i need is the top 5 unique values.

Thanks

Bob Phillips
10-07-2008, 05:43 AM
You reckon that is working?

Your formula has ROWS($C$2:C2042)<=$C$2 in it, and seeing as C2 says Construction I fail to see how you think that is a working formula.

LOSS1574
10-07-2008, 07:10 AM
You reckon that is working?

Your formula has ROWS($C$2:C2042)<=$C$2 in it, and seeing as C2 says Construction I fail to see how you think that is a working formula.

I reckon the $C$2:C2042 should be $C$2:C20 per the sample table. but the formula is working to extract the top highest values (just not the unique values - no duplicates). Isn't it?

if you do not think it is or if you can suggest a better way of making it work please englighten me:)


=IF(ROWS($C$2:C20)<=$C$2,LARGE(IF(SUBTOTAL(2,OFFSET($D$2:$D$20,ROW($D$2:$D$20)-ROW($D$2),,1)),$D$2:$D$20),ROWS($C$2:C2)),"")

georgiboy
10-07-2008, 11:06 PM
I found the original macro you recorded and ammended it, it now puts unique numbers from column D into Column A. I then used a much simpler formula =LARGE(A:A,H2) to achieve the same result but from the now unique numbers in column A. I have hidden column A.

Sub ext32()
' macro ammended by Georgiboy
Range("D2:D1000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A2:A1000"), Unique:=True
End Sub

You now have a button to update the top 10 but if you wanted you could have this as a change event in the sheet.

hope this helps

GTO
10-08-2008, 01:11 AM
Greetings Loss1574,

Literally, your first test reads: "If 2041 is less than or equal to Construction", which I think (as in not sure, a bit beyond me) that its looking at the val of the letters to pass the test. Thus - I was unsure of the goal of the test as well.

That said, as you're only looking to return the top five values, how about:

=LARGE($D$2:$D$2042,RANK(LARGE($D$2:$D$2042,ROW($D2)-1),$D$2:$D$2042))

...for the first (uppermost cell), and:

=IF(LARGE($D$2:$D$2042,RANK(LARGE($D$2:$D$2042,ROW($D3)-1),$D$2:$D$2042))=D2,LARGE($D$2:$D$2042,RANK(LARGE($D$2:$D$2042,ROW($D3)),$ D$2:$D$2042)),LARGE($D$2:$D$2042,RANK(LARGE($D$2:$D$2042,ROW($D3)-1),$D$2:$D$2042)))

...for the second thru fifth values, the red numbers of course increasing per row...

Seems to work fine and hope this helps,

Mark

Krishna Kumar
10-08-2008, 02:21 AM
Hi,

In H2,

=LARGE($B$2:$B$20,RANK(LARGE(IF(SUBTOTAL(3,OFFSET($B$2:$B$20,ROW($B$2:$B$20 )-ROW($B$2),,1)),$B$2:$B$20),1),$B$2:$B$20))

In H3 and copied down,

=IF(LARGE($B$2:$B$20,RANK(LARGE(IF(SUBTOTAL(3,OFFSET($B$2:$B$20,ROW($B$2:$B $20)-ROW($B$2),,1)),$B$2:$B$20),ROWS($H$2:H3)),$B$2:$B$20))=H2,LARGE($B$2:$B$20, RANK(LARGE(IF(SUBTOTAL(3,OFFSET($B$2:$B$20,ROW($B$2:$B$20)-ROW($B$2),,1)),$B$2:$B$20),ROWS($H$2:H3)+1),$B$2:$B$20)),LARGE($B$2:$B$20,R ANK(LARGE(IF(SUBTOTAL(3,OFFSET($B$2:$B$20,ROW($B$2:$B$20)-ROW($B$2),,1)),$B$2:$B$20),ROWS($H$2:H3)),$B$2:$B$20)))

Both the formulas need to be confirmed with CTRL+SHIFT+ENTER

HTH