• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find top 5 values with duplicates [SOLVED]

A

AWells77

New Member
Hello Chandoo.org community.


I developed a Risk Register where each risk is associated with a number value (column B). I want the top 5 risk names (column A) to show on a dashboard based on their numeric value.


I can do this with Index + Match + Large functions as long as none of the number values are the same. However, there will be situations where more than one number value will be the same.

Is there way in Excel to handle this scenario? I truly appreciate your time and expertise.

[pre]
Code: 
A	B
1	Risk 1	36
2	Risk 2	36
3	Risk 3	20
4	Risk 4	15
5	Risk 5	5
6	Risk 6	30
[/pre]
 
Hi,


Can you please clarify what appears to be numbers in Column C: 36, 36, 20 etc? Are these the numeric risks?

Also, what do you want to do if (say) the 5th and 6th risks have the same numeric value? Does it matter which is on your dashboard?


I would do this by "individualising" the numeric data - I do this with a helper column and (on the assumption this is column C, it would have the formula =c2+(row()/1000000) which I would fill down.


I would then have as a new column A a RANK function.


I would use that new column A as the basis for a VLOOKUP table elsewhere in the workbook to "sort" the data in a form to use on the dashboard.


It may be clunky but it has worked for me loads.


Cheers.
 
Similar to OliverCrispin's idea...

Helper column (I'll assume col E) with this formula:

=RANK(B2,$B$2:$B$10,0)+ROW()/10000


Then, to get list of names for top x entries:

=INDEX($A$2:$A$10,MATCH(SMALL($E$2:$E$10,ROW($A1)),$E$2:$E$10,0))


Copy down as far as is desired.
 
Hello and thank you for your reply. The risk values are listed: 36, 30, etc which would be in column C. Based on this number I would like to pull the contents of column B associated with the highest risk values into the dashboard. If, say, two risks had a value of 36 (the highest possible on my scale), I would want them both pulled into the dashboard.


I hope this is clearer.
 
Hi AWells77`


Can you please check the attached..


https://dl.dropboxusercontent.com/u/78831150/Excel/Find%20top%205%20values%20with%20duplicates%28AWells77%29.xlsx


Select F2:F6 (in case of top 5) then write formula as

Code: 
=INDEX($B$2:$B$7,MATCH(LARGE($C$2:$C$7+(ROW($C$2:$C$7)/1000),ROW($A$1:$A$5)),$C$2:$C$7+(ROW($C$2:$C$7)/1000),0))

Please confirm the formula by pressing Ctrl + Shift + Enter, not just enter..


Regards,

Deb
 
@Debraj Roy

Hi!

Maybe for retrieving tied values in original order replacing this:

(ROW($C$2:$C$7)/1000)

by this:

.5-(ROW($C$2:$C$7)/1000)

works?

Haven't tested it.

Regards!
 
While that formula works, you could also just use a pivottable, with the data field being COUNT OF SOMEFIELD and the Top X set to Top 5.
 
I truly appreciate everyone's expertise and time helping to resolve my issue. Debraj's recommendation works wonderfully. Again, thank you!
 
You must log in or register to reply here.
Back
Top