Need ranking based on the score

[sksanjeev786]

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	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						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]

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]

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),"")

 

[sksanjeev786]

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...!!!