Formula to find highest unique values in a range

SimonGeoghegan

Board Regular
Joined
Nov 5, 2013
Messages
50
Hi All,

I have a range of risks with risk scores between 1-25
("F2:F2460"), and
details about each of these risks within the range ("E2:E2460")

I need to provide a list which will return the top 'X' amount of risks depending on what the risk scores are. I need to show a minimum of 5, however if there are further risks which score the same as the 5th highest risk, they would also show, so I could have maybe 12,13,14 etc. that I need to display.

I have a hospital name in Cell H1.

I have a formula in Cell I1 which identifies how many instances of the highest risk score there are, for the hospital in H1.

Code:
[FONT=Verdana]=SUM(IF(Risks!$A2:$A2460=$H$1,IF(Risks!$F$2:$F$2460>=LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),MIN(5,COUNT(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460)))),1)))[/FONT]

I then have a formula in Cells I2:I6 which tells me what the score is:

Code:
[FONT=Verdana]=IF(ROWS($I$2:I2)>$I$1,"",LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),ROWS($I$2:I2)))[/FONT]

And then a code in H2:H6 to give me the details:

Code:
[FONT=Verdana]=IF($I2="","",INDEX(Risks!$L$2:$L$2460,SMALL(IF(Risks!$A$2:$A$2460=$H$1,IF(Risks!$F$2:$F$2460=$I2,ROW(Risks!$A$2:$A$2460)-ROW($A$2)+1)),COUNTIFS($I$2:I2,I2))))[/FONT]

Is anybody able to point me in the right direction at all as to whether this can be achieved? I'm assuming it can, I just cannot think how to go about it!

Thanks in advance of any help!

Simon
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,397
Office Version
  1. 2016
Platform
  1. Windows
Simon,

I think I'm onboard and it looks like you almost have it but shouldn't that final H2:H6 (which should probably be H2:H99 to catch duplicates of the last risk score)
INDEX(Risks!$L$2:$L$2460

...actually be...
INDEX(Risks!$E$2:$E$2460


ABCDEFGHI
1HospitalRiskRisk ScoreSt Elsewhere8
2St ElsewherePoisoning from Hospital meal18Risk 820
3St ElsewhereSurgeon can't tell Left from Right6Poisoning from Hospital meal18
4St ElsewhereStub toe on bed11Hospital Bill gives heart attack18
5St ElsewhereHospital Bill gives heart attack18Risk 617
6St ElsewhereRisk 55Risk 717
7St ElsewhereRisk 617Risk 2417
8St ElsewhereRisk 717Risk 2617
9St ElsewhereRisk 820Risk 2717
10St ElsewhereRisk 910
11St ElsewhereRisk 106

<tbody>
</tbody>
Risks


Array Formulas
CellFormula
I1{=SUM(IF(Risks!$A2:$A2460=$H$1,IF(Risks!$F$2:$F$2460>=LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),MIN(5,COUNT(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460)))),1)))}
H2{=IF($I2="","",INDEX(Risks!$E$2:$E$2460,SMALL(IF(Risks!$A$2:$A$2460=$H$1,IF(Risks!$F$2:$F$2460=$I2,ROW(Risks!$A$2:$A$2460)-ROW($A$2)+1)),COUNTIFS($I$2:I2,I2))))}
I11{=IF(ROWS($I$2:I11)>$I$1,"",LARGE(IF(Risks!$A$2:$A$2460=$H$1,Risks!$F$2:$F$2460),ROWS($I$2:I11)))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>


Personally I'd use AGGREGATE to avoid the array formulae but you're so close it's probably not worth the surgery.

Regards,
Toadstool
 

SimonGeoghegan

Board Regular
Joined
Nov 5, 2013
Messages
50
Thanks for this Toadstool, works perfectly now! :)

Appreciate your help and nice to know that I wasn't too far away from what I wanted to achieve!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,397
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!

...and I trust my test data for column E risk descriptions is nowhere near the actual data ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,941
Messages
5,627,738
Members
416,271
Latest member
Bolweavil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top