Need data only for first NET only

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
225
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I need data only for 1st NET and statement from Row P 7 to P10 and rest i need blank from 2nd net to end.

out put i in Column T


book3
ABCDEFGHIJKLMNOPQRSTU
12
2
3
4A
5212NEW/REDESIGNED (NET)Output
6TRUE
7SITUATION VISUAL (NET)2%SITUATION VISUAL (NET)2%1111VEHICLE FEATURES/TECHNOLOGY (NET)25%1St NET105NEW/REDESIGNED (NET)12%1NEW/REDESIGNED (NET)12%
8Car/vehicle(no detail)2%AD COPY (NET)0%1422NEW/REDESIGNED (NET)12%106New(model)/All new/Redesigned6%3New(model)/All new/Redesigned6%
9Car driving down road/down desert road/dirt road-OFFER/PRICE/VALUE (NET)0%1533CONTENT FORMAT (NET)9%107Changing/improving6%4Changing/improving6%
10People/man/woman working/loading-VEHICLE FEATURES/TECHNOLOGY (NET)25%14108Evolution (in time)0%12Evolution (in time)0%
11Shows football (soccer) game/field/player-VEHICLE APPEAL (NET)5%652ND NET109CONVENIENCE (NET)0%13
12AD COPY (NET)-MODERN/INNOVATIVE (NET)6%56110Easy to drive0%14
13Reference to enjoy/appreciate your life-VEHICLE APPEARANCE/STYLE (NET)5%77111BRAND HERITAGE (NET)4%5
14Reference to 'be yourself/whatever you want to be/be unique'-NEW/REDESIGNED (NET)12%283rd NET112Best/better than others0%15
15Reference to 'Togetherness/being together'-CONVENIENCE (NET)0%169113Classy/sophisticated/elegant0%16
16To find new roads (similar)-BRAND HERITAGE (NET)4%1010114Cool0%17
17OFFER/PRICE/VALUE (NET)-CONTENT FORMAT (NET)9%311115Different/unique2%7
18Good/affordable price-MISCELLANEOUS (NET)5%812116Responsible0%18
19Good value/value for money-SAFETY (NET)8%413117Variety/choices/options0%19
20Expensive/high price-QUALITY (NET)0%1714118Competitive0%20
21It is easy to buy-STRENGTH (NET)1%1315119Successful brand/company0%21
22Economy/economical-STORAGE/CAPACITY (NET)2%1216120Established/been around long time0%22
23Buy the car/vehicle/ad says buy it-USAGE/EFFICACY (NET)5%917121It’s a Dream car2%8
24Low payment/monthly payment plan-1718122Good/like brand0%23
250% interest-1719123Best in class/top in class0%24
26Saves you money/saving money-124Always there for you0%25
27More/extra features included in base price/at no additional cost-125Helpful/supportive0%26
28Good financing/payment plans-DK/none25%126Contributes to society/social responsibility0%27
29Offers financing/payment plans (no detail)-127TYPE OF CAR (NET)0%28
30VEHICLE FEATURES/TECHNOLOGY (NET)25%128SUV0%29
31Good features-129Truck/pick-up truck0%30
32Variety/different features-130CONTENT FORMAT (NET)9%2
33Has new functions-131Funny/amusing ad1%11
34Light/lighter-132Good/like it(no detail)0%31
35New/advanced technology3%133Interesting ad0%32
36Technology (no detail)6%134Ad made me want to buy/drive it0%33
37Good/high technology-135Creative/unique/original ad4%6
38Good/great performance-136Exciting ad0%34
39Comfortable to drive1%137Positive animation0%35
40Car has good traction/grip on the road-138Motivating / inspiring0%36
41Good suspension1%139Confusing/difficult to understand/didn't understand point of ad2%9
42Has turbo/fast turbo2%140Poor/dislike ad(no detail)2%10
43Has wifi/built in wifi9%141Same as/similar to other ads0%37
44Good connectivity/communication-142Not relevant0%38
45OnStar/accident alert system3%143
46It's complete/with everything you need2%144
47Anti-collision system/collision avoidance sensors-145
48Assistant/personal/online2%
49VEHICLE APPEAL (NET)5%
50For anyone/everyone-
51For me/meets my needs-
52For men/masculine-
53Good for family/family oriented/for families-
54For young people/teenagers-
55For future/next generation-
56For successful people-
57For adults/grown-ups-
58For farmers/people who work in farm-
59For both men and women-
60Not for everyone/exclusive/for a select few-
61For independent/self-sufficient people-
62For brave/courageous people/drivers-
63For heros/superheros-
64For outdoorsy people/who like the outdoors-
65Flexible car-
66Suits/fits with any lifestyle-
67Makes you feel free3%
68Will make you happy2%
69Makes you want to share/with friends-
70Makes you feel satisfied-
71Changes you/your life1%
72Brings you respect-
73Gives you freedom to choose-
74Ready to take it on/take on/everything-
75MODERN/INNOVATIVE (NET)6%
76Modern/up to date/innovative6%
77Futuristic/something from future-
78Up to date-
79Innovative-
80VEHICLE APPEARANCE/STYLE (NET)5%
81Good/like style/stylish-
82Eyecatching car-
83Good/like shape/design-
84Good/like colors2%
85Good/like Interior-
86Special/unique look/shape/design-
87Good/like product/vehicle(no detail)-
88Fun/fun to drive2%
89Luxury/luxurious-
90Relaxing-
91Enjoyable car/vehicle-
92Friendly car/truck-
93Youthful/youth-
94Active/dynamic-
95Sporty/fast1%
96Poor/dislike car/product(no detail)-
97Same as/similar to other cars-
98Simple/basic-
99Has a lot of personality/strong character/boldness-
100Practical-
101Warm/has warmth-
102Passion-
103Determined/ambitious/unstoppable-
104Reward for effort/hard work-
105NEW/REDESIGNED (NET)12%
106New(model)/All new/Redesigned6%
107Changing/improving6%
108Evolution (in time)-
109CONVENIENCE (NET)-
110Easy to drive-
111BRAND HERITAGE (NET)4%
112Best/better than others-
113Classy/sophisticated/elegant-
114Cool-
115Different/unique2%
116Responsible-
117Variety/choices/options-
118Competitive-
119Successful brand/company-
120Established/been around long time-
121It’s a Dream car2%
122Good/like brand-
123Best in class/top in class-
124Always there for you-
125Helpful/supportive-
126Contributes to society/social responsibility-
127TYPE OF CAR (NET)-
128SUV-
Sheet1
Cell Formulas
RangeFormula
Q1Q1=E5
P5P5=K8
P6P6=P5=P7
K7:L9K7=INDEX($D$7:$G$25,MATCH($J7,$G$7:$G$25,0),K$5)
P7:P44P7=INDIRECT($O$4&O7)
Q7:Q44Q7=VALUE(IF(INDEX($A:$B,MATCH($P7,$A:$A,0),$Q$1)="-","0%",INDEX($A:$B,MATCH($P7,$A:$A,0),$Q$1)))
R7:R44R7=RANK($Q7,$Q$7:$Q$44,0)-1+COUNTIF($Q$7:Q7,Q7)
E7:E23,E28E7=VALUE(IF(INDEX($A:$B,MATCH($D7,$A:$A,0),E$5)="-","0%",INDEX($A:$B,MATCH($D7,$A:$A,0),E$5)))
G7:G25G7=RANK($E7,$E$7:$E$25,0)-1+COUNTIF($E$7:E7,E7)
O7O7=MATCH(P$5,$A:$A,0)
O8:O47O8=O7+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E28Cell Valuetop 3 valuestextNO
D28Cell ValueduplicatestextNO
D26Cell ValueduplicatestextNO
T7:T44Cell Valuecontains "NET"textNO
P7:P47Cell Valuecontains "NET"textNO
A:ACell ValueduplicatestextNO
E7:E25Cell Valuetop 3 valuestextNO
Cells with Data Validation
CellAllowCriteria
E5List=#REF!




Thank in advance
Regards,
Sanjeev
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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
Top