Ranking top 5 with out taking NET and SN

[sksanjeev786]

Hi Team,

I Need data without NET and (SN) top 5 statement

book1
	A	B	C	D	E
1	SITUATION VISUAL (NET)	50%
2	People are more interested in the car than the driver or anyone/anything else	40%
3	People notice/are captivated by the car	1%		People are more interested in the car than the driver or anyone/anything else	40%
4	AD COPY (NET)	70%		Reference to 'be yourself/whatever you want to be/be unique'	39%
5	References to 'Goat'	-		To find new roads (similar)	38%
6	Reference to enjoy/appreciate your life(SN)	33%		Reference to 'Dream'	35%
7	Reference to 'be yourself/whatever you want to be/be unique'	39%
8	Reference to 'You can achieve anything'	-
9	To find new roads (similar)	38%
10	Reference to 'Dream'	35%
11	OFFER/PRICE/VALUE (NET)	35%
12	Good/affordable price	-
13	Offered a good/great deal/rebate/promotionife(SN)	32%
14	It is easy to buy	33%
15	Easy to get/no hassle good pricing/don't need a sale	-
16	Economy/economicalife(SN)	31%
17	Buy the car/vehicle/ad says buy it	0%
18	20 day sale	-
19	Offers discount(no detail)ife(SN)	29%
20	Offers 20 day sale	-
21	VEHICLE FEATURES/TECHNOLOGY (NET)	30%
22	Good features	0%
23	Variety/different features	1%
24	Has new functions	-
Sheet1

Thanks

 

[jasonb75]

For excel 2016,

Codename.xlsm
	A	B	C	D	E
1	SITUATION VISUAL (NET)	50%
2	People are more interested in the car than the driver or anyone/anything else	40%
3	People notice/are captivated by the car	1%		People are more interested in the car than the driver or anyone/anything else	40%
4	AD COPY (NET)	70%		Reference to 'be yourself/whatever you want to be/be unique'	39%
5	References to 'Goat'	-		To find new roads (similar)	38%
6	Reference to enjoy/appreciate your life(SN)	33%		Reference to 'Dream'	35%
7	Reference to 'be yourself/whatever you want to be/be unique'	39%
8	Reference to 'You can achieve anything'	-
9	To find new roads (similar)	38%
10	Reference to 'Dream'	35%
11	OFFER/PRICE/VALUE (NET)	35%
12	Good/affordable price	-
13	Offered a good/great deal/rebate/promotionife(SN)	32%
14	It is easy to buy	33%
15	Easy to get/no hassle good pricing/don't need a sale	-
16	Economy/economicalife(SN)	31%
17	Buy the car/vehicle/ad says buy it	0%
18	20 day sale	-
19	Offers discount(no detail)ife(SN)	29%
20	Offers 20 day sale	-
21	VEHICLE FEATURES/TECHNOLOGY (NET)	30%
22	Good features	0%
23	Variety/different features	1%
24	Has new functions	-
Sheet3

Cell Formulas
Range	Formula
D3:D6	D3	=INDEX($A$1:$A$24,AGGREGATE(15,6,ROW($A$1:$A$24)/($B$1:$B$24=E3),COUNTIF(E$3:E3,E3)))
E3:E6	E3	=AGGREGATE(14,6,$B$1:$B$24/ISERROR(SEARCH("(NET)",$A$1:$A$24))/ISERROR(SEARCH("(SN)",$A$1:$A$24)),ROWS(E$3:E3))

It might also be possible with SORT and FILTER using 365, as your profile shows 2 versions of excel I have based this on the oldest one.

 

[KevCarter]

For 365 only. Using a helper cell in G2 for LARGE(5)

Book1
	A	B	C	D	E	F	G
1	SITUATION VISUAL (NET)	0.5		People are more interested in the car than the driver or anyone/anything else	0.4		LARGE(5)
2	People are more interested in the car than the driver or anyone/anything else	0.4		Reference to 'be yourself/whatever you want to be/be unique'	0.39		0.33
3	People notice/are captivated by the car	0.0		To find new roads (similar)	0.38
4	AD COPY (NET)	0.7		Reference to 'Dream'	0.35
5	References to 'Goat'	0.0		It is easy to buy	0.33
6	Reference to enjoy/appreciate your life(SN)	0.3
7	Reference to 'be yourself/whatever you want to be/be unique'	0.4
8	Reference to 'You can achieve anything'	0.0
9	To find new roads (similar)	0.4
10	Reference to 'Dream'	0.4
11	OFFER/PRICE/VALUE (NET)	0.4
12	Good/affordable price	0.0
13	Offered a good/great deal/rebate/promotionife(SN)	0.3
14	It is easy to buy	0.3
15	Easy to get/no hassle good pricing/don't need a sale	0.0
16	Economy/economicalife(SN)	0.3
17	Buy the car/vehicle/ad says buy it	0.0
18	20 day sale	0.0
19	Offers discount(no detail)ife(SN)	0.3
20	Offers 20 day sale	0.0
21	VEHICLE FEATURES/TECHNOLOGY (NET)	0.3
22	Good features	0.0
23	Variety/different features	0.0
24	Has new functions	0.0
Sheet1

Cell Formulas
Range	Formula
D1:E5	D1	=SORT(FILTER(A1:B24,(NOT(ISNUMBER(SEARCH("NET",A1:A24))))*(NOT(ISNUMBER(SEARCH("SN",A1:A24))))*(B1:B24>=$G$2)),2,-1)
G2	G2	=SUMPRODUCT(LARGE((NOT(ISNUMBER(SEARCH("NET",A1:A24)))*(NOT(ISNUMBER(SEARCH("SN",A1:A24)))))*(B1:B24),5))
Dynamic array formulas.

 

[Fluff]

Another option for 365

+Fluff v2.xlsm
	A	B	C	D	E
1	SITUATION VISUAL (NET)	50%
2	People are more interested in the car than the driver or anyone/anything else	40%
3	People notice/are captivated by the car	1%		People are more interested in the car than the driver or anyone/anything else	40%
4	AD COPY (NET)	70%		Reference to 'be yourself/whatever you want to be/be unique'	39%
5	References to 'Goat'	-		To find new roads (similar)	38%
6	Reference to enjoy/appreciate your life(SN)	33%		Reference to 'Dream'	35%
7	Reference to 'be yourself/whatever you want to be/be unique'	39%		It is easy to buy	33%
8	Reference to 'You can achieve anything'	-
9	To find new roads (similar)	38%
10	Reference to 'Dream'	35%
11	OFFER/PRICE/VALUE (NET)	35%
12	Good/affordable price	-
13	Offered a good/great deal/rebate/promotionife(SN)	32%
14	It is easy to buy	33%
15	Easy to get/no hassle good pricing/don't need a sale	-
16	Economy/economicalife(SN)	31%
17	Buy the car/vehicle/ad says buy it	0%
18	20 day sale	-
19	Offers discount(no detail)ife(SN)	29%
20	Offers 20 day sale	-
21	VEHICLE FEATURES/TECHNOLOGY (NET)	30%
22	Good features	0%
23	Variety/different features	1%
24	Has new functions	-
25
26
Data

Cell Formulas
Range	Formula
D3:E7	D3	=INDEX(SORT(FILTER(A1:B24,(ISERR(SEARCH("(net)",A1:A24)))*(ISERR(SEARCH("(SN)",A1:A24)))*(ISNUMBER(B1:B24))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.

 

[KevCarter]

Another option for 365

+Fluff v2.xlsm
	A	B	C	D	E
1	SITUATION VISUAL (NET)	50%
2	People are more interested in the car than the driver or anyone/anything else	40%
3	People notice/are captivated by the car	1%		People are more interested in the car than the driver or anyone/anything else	40%
4	AD COPY (NET)	70%		Reference to 'be yourself/whatever you want to be/be unique'	39%
5	References to 'Goat'	-		To find new roads (similar)	38%
6	Reference to enjoy/appreciate your life(SN)	33%		Reference to 'Dream'	35%
7	Reference to 'be yourself/whatever you want to be/be unique'	39%		It is easy to buy	33%
8	Reference to 'You can achieve anything'	-
9	To find new roads (similar)	38%
10	Reference to 'Dream'	35%
11	OFFER/PRICE/VALUE (NET)	35%
12	Good/affordable price	-
13	Offered a good/great deal/rebate/promotionife(SN)	32%
14	It is easy to buy	33%
15	Easy to get/no hassle good pricing/don't need a sale	-
16	Economy/economicalife(SN)	31%
17	Buy the car/vehicle/ad says buy it	0%
18	20 day sale	-
19	Offers discount(no detail)ife(SN)	29%
20	Offers 20 day sale	-
21	VEHICLE FEATURES/TECHNOLOGY (NET)	30%
22	Good features	0%
23	Variety/different features	1%
24	Has new functions	-
25
26
Data

Cell Formulas
Range	Formula
D3:E7	D3	=INDEX(SORT(FILTER(A1:B24,(ISERR(SEARCH("(net)",A1:A24)))*(ISERR(SEARCH("(SN)",A1:A24)))*(ISNUMBER(B1:B24))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.

Elegant solution Fluff. Love it!

 

[maabadi]

Other Version except 365.
You can use helper Column at Column C and Drag it down then Hide it.
C1 =

=IF(IF(OR(ISNUMBER(SEARCH("NET",$A$1:$A$24)),ISNUMBER(SEARCH("SN",$A$1:$A$24)))=FALSE,COUNTA($A$1:A1),0)>0,B1,"-")

Then at D3 write:

=IFERROR(INDEX($A$1:$B$24,MATCH(LARGE($C$1:$C$24,ROWS($D$3:D3)),$C$1:$C$24,0),1),"")

and E3:

=IFERROR(INDEX($A$1:$B$24,MATCH(LARGE($C$1:$C$24,ROWS($D$3:D3)),$C$1:$C$24,0),2),"")

 

[sksanjeev786]

Thank you Team for your help and hard work on this


Regards,
Sanjeev

 

[sksanjeev786]

Another option for 365

+Fluff v2.xlsm
	A	B	C	D	E
1	SITUATION VISUAL (NET)	50%
2	People are more interested in the car than the driver or anyone/anything else	40%
3	People notice/are captivated by the car	1%		People are more interested in the car than the driver or anyone/anything else	40%
4	AD COPY (NET)	70%		Reference to 'be yourself/whatever you want to be/be unique'	39%
5	References to 'Goat'	-		To find new roads (similar)	38%
6	Reference to enjoy/appreciate your life(SN)	33%		Reference to 'Dream'	35%
7	Reference to 'be yourself/whatever you want to be/be unique'	39%		It is easy to buy	33%
8	Reference to 'You can achieve anything'	-
9	To find new roads (similar)	38%
10	Reference to 'Dream'	35%
11	OFFER/PRICE/VALUE (NET)	35%
12	Good/affordable price	-
13	Offered a good/great deal/rebate/promotionife(SN)	32%
14	It is easy to buy	33%
15	Easy to get/no hassle good pricing/don't need a sale	-
16	Economy/economicalife(SN)	31%
17	Buy the car/vehicle/ad says buy it	0%
18	20 day sale	-
19	Offers discount(no detail)ife(SN)	29%
20	Offers 20 day sale	-
21	VEHICLE FEATURES/TECHNOLOGY (NET)	30%
22	Good features	0%
23	Variety/different features	1%
24	Has new functions	-
25
26
Data

Cell Formulas
Range	Formula
D3:E7	D3	=INDEX(SORT(FILTER(A1:B24,(ISERR(SEARCH("(net)",A1:A24)))*(ISERR(SEARCH("(SN)",A1:A24)))*(ISNUMBER(B1:B24))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.

Hey Fluff,

Thank you once again.

I have shared your formula and team loved it!!! as mention by @Kavecarter its really beautiful

Just wanted to check if I want to understand your formal concept can you please where is find.

Thanks in advance.
Regards,
Sanjeev

 

[Fluff]

Glad we could help & thanks for the feedback.

Just wanted to check if I want to understand your formal concept can you please where is find.

Not quite sure what you mean by this.

 

[sksanjeev786]

Glad we could help & thanks for the feedback.


Not quite sure what you mean by this.

Hey Fluff

I meant to say your function clarification in formula just like (Formula Evaluation ).

If it possible otherwise no issue

Thanks.
Sanjeev