Highest data with each column including Statmements

[sksanjeev786]

Hi Team,

I need the highest data from each column example shown in I to M.

book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Total PCMO Full year 2021 data	Premium Discerners	Convenience Cravers	Environmental Product Pionners	High Performance Product Enthusiats	Hispanics		Output
2	Provides the best engine protection	45	38	53	66	82	39		Total PCMO Full year 2021 data	It prolongs the life of my engine	50
3	Helps my car perform at its best	47	41	49	74	61	44		Premium Discerners	Helps my car perform at its best	41	It prolongs the life of my engine	41
4	Is best for cars with high mileage	31	29	35	33	2	30		Convenience Cravers	It prolongs the life of my engine	58
5	It’s formulated for extreme (hot / cold) temperatures	24	25	28	30	12	23		Environmental Product Pionners	It’s produced in an environmentally friendly way	100
6	It can be used in extreme driving conditions (e.g. high speed, heavy loads, etc.)	20	25	21	19	7	21		High Performance Product Enthusiats	Provides the best engine protection	82
7	Uses the latest advanced technology/innovations	22	29	18	42	39	21		Hispanics	Helps my car perform at its best	44
8	It effectively cleans and removes the residues of the engine	38	35	45	60	41	32
9	Allows my vehicle to go longer between oil changes	37	34	45	45	2	36
10	Clear signs on pack that the oil is not counterfeit	13	17	11	15	-	15
11	Helps to pollute less while I am driving	22	24	16	57	12	22
12	It’s produced in an environmentally friendly way	21	26	14	100	-	22
13	It keeps my engine quiet	31	32	30	47	23	29
14	It prolongs the life of my engine	50	41	58	71	65	43
15	It improves fuel efficiency	44	40	44	69	54	41
16	Other	4	1	6	*	-	3
Sheet1

Thanks in advance
Regards,
Sanjeev

 

[JamesCanale]

365 only. It's a lot easier by reordering the columns in the output - but it could be done just the way you had it.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Total PCMO Full year 2021 data	Premium Discerners	Convenience Cravers	Environmental Product Pionners	High Performance Product Enthusiats	Hispanics		Output
2	Provides the best engine protection	45	38	53	66	82	39		Total PCMO Full year 2021 data	50	It prolongs the life of my engine
3	Helps my car perform at its best	47	41	49	74	61	44		Premium Discerners	41	Helps my car perform at its best	It prolongs the life of my engine
4	Is best for cars with high mileage	31	29	35	33	2	30		Convenience Cravers	58	It prolongs the life of my engine
5	It’s formulated for extreme (hot / cold) temperatures	24	25	28	30	12	23		Environmental Product Pionners	100	It’s produced in an environmentally friendly way
6	It can be used in extreme driving conditions (e.g. high speed, heavy loads, etc.)	20	25	21	19	7	21		High Performance Product Enthusiats	82	Provides the best engine protection
7	Uses the latest advanced technology/innovations	22	29	18	42	39	21		Hispanics	44	Helps my car perform at its best
8	It effectively cleans and removes the residues of the engine	38	35	45	60	41	32
9	Allows my vehicle to go longer between oil changes	37	34	45	45	2	36
10	Clear signs on pack that the oil is not counterfeit	13	17	11	15	-	15
11	Helps to pollute less while I am driving	22	24	16	57	12	22
12	It’s produced in an environmentally friendly way	21	26	14	100	-	22
13	It keeps my engine quiet	31	32	30	47	23	29
14	It prolongs the life of my engine	50	41	58	71	65	43
15	It improves fuel efficiency	44	40	44	69	54	41
16	Other	4	1	6	*	-	3
Sheet26

Cell Formulas
Range	Formula
J2	J2	=MAX(B2:B16)
K2	K2	=TRANSPOSE(FILTER($A$2:$A$16,$B$2:$B$16=J2))
J3	J3	=MAX(C2:C16)
K3:L3	K3	=TRANSPOSE(FILTER($A$2:$A$16,$C$2:$C$16=J3))
J4	J4	=MAX(D2:D16)
K4	K4	=TRANSPOSE(FILTER($A$2:$A$16,$D$2:$D$16=J4))
J5	J5	=MAX(E2:E16)
K5	K5	=TRANSPOSE(FILTER($A$2:$A$16,$E$2:$E$16=J5))
J6	J6	=MAX(F2:F16)
K6	K6	=TRANSPOSE(FILTER($A$2:$A$16,$F$2:$F$16=J6))
J7	J7	=MAX(G2:G16)
K7	K7	=TRANSPOSE(FILTER($A$2:$A$16,$G$2:$G$16=J7))
Dynamic array formulas.

 

[sksanjeev786]

365 only. It's a lot easier by reordering the columns in the output - but it could be done just the way you had it.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Total PCMO Full year 2021 data	Premium Discerners	Convenience Cravers	Environmental Product Pionners	High Performance Product Enthusiats	Hispanics		Output
2	Provides the best engine protection	45	38	53	66	82	39		Total PCMO Full year 2021 data	50	It prolongs the life of my engine
3	Helps my car perform at its best	47	41	49	74	61	44		Premium Discerners	41	Helps my car perform at its best	It prolongs the life of my engine
4	Is best for cars with high mileage	31	29	35	33	2	30		Convenience Cravers	58	It prolongs the life of my engine
5	It’s formulated for extreme (hot / cold) temperatures	24	25	28	30	12	23		Environmental Product Pionners	100	It’s produced in an environmentally friendly way
6	It can be used in extreme driving conditions (e.g. high speed, heavy loads, etc.)	20	25	21	19	7	21		High Performance Product Enthusiats	82	Provides the best engine protection
7	Uses the latest advanced technology/innovations	22	29	18	42	39	21		Hispanics	44	Helps my car perform at its best
8	It effectively cleans and removes the residues of the engine	38	35	45	60	41	32
9	Allows my vehicle to go longer between oil changes	37	34	45	45	2	36
10	Clear signs on pack that the oil is not counterfeit	13	17	11	15	-	15
11	Helps to pollute less while I am driving	22	24	16	57	12	22
12	It’s produced in an environmentally friendly way	21	26	14	100	-	22
13	It keeps my engine quiet	31	32	30	47	23	29
14	It prolongs the life of my engine	50	41	58	71	65	43
15	It improves fuel efficiency	44	40	44	69	54	41
16	Other	4	1	6	*	-	3
Sheet26

Cell Formulas
Range	Formula
J2	J2	=MAX(B2:B16)
K2	K2	=TRANSPOSE(FILTER($A$2:$A$16,$B$2:$B$16=J2))
J3	J3	=MAX(C2:C16)
K3:L3	K3	=TRANSPOSE(FILTER($A$2:$A$16,$C$2:$C$16=J3))
J4	J4	=MAX(D2:D16)
K4	K4	=TRANSPOSE(FILTER($A$2:$A$16,$D$2:$D$16=J4))
J5	J5	=MAX(E2:E16)
K5	K5	=TRANSPOSE(FILTER($A$2:$A$16,$E$2:$E$16=J5))
J6	J6	=MAX(F2:F16)
K6	K6	=TRANSPOSE(FILTER($A$2:$A$16,$F$2:$F$16=J6))
J7	J7	=MAX(G2:G16)
K7	K7	=TRANSPOSE(FILTER($A$2:$A$16,$G$2:$G$16=J7))
Dynamic array formulas.

Waw!!!!

Thank you so much!!!!