Top five

[Evil Twin]

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

Insanity is a gift..

 

[mathchick]

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

 

[Oeldere]

This is a way.

Excel Workbook
	A	B	C	D	E	F	G	H	I
9	Location	Fire alarms	Key assist	Maintenance	Noise	totals
10	Manchester house	3	5	2	6	16		16	Manchester house
11	Windsor house	1	2	2	1	6		9	Henley Mews
12	Henley Mews	0	8	0	1	9		6	Windsor house
13
14
Blad3

Cell Formulas
Range	Formula
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),"")

 

[soccerkingpilot]

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

 

[Oeldere]

or with offset

Excel Workbook
	A	B	C	D	E	F	G	H
9	Location	Fire alarms	Key assist	Maintenance	Noise	totals
10	Manchester house	3	5	2	6	16	16	Manchester house
11	Windsor house	1	2	2	1	6	9	Henley Mews
12	Henley Mews	0	8	0	1	9	6	Windsor house
13
14
Blad3

Cell Formulas
Range	Formula
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,,),"")

 

[Evil Twin]

Hi Oeldere,

Thanks for the quick reply,
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

Insanity is a gift...

 

[Oeldere]

I make an example.

 

[Evil Twin]

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,

Insanity is a gift...

 

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

Insanity is a gift...

 

[Oeldere]

This is an way (with a help cel)

Blad3

	A	B	C	D	E	F	G	H	I
9	Location	Fire alarms	Key assist	Maintenance	Noise	totals
10	Manchester house	3	5	2	6	16	16,0001	16,00013	London Tower
11	Windsor house	1	2	2	1	6	6,00011	16,0001	Manchester house
12	Henley Mews	0	8	0	1	9	9,00012	9,00012	Henley Mews
13	London Tower	4	1	2	9	16	16,00013	6,00011	Windsor house
14

<THEAD>
</THEAD><TBODY>
</TBODY>

Worksheet Formulas Cell Formula 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 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

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