Need ranking based on the score

S

sksanjeev786

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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
  • Solved
How to calculate rank (subrank?) in NFL conference and division?
Replies
6
Views
97
JenniferMurphy
JenniferMurphy
S
  • Solved
Ranking with NET and with in the group
Replies
7
Views
109
sksanjeev786
S
S
  • Question Question
need the score based on range
Replies
0
Views
76
sksanjeev786
S
E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
92
earth
E
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

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