Get Top 5 values in a list containing Duplicates

[adaytay]

Hey guys. It's been a while!

As I've spent the last few years in Access and SQL land, I've gotten a bit rusty in some areas of Excel, and this is bugging me - am trying to do something which should be straight-forward, but is turning into a head-scratcher!

I have a risk matrix, which has a load of scores in. The request from engineering is that they want to see the top 5 unique values (aka highest risk items) highlighted. Originally the workbook had conditional formatting, but it's not working. As there are >5 rows with the highest score, it only highlights these, and not the next 4 lowest numbers. I need a formula to identify the 5th highest unique number in the list - I'll then automatically trigger the conditional formatting to highlight the 5 highest values (ie => the 5th highest).

That bit I can do - I am just struggling on the formula to identify the 5 highest unique values. Can anyone assist?

Example values:

RPN
84
84
84
96
210
210
210
80
64
128
40
120
80
224
240
240
240
240
240
240
224
224
96

Appreciate your help - thanks

Ad

 

[Peter_SSs]

If using Excel 2010 or later try this.

Excel Workbook
	E
1
2	105
3	108
4	103
5	105
6	103
7	102
8	101
9	106
10	108
11	105
12	107
13	100
14	102
15
CF Top 5 Unique

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	1. / Formula is =E2>=AGGREGATE(14,6,E$2:E$14/(MATCH(E$2:E$14,E$2:E$14,0)=ROW(E$2:E$14)-ROW(E$2)+1),5)	Abc

 

[Peter_SSs]

For all versions.

Excel Workbook
	E
1
2	105
3	108
4	103
5	105
6	103
7	102
8	101
9	106
10	108
11	105
12	107
13	100
14	102
15
CF Top 5 Unique (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	1. / Formula is =E2>=LARGE(IF(MATCH(E$2:E$14,E$2:E$14,0)=ROW(E$2:E$14)-ROW(E$2)+1,E$2:E$14),5)	Abc

 

[adaytay]

Lost for words. Simply brilliant - thank you Peter!

 

[Peter_SSs]

Lost for words. Simply brilliant - thank you Peter!

Glad it worked for you. Thanks for letting us know.