Need ranking based on the score

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
961
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
Sort by date Sort by votes
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
kevin9999 said:
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.
Click to expand...
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
sksanjeev786 said:
thank you so much for your help on this Kevin :)

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

Regards,
Sanjeev
Click to expand...
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
kevin9999 said:
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 :(
Click to expand...

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
kevin9999 said:
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.
Click to expand...

AWESOME!!! Kevin :)

I always believe nothing is IM Possible.

Thanks Again!!

Regards,
Sanjeev
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
How to adjust (correct) Z Scores for missing values?
Replies
5
Views
511
Cubist
Cubist
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Scoring weighted permutations
Replies
1
Views
190
StephenCrump
S
B
  • Solved
Ranking Riddle
2
Replies
14
Views
1K
Fluff
Fluff
M
  • Question Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
403
mikefisher
M

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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