Need ranking based on the score

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
884
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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

B
  • Solved
Ranking Riddle
2
Replies
14
Views
850
Fluff
Fluff
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
200
mikefisher
M
V
  • Question
List players based on scores in order per year
Replies
3
Views
60
Sanjeev1976
Sanjeev1976
A
  • Solved
merge QTY on textbox based on selected combobox on userform if the adjacent cell doesn't equal zero
Replies
2
Views
170
abdo meghari
A
D
  • Question
Automating selecting cells to create average
Replies
2
Views
401
Draff
D

Forum statistics

Threads
1,216,144
Messages
6,129,120
Members
449,488
Latest member
qh017

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