Top 10 Formula Help

[Scotster]

Hi there,

I cannot for the life of me figure out how to go about extracting a top 10 table from a varying dataset. Any help would be appreciated.

Say I have the following:

Vendor TRT
Vendor1 10
Vendor2 12
Vendor6 36
Vendor3 13
Vendor1 42
Vendor4 15
Vendor6 18
Vendor4 23
Vendor2 32
Vendor3 30
Vendor1 18
Vendor2 7
Vendor3 7
Vendor4 18
Vendor5 12
Vendor6 30
Vendor7 41
Vendor8 19
Vendor9 50
Vendor10 75

How do I get the top 5 unique vendors based on TRT?

Many thanks

 

[James006]

Hello,

Have you tried to insert a Pivot table ... and use the Top 10 feature ...?

 

[Scotster]

Hello,

Have you tried to insert a Pivot table ... and use the Top 10 feature ...?

I have yes, I was hoping for a more elegant solution though as I would rather have a static table for the report, programmatically generating the table to begin with using VBA. I realise that I could do the same by inserting a pivot table, copying the data, pasting values and then re-formatting but I would rather do it with a formula if possible.

I always love a formula challenge also

 

[Aladin Akyurek]

Are you wanting these top 5 unique vendors based on individual TRT values or aggregated TRT values?

 

[Scotster]

Are you wanting these top 5 unique vendors based on individual TRT values or aggregated TRT values?

At the moment I'm looking for average TRT performance

 

[Aladin Akyurek]

At the moment I'm looking for average TRT performance

Top 5 highest averages?

 

[Scotster]

Top 5 highest averages?

No, the unique top 5 Vendors with the highest average. In my example the results would be....

1. Vendor 10 - 75
2. Vendor 9 - 50
3. Vendor 7 - 41
4. Vendor 6 - 28
5. Vendor 1 - 23

 

[Aladin Akyurek]

No, the unique top 5 Vendors with the highest average. In my example the results would be....

1. Vendor 10 - 75
2. Vendor 9 - 50
3. Vendor 7 - 41
4. Vendor 6 - 28
5. Vendor 1 - 23

That's actually yes...

Book1
	A	B	C	D	E	F	G
1	Vendor	TRT	AVG			5
2	Vendor1	10	23			5
3	Vendor2	12	17
4	Vendor6	36	28			VENDOR	AVG TRT
5	Vendor3	13	17			Vendor10	75
6	Vendor1	42				Vendor9	50
7	Vendor4	15	19			Vendor7	41
8	Vendor6	18				Vendor6	28
9	Vendor4	23				Vendor1	23.33333333
10	Vendor2	32
11	Vendor3	30
12	Vendor1	18
13	Vendor2	7
14	Vendor3	7
15	Vendor4	18
16	Vendor5	12	12
17	Vendor6	30
18	Vendor7	41	41
19	Vendor8	19	19
20	Vendor9	50	50
21	Vendor10	75	75
Sheet1

In C2 just enter and copy down:

=IF(COUNTIFS($A$2:A2,A2)=1,AVERAGEIFS($B$2:$B$21,$A$2:$A$21,A2),"")

In F1 enter: 5 (Top 5)

In F2 just enter:

=COUNTIFS($C$2:$C$21,">="&LARGE($C$2:$C$21,MIN(F1,COUNT($C$2:$C$21))))

in order to adjust Top 5 for possible ties.

In F5 control+shift+enter, not just enter, and copy down:

=IF($G5="","",INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$G5,ROW($A$2:$A$21)-ROW($A$2)+1),COUNTIFS($G$5:G5,G5))))

In G5 just enter and copy down:

=IF(ROWS($G$5:G5)>$F$2,"",LARGE($C$2:$C$21,ROWS($G$5:G5)))

 

[Scotster]

Great solution, very well thought out. Thanks for that

 

[Aladin Akyurek]

Great solution, very well thought out. Thanks for that

You are welcome. Thanks for the kind update.