# Formula to find highest unique values in a range

#### SimonGeoghegan

##### Board Regular
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:

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

##### Well-known Member
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,

#### SimonGeoghegan

##### Board Regular
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!

##### Well-known Member
You're welcome!

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

Replies
3
Views
112
Replies
1
Views
192
Replies
5
Views
109
Replies
1
Views
102
Replies
1
Views
92

1,127,600
Messages
5,625,741
Members
416,132
Latest member
Chandan Choubey

### 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.

### Which adblocker are you using?

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

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