Need ranking based on the score

sksanjeev786 · Jun 22, 2022

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

3

1

0

Average

The Home Depot

Lowe's

Amazon

Ferguson

Hajoca

Winsupply

SupplyHouse.com

Johnstone Supply

Grainger

Build.com

Lennox

MSC Industrial

United Refrigeration

Core & Main

MRC Global

HD Supply

Distribution Now

Viking

Winwholesale

Windustrial

Tyco

Southern Pipe & Supply

RE Michel

HD Fowler

Carrier Enterprise

4

5

[2124.xlsx]

SS2

5

Meaningful (indexed against first wave category average)

97

184

137

0

99

69

0

98

0

74

0

78

0

64

0

64

0

6

[2124.xlsx]

SS2

6

Different (indexed against first wave category average)

103

178

123

0

97

92

0

99

0

82

0

78

0

82

0

89

0

7

[2124.xlsx]

SS2

7

Salient (indexed against first wave category average)

107

181

176

0

82

94

0

104

0

82

0

79

0

81

0

73

0

8

[2124.xlsx]

SS2

18

Power % (share)

2%

28%

20%

0%

9%

7%

0%

11%

0%

7%

0%

7%

0%

6%

0%

6%

0%

9

[2124.xlsx]

SS2

19

Premium v2 (indexed vs. first wave category average)

1.04

1.28

1.15

0.00

1.03

0.99

0.00

1.03

0.00

0.96

0.00

0.97

0.00

0.96

0.00

0.95

0.00

10

11

12

Output

13

14

The Home Depot

28%

15

Lowe's

20%

16

Grainger

11%

17

Ferguson

9%

18

MSC Industrial

7%

19

MRC Global

7%

20

Hajoca

7%

21

Distribution Now

6%

22

Windustrial

6%

23

Amazon

0%

24

Winsupply

0%

25

SupplyHouse.com

0%

26

Johnstone Supply

0%

27

Build.com

0%

28

Lennox

0%

29

United Refrigeration

0%

30

Core & Main

0%

31

HD Supply

0%

32

Viking

0%

33

Winwholesale

0%

34

Tyco

0%

35

Southern Pipe & Supply

0%

36

RE Michel

0%

37

HD Fowler

0%

38

Carrier Enterprise

0%

Sheet1

sksanjeev786 · Jun 24, 2022

kevin9999 said:
How about:

test.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD
1
2
3 1 0 Average The Home Depot Lowe's Amazon Ferguson Hajoca Winsupply SupplyHouse.com Johnstone Supply Grainger Build.com Lennox MSC Industrial United Refrigeration Core & Main MRC Global HD Supply Distribution Now Viking Winwholesale Windustrial Tyco Southern Pipe & Supply RE Michel HD Fowler Carrier Enterprise
4
5 [2124.xlsx] SS2 5 Meaningful (indexed against first wave category average) 97 184 137 0 99 69 0 0 0 98 0 0 74 0 0 78 0 64 0 0 64 0 0 0 0 0
6 [2124.xlsx] SS2 6 Different (indexed against first wave category average) 103 178 123 0 97 92 0 0 0 99 0 0 82 0 0 78 0 82 0 0 89 0 0 0 0 0
7 [2124.xlsx] SS2 7 Salient (indexed against first wave category average) 107 181 176 0 82 94 0 0 0 104 0 0 82 0 0 79 0 81 0 0 73 0 0 0 0 0
8 [2124.xlsx] SS2 18 Power % (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] SS2 19 Premium v2 (indexed vs. first wave category average) 1.04 1.28 1.15 0.00 1.03 0.99 0.00 0.00 0.00 1.03 0.00 0.00 0.96 0.00 0.00 0.97 0.00 0.96 0.00 0.00 0.95 0.00 0.00 0.00 0.00 0.00
10
11
12 Output
13
14 The Home Depot 28%
15 Lowe's 20%
16 Grainger 11%
17 Ferguson 9%
18 MSC Industrial 7%
19 MRC Global 7%
20 Hajoca 7%
21 Distribution Now 6%
22 Windustrial 6%
23
Sheet1
Cell Formulas
Range Formula
F14:G22 F14 =INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTIF($F$8:$AD$8,">0")),{1,6})
Dynamic array formulas.

Hi Kevin,

I have another request on the same data file.

Ferguson is my main brand so can I get that in 1st position and rest looks same what we have done earlier.

Thanks in advance
Regards,
Sanjeev

bebo021999 · Jun 24, 2022

I dont have 365. But it works for 2016 or earlier

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

2

3

1

0

Average

The Home Depot

Lowe's

Amazon

Ferguson

Hajoca

Winsupply

SupplyHouse.com

Johnstone Supply

Grainger

Build.com

Lennox

MSC Industrial

United Refrigeration

Core & Main

MRC Global

HD Supply

Distribution Now

Viking

Winwholesale

Windustrial

Tyco

Southern Pipe & Supply

RE Michel

HD Fowler

Carrier Enterprise

4

5

[2124.xlsx]

SS2

5

Meaningful (indexed against first wave category average)

97.23432

183.7900085

136.6236

0

98.66078

69.10673

0

98.18241

0

73.99705

0

78.11188

0

64.25554

0

64.39245

0

6

[2124.xlsx]

SS2

6

Different (indexed against first wave category average)

102.7886

177.9639769

123.346

0

96.55163

92.35138

0

98.78682

0

81.58478

0

78.08882

0

82.45073

0

88.58923

0

7

[2124.xlsx]

SS2

7

Salient (indexed against first wave category average)

106.5961

180.627649

176.3788

0

81.71549

93.76618

0

104.4586

0

82.33225

0

78.72557

0

80.53764

0

73.39269

0

8

[2124.xlsx]

SS2

18

Power % (share)

2%

28%

20%

0%

9%

7%

0%

11%

0%

7%

0%

7%

0%

6%

0%

6%

0%

9

[2124.xlsx]

SS2

19

Premium v2 (indexed vs. first wave category average)

1.038237

1.283208159

1.147788

0

1.028272

0.992376

0

1.027736

0

0.961424

0

0.969796

0

0.962779

0

0.950395

0

10

11

12

Output

13

14

15

16

17

18

19

20

21

22

Sheet2

Cell Formulas
Range	Formula
F13	F13	=I3
G13	G13	=I8
F14:F22	F14	=IFERROR(LOOKUP(2,1/($E$8:$AD$8=AGGREGATE(14,6,$E$8:$AD$8/($E$8:$AD$8>0)/($E$3:$AD$3<>$F$13),ROWS($1:1))),$E$3:$AD$3),"")
G14:G22	G14	=IFERROR(LOOKUP(2,1/($E$8:$AD$8=AGGREGATE(14,6,$E$8:$AD$8/($E$8:$AD$8>0)/($E$3:$AD$3<>$F$13),ROWS($1:1))),$E$8:$AD$8),"")

sksanjeev786 · Jun 24, 2022

bebo021999 said:
I dont have 365. But it works for 2016 or earlier

Book1
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD
1
2
3 1 0 Average The Home Depot Lowe's Amazon Ferguson Hajoca Winsupply SupplyHouse.com Johnstone Supply Grainger Build.com Lennox MSC Industrial United Refrigeration Core & Main MRC Global HD Supply Distribution Now Viking Winwholesale Windustrial Tyco Southern Pipe & Supply RE Michel HD Fowler Carrier Enterprise
4
5 [2124.xlsx] SS2 5 Meaningful (indexed against first wave category average) 97.23432 183.7900085 136.6236 0 98.66078 69.10673 0 0 0 98.18241 0 0 73.99705 0 0 78.11188 0 64.25554 0 0 64.39245 0 0 0 0 0
6 [2124.xlsx] SS2 6 Different (indexed against first wave category average) 102.7886 177.9639769 123.346 0 96.55163 92.35138 0 0 0 98.78682 0 0 81.58478 0 0 78.08882 0 82.45073 0 0 88.58923 0 0 0 0 0
7 [2124.xlsx] SS2 7 Salient (indexed against first wave category average) 106.5961 180.627649 176.3788 0 81.71549 93.76618 0 0 0 104.4586 0 0 82.33225 0 0 78.72557 0 80.53764 0 0 73.39269 0 0 0 0 0
8 [2124.xlsx] SS2 18 Power % (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] SS2 19 Premium v2 (indexed vs. first wave category average) 1.038237 1.283208159 1.147788 0 1.028272 0.992376 0 0 0 1.027736 0 0 0.961424 0 0 0.969796 0 0.962779 0 0 0.950395 0 0 0 0 0
10
11
12 Output
13 Ferguson 9.30%
14 The Home Depot 27.88%
15 Lowe's 19.75%
16 Grainger 10.72%
17 MSC Industrial 7.25%
18 MRC Global 6.83%
19 Hajoca 6.81%
20 Distribution Now 5.96%
21 Windustrial 5.52%
22 Average 2.08%
Sheet2
Cell Formulas
Range Formula
F13 F13 =I3
G13 G13 =I8
F14:F22 F14 =IFERROR(LOOKUP(2,1/($E$8:$AD$8=AGGREGATE(14,6,$E$8:$AD$8/($E$8:$AD$8>0)/($E$3:$AD$3<>$F$13),ROWS($1:1))),$E$3:$AD$3),"")
G14:G22 G14 =IFERROR(LOOKUP(2,1/($E$8:$AD$8=AGGREGATE(14,6,$E$8:$AD$8/($E$8:$AD$8>0)/($E$3:$AD$3<>$F$13),ROWS($1:1))),$E$8:$AD$8),"")

Thank you so much for your help...!!!

Need ranking based on the score

sksanjeev786

Well-known Member

sksanjeev786

Well-known Member

Excel Facts

bebo021999

Well-known Member

sksanjeev786

Well-known Member

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
F14:G22	F14	=INDEX(SORT(TRANSPOSE($F$3:$AD$9),6,-1),SEQUENCE(COUNTIF($F$8:$AD$8,">0")),{1,6})
Dynamic array formulas.

Need ranking based on the score

sksanjeev786

Well-known Member

sksanjeev786

Well-known Member

Excel Facts

bebo021999

Well-known Member

sksanjeev786

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock