Ranking with NET and with in the group

[sksanjeev786]

Hi Team,

I need a ranking Based on NET first and the within the group

for example : I have done the NET ranking Row no 3,10,16,21,25 and 29 in column B and then rank within the group row 4-9, 12-15
we will always have NET wording in data file

i have done manually Output F to G column.

book2
	B	C	D	E	F	G
1	Input				Ouput
2
3	(NET) IN/AROUND STORE	87%			(NET) IN/AROUND STORE	87%
4	Featured prominently on the shelf at a store	63%			Saw product inside of a store	66%
5	Saw signs or ads inside or outside of a store	53%			Featured prominently on the shelf at a store	63%
6	Saw product inside of a store	66%			Saw signs or ads inside or outside of a store	53%
7	Heard about it from a store clerk	34%			Visited a brand shop / pop-up store	43%
8	Heard about it from a representative of the brand at a store	33%			Heard about it from a store clerk	34%
9	Visited a brand shop / pop-up store	43%			Heard about it from a representative of the brand at a store	33%
10	(NET) ADVERTISEMENT	66%			(NET) WOM / OVERHEARD	80%
11	Saw an ad on TV or video streaming service	36%			Saw an adult tobacco/nicotine consumer using it	56%
12	Saw an ad in a magazine	40%			Recommended by friends or family	53%
13	Saw an ad online (banner, pop-up)	40%			Heard about it from an adult tobacco/nicotine consumer	45%
14	Saw the brand advertised at an event/concert	31%			Saw in news article or clip	32%
15	Heard an ad when listening to the radio, streaming music, or a podcast	35%			(NET) ONLINE / SOCIAL MEDIA	72%
16	(NET) WOM / OVERHEARD	80%			Saw it on an online tobacco retailer website	44%
17	Recommended by friends or family	53%			Went to a brand's website	44%
18	Heard about it from an adult tobacco/nicotine consumer	45%			Saw a brand's social media post or went to their social media page	40%
19	Saw an adult tobacco/nicotine consumer using it	56%			Read about it when doing an online search	39%
20	Saw in news article or clip	32%			Saw an adult tobacco/nicotine consumer post about it on social media	38%
21	(NET) BRAND MARKETING	60%			(NET) ADVERTISEMENT	66%
22	Received something in the mail from the brand (flyer, leaflet, etc.)	35%			Saw an ad in a magazine	40%
23	Received an email from the brand	42%			Saw an ad online (banner, pop-up)	40%
24	Received a text message from the brand	33%			Saw an ad on TV or video streaming service	36%
25	(NET) PROMOTIONAL	54%			Heard an ad when listening to the radio, streaming music, or a podcast	35%
26	Used paper coupon for the brand	23%			Saw the brand advertised at an event/concert	31%
27	Used mobile coupon for the brand	36%			(NET) BRAND MARKETING	60%
28	Participated in the brand's loyalty or subscription program	35%			Received an email from the brand	42%
29	(NET) ONLINE / SOCIAL MEDIA	72%			Received something in the mail from the brand (flyer, leaflet, etc.)	35%
30	Saw it on an online tobacco retailer website	44%			Received a text message from the brand	33%
31	Read about it when doing an online search	39%			(NET) PROMOTIONAL	54%
32	Went to a brand's website	44%			Used mobile coupon for the brand	36%
33	Saw a brand's social media post or went to their social media page	40%			Participated in the brand's loyalty or subscription program	35%
34	Saw an adult tobacco/nicotine consumer post about it on social media	38%			Used paper coupon for the brand	23%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B34	Cell Value	contains "(NET)"	text	NO
F3:F34	Cell Value	contains "(NET)"	text	NO

 

[Peter_SSs]

Could we use a helper column (could be hidden)?

24 11 22.xlsm
	B	C	D	E	F	G
1	Input				Ouput
2
3	(NET) IN/AROUND STORE	87%	870870		(NET) IN/AROUND STORE	87%
4	Featured prominently on the shelf at a store	63%	870630		Saw product inside of a store	66%
5	Saw signs or ads inside or outside of a store	53%	870530		Featured prominently on the shelf at a store	63%
6	Saw product inside of a store	66%	870660		Saw signs or ads inside or outside of a store	53%
7	Heard about it from a store clerk	34%	870340		Visited a brand shop / pop-up store	43%
8	Heard about it from a representative of the brand at a store	33%	870330		Heard about it from a store clerk	34%
9	Visited a brand shop / pop-up store	43%	870430		Heard about it from a representative of the brand at a store	33%
10	(NET) ADVERTISEMENT	66%	660660		(NET) WOM / OVERHEARD	80%
11	Saw an ad on TV or video streaming service	36%	660360		Saw an adult tobacco/nicotine consumer using it	56%
12	Saw an ad in a magazine	40%	660400		Recommended by friends or family	53%
13	Saw an ad online (banner, pop-up)	40%	660400		Heard about it from an adult tobacco/nicotine consumer	45%
14	Saw the brand advertised at an event/concert	31%	660310		Saw in news article or clip	32%
15	Heard an ad when listening to the radio, streaming music, or a podcast	35%	660350		(NET) ONLINE / SOCIAL MEDIA	72%
16	(NET) WOM / OVERHEARD	80%	800800		Saw it on an online tobacco retailer website	44%
17	Recommended by friends or family	53%	800530		Went to a brand's website	44%
18	Heard about it from an adult tobacco/nicotine consumer	45%	800450		Saw a brand's social media post or went to their social media page	40%
19	Saw an adult tobacco/nicotine consumer using it	56%	800560		Read about it when doing an online search	39%
20	Saw in news article or clip	32%	800320		Saw an adult tobacco/nicotine consumer post about it on social media	38%
21	(NET) BRAND MARKETING	60%	600600		(NET) ADVERTISEMENT	66%
22	Received something in the mail from the brand (flyer, leaflet, etc.)	35%	600350		Saw an ad in a magazine	40%
23	Received an email from the brand	42%	600420		Saw an ad online (banner, pop-up)	40%
24	Received a text message from the brand	33%	600330		Saw an ad on TV or video streaming service	36%
25	(NET) PROMOTIONAL	54%	540540		Heard an ad when listening to the radio, streaming music, or a podcast	35%
26	Used paper coupon for the brand	23%	540230		Saw the brand advertised at an event/concert	31%
27	Used mobile coupon for the brand	36%	540360		(NET) BRAND MARKETING	60%
28	Participated in the brand's loyalty or subscription program	35%	540350		Received an email from the brand	42%
29	(NET) ONLINE / SOCIAL MEDIA	72%	720720		Received something in the mail from the brand (flyer, leaflet, etc.)	35%
30	Saw it on an online tobacco retailer website	44%	720440		Received a text message from the brand	33%
31	Read about it when doing an online search	39%	720390		(NET) PROMOTIONAL	54%
32	Went to a brand's website	44%	720440		Used mobile coupon for the brand	36%
33	Saw a brand's social media post or went to their social media page	40%	720400		Participated in the brand's loyalty or subscription program	35%
34	Saw an adult tobacco/nicotine consumer post about it on social media	38%	720380		Used paper coupon for the brand	23%
Rank

Cell Formulas
Range	Formula
F3:G34	F3	=SORTBY(B3:C34,D3:D34,-1)
D3:D34	D3	=XLOOKUP("(NET)*",B$3:B3,C$3:C3,,2,-1)*10^6+C3*10^3
Dynamic array formulas.

 

[sksanjeev786]

Could we use a helper column (could be hidden)?

24 11 22.xlsm
	B	C	D	E	F	G
1	Input				Ouput
2
3	(NET) IN/AROUND STORE	87%	870870		(NET) IN/AROUND STORE	87%
4	Featured prominently on the shelf at a store	63%	870630		Saw product inside of a store	66%
5	Saw signs or ads inside or outside of a store	53%	870530		Featured prominently on the shelf at a store	63%
6	Saw product inside of a store	66%	870660		Saw signs or ads inside or outside of a store	53%
7	Heard about it from a store clerk	34%	870340		Visited a brand shop / pop-up store	43%
8	Heard about it from a representative of the brand at a store	33%	870330		Heard about it from a store clerk	34%
9	Visited a brand shop / pop-up store	43%	870430		Heard about it from a representative of the brand at a store	33%
10	(NET) ADVERTISEMENT	66%	660660		(NET) WOM / OVERHEARD	80%
11	Saw an ad on TV or video streaming service	36%	660360		Saw an adult tobacco/nicotine consumer using it	56%
12	Saw an ad in a magazine	40%	660400		Recommended by friends or family	53%
13	Saw an ad online (banner, pop-up)	40%	660400		Heard about it from an adult tobacco/nicotine consumer	45%
14	Saw the brand advertised at an event/concert	31%	660310		Saw in news article or clip	32%
15	Heard an ad when listening to the radio, streaming music, or a podcast	35%	660350		(NET) ONLINE / SOCIAL MEDIA	72%
16	(NET) WOM / OVERHEARD	80%	800800		Saw it on an online tobacco retailer website	44%
17	Recommended by friends or family	53%	800530		Went to a brand's website	44%
18	Heard about it from an adult tobacco/nicotine consumer	45%	800450		Saw a brand's social media post or went to their social media page	40%
19	Saw an adult tobacco/nicotine consumer using it	56%	800560		Read about it when doing an online search	39%
20	Saw in news article or clip	32%	800320		Saw an adult tobacco/nicotine consumer post about it on social media	38%
21	(NET) BRAND MARKETING	60%	600600		(NET) ADVERTISEMENT	66%
22	Received something in the mail from the brand (flyer, leaflet, etc.)	35%	600350		Saw an ad in a magazine	40%
23	Received an email from the brand	42%	600420		Saw an ad online (banner, pop-up)	40%
24	Received a text message from the brand	33%	600330		Saw an ad on TV or video streaming service	36%
25	(NET) PROMOTIONAL	54%	540540		Heard an ad when listening to the radio, streaming music, or a podcast	35%
26	Used paper coupon for the brand	23%	540230		Saw the brand advertised at an event/concert	31%
27	Used mobile coupon for the brand	36%	540360		(NET) BRAND MARKETING	60%
28	Participated in the brand's loyalty or subscription program	35%	540350		Received an email from the brand	42%
29	(NET) ONLINE / SOCIAL MEDIA	72%	720720		Received something in the mail from the brand (flyer, leaflet, etc.)	35%
30	Saw it on an online tobacco retailer website	44%	720440		Received a text message from the brand	33%
31	Read about it when doing an online search	39%	720390		(NET) PROMOTIONAL	54%
32	Went to a brand's website	44%	720440		Used mobile coupon for the brand	36%
33	Saw a brand's social media post or went to their social media page	40%	720400		Participated in the brand's loyalty or subscription program	35%
34	Saw an adult tobacco/nicotine consumer post about it on social media	38%	720380		Used paper coupon for the brand	23%
Rank

Cell Formulas
Range	Formula
F3:G34	F3	=SORTBY(B3:C34,D3:D34,-1)
D3:D34	D3	=XLOOKUP("(NET)*",B$3:B3,C$3:C3,,2,-1)*10^6+C3*10^3
Dynamic array formulas.

Hi Peter,

Thank you so much for your help


Regards
Sanjeev

 

[Cubist]

Without a helper column.
Edited

Book1
	B	C	D	E	F	G
1	Input				Ouput
2
3	(NET) IN/AROUND STORE	0.87			(NET) IN/AROUND STORE	0.87
4	Featured prominently on the shelf at a store	0.63			Saw product inside of a store	0.66
5	Saw signs or ads inside or outside of a store	0.53			Featured prominently on the shelf at a store	0.63
6	Saw product inside of a store	0.66			Saw signs or ads inside or outside of a store	0.53
7	Heard about it from a store clerk	0.34			Visited a brand shop / pop-up store	0.43
8	Heard about it from a representative of the brand at a store	0.33			Heard about it from a store clerk	0.34
9	Visited a brand shop / pop-up store	0.43			Heard about it from a representative of the brand at a store	0.33
10	(NET) ADVERTISEMENT	0.66			(NET) WOM / OVERHEARD	0.8
11	Saw an ad on TV or video streaming service	0.36			Saw an adult tobacco/nicotine consumer using it	0.56
12	Saw an ad in a magazine	0.4			Recommended by friends or family	0.53
13	Saw an ad online (banner, pop-up)	0.4			Heard about it from an adult tobacco/nicotine consumer	0.45
14	Saw the brand advertised at an event/concert	0.31			Saw in news article or clip	0.32
15	Heard an ad when listening to the radio, streaming music, or a podcast	0.35			(NET) ONLINE / SOCIAL MEDIA	0.72
16	(NET) WOM / OVERHEARD	0.8			Saw it on an online tobacco retailer website	0.44
17	Recommended by friends or family	0.53			Went to a brand's website	0.44
18	Heard about it from an adult tobacco/nicotine consumer	0.45			Saw a brand's social media post or went to their social media page	0.4
19	Saw an adult tobacco/nicotine consumer using it	0.56			Read about it when doing an online search	0.39
20	Saw in news article or clip	0.32			Saw an adult tobacco/nicotine consumer post about it on social media	0.38
21	(NET) BRAND MARKETING	0.6			(NET) ADVERTISEMENT	0.66
22	Received something in the mail from the brand (flyer, leaflet, etc.)	0.35			Saw an ad in a magazine	0.4
23	Received an email from the brand	0.42			Saw an ad online (banner, pop-up)	0.4
24	Received a text message from the brand	0.33			Saw an ad on TV or video streaming service	0.36
25	(NET) PROMOTIONAL	0.54			Heard an ad when listening to the radio, streaming music, or a podcast	0.35
26	Used paper coupon for the brand	0.23			Saw the brand advertised at an event/concert	0.31
27	Used mobile coupon for the brand	0.36			(NET) BRAND MARKETING	0.6
28	Participated in the brand's loyalty or subscription program	0.35			Received an email from the brand	0.42
29	(NET) ONLINE / SOCIAL MEDIA	0.72			Received something in the mail from the brand (flyer, leaflet, etc.)	0.35
30	Saw it on an online tobacco retailer website	0.44			Received a text message from the brand	0.33
31	Read about it when doing an online search	0.39			(NET) PROMOTIONAL	0.54
32	Went to a brand's website	0.44			Used mobile coupon for the brand	0.36
33	Saw a brand's social media post or went to their social media page	0.4			Participated in the brand's loyalty or subscription program	0.35
34	Saw an adult tobacco/nicotine consumer post about it on social media	0.38			Used paper coupon for the brand	0.23
Sheet3

Cell Formulas
Range	Formula
F3:G34	F3	=LET( x,B3:C34,y,B3:B34, DROP(REDUCE("",INDEX(SORT(FILTER(x,ISNUMBER(SEARCH("NET",y))),2,-1),0,1),LAMBDA(a,b,VSTACK(a,SORT(FILTER(x,SCAN("",y,LAMBDA(a,b,IF(ISNUMBER(SEARCH("NET",b)),b,a)))=b),2,-1)))),1) )
Dynamic array formulas.

 

[Cubist]

@Peter_SSs 's solution without a helper.

Book1
	B	C	D	E	F	G
1	Input				Ouput
2
3	(NET) IN/AROUND STORE	0.87			(NET) IN/AROUND STORE	0.87
4	Featured prominently on the shelf at a store	0.63			Saw product inside of a store	0.66
5	Saw signs or ads inside or outside of a store	0.53			Featured prominently on the shelf at a store	0.63
6	Saw product inside of a store	0.66			Saw signs or ads inside or outside of a store	0.53
7	Heard about it from a store clerk	0.34			Visited a brand shop / pop-up store	0.43
8	Heard about it from a representative of the brand at a store	0.33			Heard about it from a store clerk	0.34
9	Visited a brand shop / pop-up store	0.43			Heard about it from a representative of the brand at a store	0.33
10	(NET) ADVERTISEMENT	0.66			(NET) WOM / OVERHEARD	0.8
11	Saw an ad on TV or video streaming service	0.36			Saw an adult tobacco/nicotine consumer using it	0.56
12	Saw an ad in a magazine	0.4			Recommended by friends or family	0.53
13	Saw an ad online (banner, pop-up)	0.4			Heard about it from an adult tobacco/nicotine consumer	0.45
14	Saw the brand advertised at an event/concert	0.31			Saw in news article or clip	0.32
15	Heard an ad when listening to the radio, streaming music, or a podcast	0.35			(NET) ONLINE / SOCIAL MEDIA	0.72
16	(NET) WOM / OVERHEARD	0.8			Saw it on an online tobacco retailer website	0.44
17	Recommended by friends or family	0.53			Went to a brand's website	0.44
18	Heard about it from an adult tobacco/nicotine consumer	0.45			Saw a brand's social media post or went to their social media page	0.4
19	Saw an adult tobacco/nicotine consumer using it	0.56			Read about it when doing an online search	0.39
20	Saw in news article or clip	0.32			Saw an adult tobacco/nicotine consumer post about it on social media	0.38
21	(NET) BRAND MARKETING	0.6			(NET) ADVERTISEMENT	0.66
22	Received something in the mail from the brand (flyer, leaflet, etc.)	0.35			Saw an ad in a magazine	0.4
23	Received an email from the brand	0.42			Saw an ad online (banner, pop-up)	0.4
24	Received a text message from the brand	0.33			Saw an ad on TV or video streaming service	0.36
25	(NET) PROMOTIONAL	0.54			Heard an ad when listening to the radio, streaming music, or a podcast	0.35
26	Used paper coupon for the brand	0.23			Saw the brand advertised at an event/concert	0.31
27	Used mobile coupon for the brand	0.36			(NET) BRAND MARKETING	0.6
28	Participated in the brand's loyalty or subscription program	0.35			Received an email from the brand	0.42
29	(NET) ONLINE / SOCIAL MEDIA	0.72			Received something in the mail from the brand (flyer, leaflet, etc.)	0.35
30	Saw it on an online tobacco retailer website	0.44			Received a text message from the brand	0.33
31	Read about it when doing an online search	0.39			(NET) PROMOTIONAL	0.54
32	Went to a brand's website	0.44			Used mobile coupon for the brand	0.36
33	Saw a brand's social media post or went to their social media page	0.4			Participated in the brand's loyalty or subscription program	0.35
34	Saw an adult tobacco/nicotine consumer post about it on social media	0.38			Used paper coupon for the brand	0.23
Sheet4

Cell Formulas
Range	Formula
F3:G34	F3	=SORTBY(B3:C34,MAP(B3:B34,C3:C34,LAMBDA(a,b,XLOOKUP("(NET)*",B3:a,C3:b,,2,-1)))*10^6+C3:C34*10^3,-1)
Dynamic array formulas.