Need ranking based on the score

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
995
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I need a ranking based on the D8 data excluding (Average Data) and Brand order from F3. I have kept the brand order for example F14 to F38


book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
310AverageThe Home DepotLowe'sAmazonFergusonHajocaWinsupplySupplyHouse.comJohnstone SupplyGraingerBuild.comLennoxMSC IndustrialUnited RefrigerationCore & MainMRC GlobalHD SupplyDistribution NowVikingWinwholesaleWindustrialTycoSouthern Pipe & SupplyRE MichelHD FowlerCarrier Enterprise
4
5[2124.xlsx]SS25Meaningful (indexed against first wave category average)97184137099690009800740078064006400000
6[2124.xlsx]SS26Different (indexed against first wave category average)103178123097920009900820078082008900000
7[2124.xlsx]SS27Salient (indexed against first wave category average)1071811760829400010400820079081007300000
8[2124.xlsx]SS218Power % (share)2%28%20%0%9%7%0%0%0%11%0%0%7%0%0%7%0%6%0%0%6%0%0%0%0%0%
9[2124.xlsx]SS219Premium v2 (indexed vs. first wave category average)1.041.281.150.001.030.990.000.000.001.030.000.000.960.000.000.970.000.960.000.000.950.000.000.000.000.00
10
11
12Output
13
14The Home Depot28%
15Lowe's20%
16Grainger11%
17Ferguson9%
18MSC Industrial7%
19MRC Global7%
20Hajoca7%
21Distribution Now6%
22Windustrial6%
23Amazon0%
24Winsupply0%
25SupplyHouse.com0%
26Johnstone Supply0%
27Build.com0%
28Lennox0%
29United Refrigeration0%
30Core & Main0%
31HD Supply0%
32Viking0%
33Winwholesale0%
34Tyco0%
35Southern Pipe & Supply0%
36RE Michel0%
37HD Fowler0%
38Carrier Enterprise0%
Sheet1
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Please try the following

test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
310AverageThe Home DepotLowe'sAmazonFergusonHajocaWinsupplySupplyHouse.comJohnstone SupplyGraingerBuild.comLennoxMSC IndustrialUnited RefrigerationCore & MainMRC GlobalHD SupplyDistribution NowVikingWinwholesaleWindustrialTycoSouthern Pipe & SupplyRE MichelHD FowlerCarrier Enterprise
4
5[2124.xlsx]SS25Meaningful (indexed against first wave category average)97184137099690009800740078064006400000
6[2124.xlsx]SS26Different (indexed against first wave category average)103178123097920009900820078082008900000
7[2124.xlsx]SS27Salient (indexed against first wave category average)1071811760829400010400820079081007300000
8[2124.xlsx]SS218Power % (share)2%28%20%0%9%7%0%0%0%11%0%0%7%0%0%7%0%6%0%0%6%0%0%0%0%0%
9[2124.xlsx]SS219Premium v2 (indexed vs. first wave category average)1.041.281.150.001.030.990.000.000.001.030.000.000.960.000.000.970.000.960.000.000.950.000.000.000.000.00
10
11
12Output
13
14The Home Depot28%
15Lowe's20%
16Grainger11%
17Ferguson9%
18MSC Industrial7%
19MRC Global7%
20Hajoca7%
21Distribution Now6%
22Windustrial6%
23Amazon0%
24Winsupply0%
25SupplyHouse.com0%
26Johnstone Supply0%
27Build.com0%
28Lennox0%
29United Refrigeration0%
30Core & Main0%
31HD Supply0%
32Viking0%
33Winwholesale0%
34Tyco0%
35Southern Pipe & Supply0%
36RE Michel0%
37HD Fowler0%
38Carrier Enterprise0%
39
Sheet1
Cell Formulas
RangeFormula
F14:G38F14=INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTA($F$3:$AD$3)),{1,6})
Dynamic array formulas.
 
Upvote 0
Please try the following

test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
310AverageThe Home DepotLowe'sAmazonFergusonHajocaWinsupplySupplyHouse.comJohnstone SupplyGraingerBuild.comLennoxMSC IndustrialUnited RefrigerationCore & MainMRC GlobalHD SupplyDistribution NowVikingWinwholesaleWindustrialTycoSouthern Pipe & SupplyRE MichelHD FowlerCarrier Enterprise
4
5[2124.xlsx]SS25Meaningful (indexed against first wave category average)97184137099690009800740078064006400000
6[2124.xlsx]SS26Different (indexed against first wave category average)103178123097920009900820078082008900000
7[2124.xlsx]SS27Salient (indexed against first wave category average)1071811760829400010400820079081007300000
8[2124.xlsx]SS218Power % (share)2%28%20%0%9%7%0%0%0%11%0%0%7%0%0%7%0%6%0%0%6%0%0%0%0%0%
9[2124.xlsx]SS219Premium v2 (indexed vs. first wave category average)1.041.281.150.001.030.990.000.000.001.030.000.000.960.000.000.970.000.960.000.000.950.000.000.000.000.00
10
11
12Output
13
14The Home Depot28%
15Lowe's20%
16Grainger11%
17Ferguson9%
18MSC Industrial7%
19MRC Global7%
20Hajoca7%
21Distribution Now6%
22Windustrial6%
23Amazon0%
24Winsupply0%
25SupplyHouse.com0%
26Johnstone Supply0%
27Build.com0%
28Lennox0%
29United Refrigeration0%
30Core & Main0%
31HD Supply0%
32Viking0%
33Winwholesale0%
34Tyco0%
35Southern Pipe & Supply0%
36RE Michel0%
37HD Fowler0%
38Carrier Enterprise0%
39
Sheet1
Cell Formulas
RangeFormula
F14:G38F14=INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTA($F$3:$AD$3)),{1,6})
Dynamic array formulas.
thank you so much for your help on this Kevin :)

Can we exclude the brands with a "0" score while ranking?

Regards,
Sanjeev
 
Upvote 0
thank you so much for your help on this Kevin :)

Can we exclude the brands with a "0" score while ranking?

Regards,
Sanjeev
It might be possible with the inclusion of the Filter() function somewhere within the existing formula, but I haven't been able to work out where exactly. Hopefully, someone with better Excel knowledge than me can work it out - or suggest a more elegant solution?
Sorry :(
 
Upvote 0
It might be possible with the inclusion of the Filter() function somewhere within the existing formula, but I haven't been able to work out where exactly. Hopefully, someone with better Excel knowledge than me can work it out - or suggest a more elegant solution?
Sorry :(

not an issue. Your support on this works very well for us A BIG THANK YOU..!!!
 
Upvote 0
How about:

test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1
2
310AverageThe Home DepotLowe'sAmazonFergusonHajocaWinsupplySupplyHouse.comJohnstone SupplyGraingerBuild.comLennoxMSC IndustrialUnited RefrigerationCore & MainMRC GlobalHD SupplyDistribution NowVikingWinwholesaleWindustrialTycoSouthern Pipe & SupplyRE MichelHD FowlerCarrier Enterprise
4
5[2124.xlsx]SS25Meaningful (indexed against first wave category average)97184137099690009800740078064006400000
6[2124.xlsx]SS26Different (indexed against first wave category average)103178123097920009900820078082008900000
7[2124.xlsx]SS27Salient (indexed against first wave category average)1071811760829400010400820079081007300000
8[2124.xlsx]SS218Power % (share)2%28%20%0%9%7%0%0%0%11%0%0%7%0%0%7%0%6%0%0%6%0%0%0%0%0%
9[2124.xlsx]SS219Premium v2 (indexed vs. first wave category average)1.041.281.150.001.030.990.000.000.001.030.000.000.960.000.000.970.000.960.000.000.950.000.000.000.000.00
10
11
12Output
13
14The Home Depot28%
15Lowe's20%
16Grainger11%
17Ferguson9%
18MSC Industrial7%
19MRC Global7%
20Hajoca7%
21Distribution Now6%
22Windustrial6%
23
Sheet1
Cell Formulas
RangeFormula
F14:G22F14=INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTIF($F$8:$AD$8,">0")),{1,6})
Dynamic array formulas.
 
Upvote 0
Solution
How about:

test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1
2
310AverageThe Home DepotLowe'sAmazonFergusonHajocaWinsupplySupplyHouse.comJohnstone SupplyGraingerBuild.comLennoxMSC IndustrialUnited RefrigerationCore & MainMRC GlobalHD SupplyDistribution NowVikingWinwholesaleWindustrialTycoSouthern Pipe & SupplyRE MichelHD FowlerCarrier Enterprise
4
5[2124.xlsx]SS25Meaningful (indexed against first wave category average)97184137099690009800740078064006400000
6[2124.xlsx]SS26Different (indexed against first wave category average)103178123097920009900820078082008900000
7[2124.xlsx]SS27Salient (indexed against first wave category average)1071811760829400010400820079081007300000
8[2124.xlsx]SS218Power % (share)2%28%20%0%9%7%0%0%0%11%0%0%7%0%0%7%0%6%0%0%6%0%0%0%0%0%
9[2124.xlsx]SS219Premium v2 (indexed vs. first wave category average)1.041.281.150.001.030.990.000.000.001.030.000.000.960.000.000.970.000.960.000.000.950.000.000.000.000.00
10
11
12Output
13
14The Home Depot28%
15Lowe's20%
16Grainger11%
17Ferguson9%
18MSC Industrial7%
19MRC Global7%
20Hajoca7%
21Distribution Now6%
22Windustrial6%
23
Sheet1
Cell Formulas
RangeFormula
F14:G22F14=INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTIF($F$8:$AD$8,">0")),{1,6})
Dynamic array formulas.

AWESOME!!! Kevin :)

I always believe nothing is IM Possible.

Thanks Again!!

Regards,
Sanjeev
 
Upvote 0

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top