Required Top 3 statement from NET statement and subnet

[sksanjeev786]

Hi Team,

I have a statement with NETs and Subnet statement with Data so I need Top Net data from Column A (solution is i.e D,E) and from NET i need top 3 subnet (solution in GH)

book2
	A	B	C	D	E	F	G	H
1
2	VEHICLE APPEAL (NET)	40%
3	For anyone/everyone	10%
4	For me/meets my needs	1%				NET	VEHICLE APPEAL (NET)	40%
5	Good for family/family oriented/for families	0%		VEHICLE APPEAL (NET)	40%	Subnet	For young people/teenagers	12%
6	For young people/teenagers	12%		LED light/matrix (NET)	22%	Subnet	For anyone/everyone	10%
7	For successful people	0%		VEHICLE APPEARANCE/STYLE (NET)	10%	Subnet	Appeals to different type of people	9%
8	Appeals to different type of people	9%
9	For businesses/business people	0%
10	Suits/matches the owner/reflects owner's image	2%
11	For active/sporty people	0%
12	For brave/courageous people/drivers	3%
13	MODERN/INNOVATIVE (NET)	2%
14	Modern/up to date/innovative	1%
15	Up to date	0%
16	Innovative	1%
17	VEHICLE APPEARANCE/STYLE (NET)	10%
18	Good/like style/stylish	0%
19	New/advanced technology	2%
20	Technology (no detail)	2%
21	Good/high technology	2%
22	Good/great performance	1%
23	SAFETY (NET)	3%
24	Auto brake/self braking system	0%
25	Safe/safety/protection	2%
26	Safety features	0%
27	LED light/matrix (NET)	22%
28	It's complete/with everything you need	2%
29	Visibility in the blind spot/blind spot detector	4%
30	Lights (no detail)	5%
31	Can connect 7 devices	1%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A31,G5:G7	Cell Value	contains "net"	text	NO

can any one check for the logical solution.
Thanks
Sanjeev

 

[sandy666]

with Power Query and Pivot Table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    IF = Table.AddColumn(Source, "NET", each if Text.Contains([Net], "NET") then [Net] else null),
    FillD = Table.FillDown(IF,{"NET"}),
    Filter = Table.SelectRows(FillD, each not Text.Contains([Net], "NET"))
in
    Filter

Net	Percent		NET2	VEHICLE APPEAL (NET)
VEHICLE APPEAL (NET)	40%
For anyone/everyone	10%		Net	Percent
For me/meets my needs	1%		Appeals to different type of people	9%
Good for family/family oriented/for families	0%		For anyone/everyone	10%
For young people/teenagers	12%		For young people/teenagers	12%
For successful people	0%
Appeals to different type of people	9%
For businesses/business people	0%
Suits/matches the owner/reflects owner's image	2%
For active/sporty people	0%
For brave/courageous people/drivers	3%
MODERN/INNOVATIVE (NET)	2%
Modern/up to date/innovative	1%
Up to date	0%
Innovative	1%
VEHICLE APPEARANCE/STYLE (NET)	10%
Good/like style/stylish	0%
New/advanced technology	2%
Technology (no detail)	2%
Good/high technology	2%
Good/great performance	1%
SAFETY (NET)	3%
Auto brake/self braking system	0%
Safe/safety/protection	2%
Safety features	0%
LED light/matrix (NET)	22%
It's complete/with everything you need	2%
Visibility in the blind spot/blind spot detector	4%
Lights (no detail)	5%
Can connect 7 devices	1%

NET2	LED light/matrix (NET)
Net	Percent
It's complete/with everything you need	2%
Lights (no detail)	5%
Visibility in the blind spot/blind spot detector	4%

 

[sksanjeev786]

with Power Query and Pivot Table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    IF = Table.AddColumn(Source, "NET", each if Text.Contains([Net], "NET") then [Net] else null),
    FillD = Table.FillDown(IF,{"NET"}),
    Filter = Table.SelectRows(FillD, each not Text.Contains([Net], "NET"))
in
    Filter

Net	Percent		NET2	VEHICLE APPEAL (NET)
VEHICLE APPEAL (NET)	40%
For anyone/everyone	10%		Net	Percent
For me/meets my needs	1%		Appeals to different type of people	9%
Good for family/family oriented/for families	0%		For anyone/everyone	10%
For young people/teenagers	12%		For young people/teenagers	12%
For successful people	0%
Appeals to different type of people	9%
For businesses/business people	0%
Suits/matches the owner/reflects owner's image	2%
For active/sporty people	0%
For brave/courageous people/drivers	3%
MODERN/INNOVATIVE (NET)	2%
Modern/up to date/innovative	1%
Up to date	0%
Innovative	1%
VEHICLE APPEARANCE/STYLE (NET)	10%
Good/like style/stylish	0%
New/advanced technology	2%
Technology (no detail)	2%
Good/high technology	2%
Good/great performance	1%
SAFETY (NET)	3%
Auto brake/self braking system	0%
Safe/safety/protection	2%
Safety features	0%
LED light/matrix (NET)	22%
It's complete/with everything you need	2%
Visibility in the blind spot/blind spot detector	4%
Lights (no detail)	5%
Can connect 7 devices	1%

NET2	LED light/matrix (NET)
Net	Percent
It's complete/with everything you need	2%
Lights (no detail)	5%
Visibility in the blind spot/blind spot detector	4%

Thanks you sandy for your hard work.

Just wanted to check it is possible to have in Excel formula or something another source

and let me check with our above query and will get back to you on this again.


Thank you once again .
Sanjeev

 

[sandy666]

sure,
but for formula solution you need to wait for someone else
good luck

 

[sandy666]

here is a better version I think,

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "NET", each if Text.Contains([Net], "NET") then [Net] else null),
    FillD = Table.FillDown(IF,{"NET"}),
    Filter = Table.SelectRows(FillD, each not Text.Contains([Net], "NET")),
    Ren = Table.RenameColumns(Filter,{{"Net", "SubNet"}})
in
    Ren

NET	VEHICLE APPEAL (NET)
SubNet	Percent
For young people/teenagers	12%
For anyone/everyone	10%
Appeals to different type of people	9%
NET	LED light/matrix (NET)
SubNet	Percent
Lights (no detail)	5%
Visibility in the blind spot/blind spot detector	4%
It's complete/with everything you need	2%

 

[sksanjeev786]

sure,
but for formula solution you need to wait for someone else
good luck

Sure Sandy

Thanks you for your support.

 

[sksanjeev786]

here is a better version I think,

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "NET", each if Text.Contains([Net], "NET") then [Net] else null),
    FillD = Table.FillDown(IF,{"NET"}),
    Filter = Table.SelectRows(FillD, each not Text.Contains([Net], "NET")),
    Ren = Table.RenameColumns(Filter,{{"Net", "SubNet"}})
in
    Ren

NET	VEHICLE APPEAL (NET)
SubNet	Percent
For young people/teenagers	12%
For anyone/everyone	10%
Appeals to different type of people	9%
NET	LED light/matrix (NET)
SubNet	Percent
Lights (no detail)	5%
Visibility in the blind spot/blind spot detector	4%
It's complete/with everything you need	2%

Sandy,Just wanted to check do we need to add any add on for running power query.


Thanks.
Sanjeev

 

[sandy666]

,Just wanted to check do we need to add any add on for running power query.

you've XL2016 so you've Power Query (Get&Transform) build-in

Power Query add-in is required for XL2010/2013

 

[sksanjeev786]

you've XL2016 so you've Power Query (Get&Transform) build-in

Power Query add-in is required for XL2010/2013

Thanks Sandyy

I will check on this !!!

 

[sandy666]

You are welcome
Power Query is worth to learn

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com