Need help with Data in Subgroup

[sksanjeev786]

Hi Team,

I need data order as per column M to O and my input data is from A1 to K25 (with cell color) and I need to keep the data as per subgroup A1to A8 same subgroup we have in E2:K2
and I need to keep the statement as pe color on cell .Sample output is on M to O

book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Input			Input									Output
2	Superior Brand	36%		Type over text to name your factors:	Superior Brand	Sustainability Leader	Premium Brand & Advanced Tech	Reduces Carbon Footprint	Professional Service	Recommended	Forward-Looking		Superior Brand	36%	Appeals to you more than other brands
3	Sustainability Leader	15%													Are better value
4	Professional Service	12%		Appeals to you more than other brands	0.65	0.29	0.23	0.19	0.13	0.22	0.19				Have the right products for my vehicle
5	Reduces Carbon Footprint	12%		Are better value	0.66	0.40	0.21	0.31	0.08	0.03	0.02				Protects my investment in my vehicle more than other oils
6	Recommended	10%		Have the right products for my vehicle	0.63	0.16	0.16	0.28	0.32	0.27	0.16				Helps extend engine life
7	Premium Brand & Advanced Tech	8%		Protects my investment in my vehicle more than other oils	0.66	0.25	0.21	0.12	0.24	0.19	0.28				Gives me extra assurance that my vehicle is protected
8	Forward-Looking	7%		Helps extend engine life	0.57	0.17	0.28	0.22	0.29	0.20	0.32				Cleans my engine better than other oils
9				Gives me extra assurance that my vehicle is protected	0.61	0.27	0.25	0.23	0.24	0.16	0.29				Is a brand I trust
10				Cleans my engine better than other oils	0.66	0.18	0.19	0.24	0.19	0.28	0.21				Maintains your engine's performance
11				Is a brand I trust	0.67	0.28	0.20	0.19	0.18	0.20	0.23				Makes my everyday life easier
12				Maintains your engine's performance	0.68	0.20	0.22	0.14	0.29	0.22	0.23				Provides a superior customer experience compared to other brands
13				Makes my everyday life easier	0.68	0.32	0.24	0.21	0.19	0.17	0.09				Is a superior quality of motor oil compared to other brands
14				Provides a superior customer experience compared to other brands	0.51	0.43	0.27	0.10	0.17	0.39	0.08
15				Acts more responsibly to look after the environment	0.35	0.59	0.15	0.39	0.11	0.15	0.26		Sustainability Leader	15%	Acts more responsibly to look after the environment
16				Sets the industry standard for sustainability	0.39	0.59	0.11	0.15	0.27	0.25	0.24				Sets the industry standard for sustainability
17				Leads in the development of products that are better for the environment	0.27	0.70	0.28	0.14	0.25	0.18	0.17				Leads in the development of products that are better for the environment
18				Have products that are more technologically advanced	0.23	0.21	0.65	0.42	0.25	0.07	0.29
19				Is a premium brand	0.43	0.23	0.71	0.02	0.18	0.23	0.08		Professional Service	12%	Provides a professional workshop service
20				Helps you to reduce your carbon footprint	0.42	0.24	0.16	0.69	0.15	0.24	0.14				Have a product range that is easier to understand
21				Provides a professional workshop service	0.34	0.27	0.21	0.13	0.75	0.17	0.17
22				Are recommended by vehicle manufacturers	0.34	0.25	0.17	0.21	0.22	0.75	0.17		Reduces Carbon Footprint	12%	Helps you to reduce your carbon footprint
23				Is a forward-looking brand	0.36	0.28	0.20	0.13	0.16	0.18	0.76
24				Have a product range that is easier to understand	0.39	0.34	0.19	0.43	0.46	0.18	0.06		Recommended	10%	Are recommended by vehicle manufacturers
25				Is a superior quality of motor oil compared to other brands	0.45	0.24	0.43	0.22	0.01	0.43	0.27
26													Premium Brand & Advanced Tech	8%	Have products that are more technologically advanced
27															Is a premium brand
28
29													Forward-Looking	7%	Is a forward-looking brand
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D24:D25	Expression	=D24<>0	text	NO
D4:D23	Expression	=D4<>0	text	NO
E2:K3	Expression	=E2=""	text	NO
E2:K3	Expression	=E2<>0	text	NO
O29	Expression	=O29<>0	text	NO
O26:O27	Expression	=O26<>0	text	NO
O24	Expression	=O24<>0	text	NO
O22	Expression	=O22<>0	text	NO
O20	Expression	=O20<>0	text	NO
O19	Expression	=O19<>0	text	NO
O15:O17	Expression	=O15<>0	text	NO
O13	Expression	=O13<>0	text	NO
O2:O12	Expression	=O2<>0	text	NO

 

[SanjayGMusafir]

Hi Team,

I need data order as per column M to O and my input data is from A1 to K25 (with cell color) and I need to keep the data as per subgroup A1to A8 same subgroup we have in E2:K2
and I need to keep the statement as pe color on cell .Sample output is on M to O

book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Input			Input									Output
2	Superior Brand	36%		Type over text to name your factors:	Superior Brand	Sustainability Leader	Premium Brand & Advanced Tech	Reduces Carbon Footprint	Professional Service	Recommended	Forward-Looking		Superior Brand	36%	Appeals to you more than other brands
3	Sustainability Leader	15%													Are better value
4	Professional Service	12%		Appeals to you more than other brands	0.65	0.29	0.23	0.19	0.13	0.22	0.19				Have the right products for my vehicle
5	Reduces Carbon Footprint	12%		Are better value	0.66	0.40	0.21	0.31	0.08	0.03	0.02				Protects my investment in my vehicle more than other oils
6	Recommended	10%		Have the right products for my vehicle	0.63	0.16	0.16	0.28	0.32	0.27	0.16				Helps extend engine life
7	Premium Brand & Advanced Tech	8%		Protects my investment in my vehicle more than other oils	0.66	0.25	0.21	0.12	0.24	0.19	0.28				Gives me extra assurance that my vehicle is protected
8	Forward-Looking	7%		Helps extend engine life	0.57	0.17	0.28	0.22	0.29	0.20	0.32				Cleans my engine better than other oils
9				Gives me extra assurance that my vehicle is protected	0.61	0.27	0.25	0.23	0.24	0.16	0.29				Is a brand I trust
10				Cleans my engine better than other oils	0.66	0.18	0.19	0.24	0.19	0.28	0.21				Maintains your engine's performance
11				Is a brand I trust	0.67	0.28	0.20	0.19	0.18	0.20	0.23				Makes my everyday life easier
12				Maintains your engine's performance	0.68	0.20	0.22	0.14	0.29	0.22	0.23				Provides a superior customer experience compared to other brands
13				Makes my everyday life easier	0.68	0.32	0.24	0.21	0.19	0.17	0.09				Is a superior quality of motor oil compared to other brands
14				Provides a superior customer experience compared to other brands	0.51	0.43	0.27	0.10	0.17	0.39	0.08
15				Acts more responsibly to look after the environment	0.35	0.59	0.15	0.39	0.11	0.15	0.26		Sustainability Leader	15%	Acts more responsibly to look after the environment
16				Sets the industry standard for sustainability	0.39	0.59	0.11	0.15	0.27	0.25	0.24				Sets the industry standard for sustainability
17				Leads in the development of products that are better for the environment	0.27	0.70	0.28	0.14	0.25	0.18	0.17				Leads in the development of products that are better for the environment
18				Have products that are more technologically advanced	0.23	0.21	0.65	0.42	0.25	0.07	0.29
19				Is a premium brand	0.43	0.23	0.71	0.02	0.18	0.23	0.08		Professional Service	12%	Provides a professional workshop service
20				Helps you to reduce your carbon footprint	0.42	0.24	0.16	0.69	0.15	0.24	0.14				Have a product range that is easier to understand
21				Provides a professional workshop service	0.34	0.27	0.21	0.13	0.75	0.17	0.17
22				Are recommended by vehicle manufacturers	0.34	0.25	0.17	0.21	0.22	0.75	0.17		Reduces Carbon Footprint	12%	Helps you to reduce your carbon footprint
23				Is a forward-looking brand	0.36	0.28	0.20	0.13	0.16	0.18	0.76
24				Have a product range that is easier to understand	0.39	0.34	0.19	0.43	0.46	0.18	0.06		Recommended	10%	Are recommended by vehicle manufacturers
25				Is a superior quality of motor oil compared to other brands	0.45	0.24	0.43	0.22	0.01	0.43	0.27
26													Premium Brand & Advanced Tech	8%	Have products that are more technologically advanced
27															Is a premium brand
28
29													Forward-Looking	7%	Is a forward-looking brand
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D24:D25	Expression	=D24<>0	text	NO
D4:D23	Expression	=D4<>0	text	NO
E2:K3	Expression	=E2=""	text	NO
E2:K3	Expression	=E2<>0	text	NO
O29	Expression	=O29<>0	text	NO
O26:O27	Expression	=O26<>0	text	NO
O24	Expression	=O24<>0	text	NO
O22	Expression	=O22<>0	text	NO
O20	Expression	=O20<>0	text	NO
O19	Expression	=O19<>0	text	NO
O15:O17	Expression	=O15<>0	text	NO
O13	Expression	=O13<>0	text	NO
O2:O12	Expression	=O2<>0	text	NO

See Cell S2 & V2 in file shared underneath as your solution

Check if that helps you in some way

I don't know how you derived percentages in column N:N else would have helped you with that too.

Must note that you can work with formulas I used in Excel 365 only and not on Excel 2016.

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Input			Input									Output					Try
2	Superior Brand	0.35651545		Type over text to name your factors:	Superior Brand	Sustainability Leader	Premium Brand & Advanced Tech	Reduces Carbon Footprint	Professional Service	Recommended	Forward-Looking		Superior Brand	0.35651545	Appeals to you more than other brands			Superior Brand	Appeals to you more than other brands		Sustainability Leader	Acts more responsibly to look after the environment
3	Sustainability Leader	0.14915691													Are better value				Are better value			Sets the industry standard for sustainability
4	Professional Service	0.12402673		Appeals to you more than other brands	0.653	0.285	0.229	0.192	0.125	0.223	0.188				Have the right products for my vehicle				Have the right products for my vehicle			Leads in the development of products that are better for the environment
5	Reduces Carbon Footprint	0.12087484		Are better value	0.661	0.4	0.211	0.31	0.082	0.028	0.022				Protects my investment in my vehicle more than other oils				Protects my investment in my vehicle more than other oils
6	Recommended	0.10373634		Have the right products for my vehicle	0.631	0.164	0.157	0.284	0.319	0.273	0.157				Helps extend engine life				Helps extend engine life
7	Premium Brand & Advanced Tech	0.07565221		Protects my investment in my vehicle more than other oils	0.657	0.253	0.212	0.115	0.236	0.194	0.282				Gives me extra assurance that my vehicle is protected				Gives me extra assurance that my vehicle is protected
8	Forward-Looking	0.07003751		Helps extend engine life	0.566	0.169	0.278	0.215	0.286	0.196	0.319				Cleans my engine better than other oils				Cleans my engine better than other oils
9				Gives me extra assurance that my vehicle is protected	0.608	0.266	0.252	0.228	0.244	0.156	0.292				Is a brand I trust				Is a brand I trust
10				Cleans my engine better than other oils	0.655	0.177	0.187	0.24	0.188	0.284	0.213				Maintains your engine's performance				Maintains your engine's performance
11				Is a brand I trust	0.666	0.276	0.196	0.189	0.183	0.204	0.234				Makes my everyday life easier				Makes my everyday life easier
12				Maintains your engine's performance	0.677	0.202	0.219	0.142	0.285	0.215	0.234				Provides a superior customer experience compared to other brands				Provides a superior customer experience compared to other brands
13				Makes my everyday life easier	0.683	0.321	0.235	0.208	0.189	0.172	0.089				Is a superior quality of motor oil compared to other brands				Is a superior quality of motor oil compared to other brands
14				Provides a superior customer experience compared to other brands	0.511	0.426	0.268	0.097	0.172	0.391	0.076
15				Acts more responsibly to look after the environment	0.354	0.587	0.149	0.392	0.112	0.149	0.261		Sustainability Leader	0.14915691	Acts more responsibly to look after the environment
16				Sets the industry standard for sustainability	0.393	0.593	0.107	0.147	0.267	0.245	0.24				Sets the industry standard for sustainability
17				Leads in the development of products that are better for the environment	0.269	0.703	0.278	0.142	0.248	0.18	0.174				Leads in the development of products that are better for the environment
18				Have products that are more technologically advanced	0.232	0.208	0.651	0.419	0.25	0.07	0.292
19				Is a premium brand	0.434	0.232	0.705	0.021	0.182	0.231	0.084		Professional Service	0.12402673	Provides a professional workshop service
20				Helps you to reduce your carbon footprint	0.415	0.242	0.158	0.691	0.148	0.24	0.142				Have a product range that is easier to understand
21				Provides a professional workshop service	0.341	0.272	0.213	0.126	0.749	0.166	0.17
22				Are recommended by vehicle manufacturers	0.338	0.249	0.174	0.207	0.224	0.748	0.169		Reduces Carbon Footprint	0.12087484	Helps you to reduce your carbon footprint
23				Is a forward-looking brand	0.357	0.282	0.202	0.133	0.158	0.176	0.755
24				Have a product range that is easier to understand	0.387	0.341	0.188	0.433	0.462	0.183	0.057		Recommended	0.10373634	Are recommended by vehicle manufacturers
25				Is a superior quality of motor oil compared to other brands	0.45	0.236	0.426	0.217	0.014	0.426	0.265
26													Premium Brand & Advanced Tech	0.07565221	Have products that are more technologically advanced
27															Is a premium brand
28
29													Forward-Looking	0.07003751	Is a forward-looking brand
Sheet1

Cell Formulas
Range	Formula
R2	R2	=E2
S2:S13	S2	=FILTER(D4:D25,E4:E25>=0.45)
U2	U2	=F2
V2:V4	V2	=FILTER(D4:D25,F4:F25>=0.45)
Dynamic array formulas.

 

[sksanjeev786]

See Cell S2 & V2 in file shared underneath as your solution

Check if that helps you in some way

I don't know how you derived percentages in column N:N else would have helped you with that too.

Must note that you can work with formulas I used in Excel 365 only and not on Excel 2016.

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Input			Input									Output					Try
2	Superior Brand	0.35651545		Type over text to name your factors:	Superior Brand	Sustainability Leader	Premium Brand & Advanced Tech	Reduces Carbon Footprint	Professional Service	Recommended	Forward-Looking		Superior Brand	0.35651545	Appeals to you more than other brands			Superior Brand	Appeals to you more than other brands		Sustainability Leader	Acts more responsibly to look after the environment
3	Sustainability Leader	0.14915691													Are better value				Are better value			Sets the industry standard for sustainability
4	Professional Service	0.12402673		Appeals to you more than other brands	0.653	0.285	0.229	0.192	0.125	0.223	0.188				Have the right products for my vehicle				Have the right products for my vehicle			Leads in the development of products that are better for the environment
5	Reduces Carbon Footprint	0.12087484		Are better value	0.661	0.4	0.211	0.31	0.082	0.028	0.022				Protects my investment in my vehicle more than other oils				Protects my investment in my vehicle more than other oils
6	Recommended	0.10373634		Have the right products for my vehicle	0.631	0.164	0.157	0.284	0.319	0.273	0.157				Helps extend engine life				Helps extend engine life
7	Premium Brand & Advanced Tech	0.07565221		Protects my investment in my vehicle more than other oils	0.657	0.253	0.212	0.115	0.236	0.194	0.282				Gives me extra assurance that my vehicle is protected				Gives me extra assurance that my vehicle is protected
8	Forward-Looking	0.07003751		Helps extend engine life	0.566	0.169	0.278	0.215	0.286	0.196	0.319				Cleans my engine better than other oils				Cleans my engine better than other oils
9				Gives me extra assurance that my vehicle is protected	0.608	0.266	0.252	0.228	0.244	0.156	0.292				Is a brand I trust				Is a brand I trust
10				Cleans my engine better than other oils	0.655	0.177	0.187	0.24	0.188	0.284	0.213				Maintains your engine's performance				Maintains your engine's performance
11				Is a brand I trust	0.666	0.276	0.196	0.189	0.183	0.204	0.234				Makes my everyday life easier				Makes my everyday life easier
12				Maintains your engine's performance	0.677	0.202	0.219	0.142	0.285	0.215	0.234				Provides a superior customer experience compared to other brands				Provides a superior customer experience compared to other brands
13				Makes my everyday life easier	0.683	0.321	0.235	0.208	0.189	0.172	0.089				Is a superior quality of motor oil compared to other brands				Is a superior quality of motor oil compared to other brands
14				Provides a superior customer experience compared to other brands	0.511	0.426	0.268	0.097	0.172	0.391	0.076
15				Acts more responsibly to look after the environment	0.354	0.587	0.149	0.392	0.112	0.149	0.261		Sustainability Leader	0.14915691	Acts more responsibly to look after the environment
16				Sets the industry standard for sustainability	0.393	0.593	0.107	0.147	0.267	0.245	0.24				Sets the industry standard for sustainability
17				Leads in the development of products that are better for the environment	0.269	0.703	0.278	0.142	0.248	0.18	0.174				Leads in the development of products that are better for the environment
18				Have products that are more technologically advanced	0.232	0.208	0.651	0.419	0.25	0.07	0.292
19				Is a premium brand	0.434	0.232	0.705	0.021	0.182	0.231	0.084		Professional Service	0.12402673	Provides a professional workshop service
20				Helps you to reduce your carbon footprint	0.415	0.242	0.158	0.691	0.148	0.24	0.142				Have a product range that is easier to understand
21				Provides a professional workshop service	0.341	0.272	0.213	0.126	0.749	0.166	0.17
22				Are recommended by vehicle manufacturers	0.338	0.249	0.174	0.207	0.224	0.748	0.169		Reduces Carbon Footprint	0.12087484	Helps you to reduce your carbon footprint
23				Is a forward-looking brand	0.357	0.282	0.202	0.133	0.158	0.176	0.755
24				Have a product range that is easier to understand	0.387	0.341	0.188	0.433	0.462	0.183	0.057		Recommended	0.10373634	Are recommended by vehicle manufacturers
25				Is a superior quality of motor oil compared to other brands	0.45	0.236	0.426	0.217	0.014	0.426	0.265
26													Premium Brand & Advanced Tech	0.07565221	Have products that are more technologically advanced
27															Is a premium brand
28
29													Forward-Looking	0.07003751	Is a forward-looking brand
Sheet1

Cell Formulas
Range	Formula
R2	R2	=E2
S2:S13	S2	=FILTER(D4:D25,E4:E25>=0.45)
U2	U2	=F2
V2:V4	V2	=FILTER(D4:D25,F4:F25>=0.45)
Dynamic array formulas.

Hi Sanjay,

Thanks for your time on this.
Data is coming correct could you please let us know how you have considered the number 0.45 in filter funtion
and column N:N is an input data I took from B column as of now data is coming correct but it should consider the Green color while taking the subgroup Column E to K

Thanks in advance

 

[SanjayGMusafir]

Hi Sanjay,

Thanks for your time on this.
Data is coming correct could you please let us know how you have considered the number 0.45 in filter funtion
and column N:N is an input data I took from B column as of now data is coming correct but it should consider the Green color while taking the subgroup Column E to K

Thanks in advance

To filter using Cell background colour can be done. But as per forum rules I can't share external links here. Also, copying and pasting someone else's work here is plagiarism that I can't do. But there is a methodical way of using Get.Cell Function through which we can identify a cell colour in formula and use as a filter.

 

[sksanjeev786]

To filter using Cell background colour can be done. But as per forum rules I can't share external links here. Also, copying and pasting someone else's work here is plagiarism that I can't do. But there is a methodical way of using Get.Cell Function through which we can identify a cell colour in formula and use as a filter.

okay....!!! thanks for the update Sanjay