Formula to find highest unique values in a range

[SimonGeoghegan]

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.

[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:

[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:

[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

 

[Toadstool]

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

	A	B	C	D	E	F	G	H	I
1	Hospital				Risk	Risk Score		St Elsewhere	8
2	St Elsewhere				Poisoning from Hospital meal	18		Risk 8	20
3	St Elsewhere				Surgeon can't tell Left from Right	6		Poisoning from Hospital meal	18
4	St Elsewhere				Stub toe on bed	11		Hospital Bill gives heart attack	18
5	St Elsewhere				Hospital Bill gives heart attack	18		Risk 6	17
6	St Elsewhere				Risk 5	5		Risk 7	17
7	St Elsewhere				Risk 6	17		Risk 24	17
8	St Elsewhere				Risk 7	17		Risk 26	17
9	St Elsewhere				Risk 8	20		Risk 27	17
10	St Elsewhere				Risk 9	10
11	St Elsewhere				Risk 10	6

<tbody>
</tbody>

Risks​

Array Formulas Cell Formula 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]

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]

You're welcome!

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