# Top five

#### Evil Twin

##### New Member
Hi Everyone,

I have a sheet full of data set out as below:

Rows A1:A50 have a Location name
Columns B1:U1 are Event types

 Location Fire alarms Key assist Maintenance Noise totals Manchester house 3 5 2 6 16 Windsor house 1 2 2 1 6 Henley Mews 0 8 0 1 9

<tbody>
</tbody>

I need to pick the top five locations with the highest totals overall, and show the location name and total in another part of the worksheet but in descending order, ( I dont want to sort the original data where it is as it is also used for other calculations on the sheet)

Also is it possible to show the top five locations and all the data for each event ( ie the whole row) but in another part of this worksheet as above?

All the best,
Evil Twin

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
you want the large function. You can specify whether you want the 1st largest, 2nd largest, etc.

This is a way.
Excel Workbook
ABCDEFGHI
9LocationFire alarmsKey assistMaintenanceNoisetotals
10Manchester house35261616Manchester house
11Windsor house122169Henley Mews
12Henley Mews080196Windsor house
13
14
Cell Formulas
RangeFormula
H10=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
H11=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
H12=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
H13=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
H14=IFERROR(LARGE(F14:F16,ROW()-9),"")
I10=IFERROR(INDIRECT("A"&MATCH(H10,\$F\$10:\$F\$14,0)+9),"")
I11=IFERROR(INDIRECT("A"&MATCH(H11,\$F\$10:\$F\$14,0)+9),"")
I12=IFERROR(INDIRECT("A"&MATCH(H12,\$F\$10:\$F\$14,0)+9),"")
I13=IFERROR(INDIRECT("A"&MATCH(H13,\$F\$10:\$F\$14,0)+9),"")
I14=IFERROR(INDIRECT("A"&MATCH(H14,\$F\$10:\$F\$14,0)+9),"")

Unfortunately, I'm not sure of a good approach for your issue. Just a heads up though, it may be more useful to have your other data that you mentioned use a vlookup. That way, in case this data is sorted, it won't be affected.

-Austin

or with offset
Excel Workbook
ABCDEFGH
9LocationFire alarmsKey assistMaintenanceNoisetotals
10Manchester house35261616Manchester house
11Windsor house122169Henley Mews
12Henley Mews080196Windsor house
13
14
Cell Formulas
RangeFormula
G10=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
G11=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
G12=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
G13=IFERROR(LARGE(\$F\$10:\$F\$12,ROW()-9),"")
G14=IFERROR(LARGE(D14:D16,ROW()-9),"")
H10=IFERROR(OFFSET(\$F\$10,MATCH(\$I10,\$F\$10:\$F\$14,0)-1,-5,,),"")
H11=IFERROR(OFFSET(\$F\$10,MATCH(\$I11,\$F\$10:\$F\$14,0)-1,-5,,),"")
H12=IFERROR(OFFSET(\$F\$10,MATCH(\$I12,\$F\$10:\$F\$14,0)-1,-5,,),"")
H13=IFERROR(OFFSET(\$F\$10,MATCH(\$I13,\$F\$10:\$F\$14,0)-1,-5,,),"")
H14=IFERROR(OFFSET(\$F\$10,MATCH(\$I14,\$F\$10:\$F\$14,0)-1,-5,,),"")

Hi Oeldere,

I dont understand the formula but that works just fine, but how do I handle duplicate totals, if two different locations show the same totals?

All the best,
Evil Twin

I make an example.

Unfortunately, I'm not sure of a good approach for your issue. Just a heads up though, it may be more useful to have your other data that you mentioned use a vlookup. That way, in case this data is sorted, it won't be affected.

-Austin

Hi Austin,
Thank you for the heads up, I can see I have lots to learn here

All the best,
Evil Twin,

you want the large function. You can specify whether you want the 1st largest, 2nd largest, etc.

Hi mathchick,

Sorry for the late reply, I have been playing with the formula from Oeldere who has the same idea as yourself,

All the best,
Evil Twin,

This is an way (with a help cel)

ABCDEFGHI
9LocationFire alarmsKey assistMaintenanceNoisetotals
10Manchester house35261616,000116,00013London Tower
11Windsor house122166,0001116,0001Manchester house
12Henley Mews080199,000129,00012Henley Mews
13London Tower41291616,000136,00011Windsor house
14

</TBODY>

Worksheet Formulas
CellFormula
H10=IFERROR(LARGE(\$G\$10:\$G\$15,ROW()-9),"")
I10=IFERROR(OFFSET(\$F\$10,MATCH(\$H10,\$G\$10:\$G\$14,0)-1,-5,,),"")
H11=IFERROR(LARGE(\$G\$10:\$G\$15,ROW()-9),"")
I11=IFERROR(OFFSET(\$F\$10,MATCH(\$H11,\$G\$10:\$G\$14,0)-1,-5,,),"")
H12=IFERROR(LARGE(\$G\$10:\$G\$15,ROW()-9),"")
I12=IFERROR(OFFSET(\$F\$10,MATCH(\$H12,\$G\$10:\$G\$14,0)-1,-5,,),"")
H13=IFERROR(LARGE(\$G\$10:\$G\$15,ROW()-9),"")
I13=IFERROR(OFFSET(\$F\$10,MATCH(\$H13,\$G\$10:\$G\$14,0)-1,-5,,),"")
H14=IFERROR(LARGE(\$G\$10:\$G\$15,ROW()-9),"")
I14=IFERROR(OFFSET(\$F\$10,MATCH(\$H14,\$G\$10:\$G\$14,0)-1,-5,,),"")
G10=+F10+ROW()/100000
G11=+F11+ROW()/100000
G12=+F12+ROW()/100000
F13=SUM(B13:E13)
G13=+F13+ROW()/100000

</TBODY>

<TBODY>
</TBODY>

You can show your valua with no valua behind the comma.
I let it this way, to show you.

Replies
6
Views
557
Replies
0
Views
615
Replies
15
Views
1K
Replies
6
Views
379
Replies
1
Views
1K

1,202,984
Messages
6,052,916
Members
444,613
Latest member
questionexcelz

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