Need top 3 data

[sksanjeev786]

Hi Team,

I need top 3 data from each row based on the yellow column (B,E,H etc) then (CFI etc) till A to V column for each row

book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
2
3	Top 3 Data	11.9	13.2	13.3	14.9	14.5	11.6	12.3	11.2	11.3	12.2	12	11.7	14.2	12.5	13.3	13.5	12.2	12.5	11.9	10.3	10.7
4	Top 3 Data	6.8	7.3	6.3	4.9	6.5	5.2	5.8	6.1	6.2	5.7	6.2	5.4	3.8	5.6	5.1	6.1	6.2	4.9	4.3	4.5	3.9
5	Top 3 Data	11.9	12.9	12.8	9	12.2	12.4	9.2	11.8	12.5	8.2	9.9	10.8	8.1	11.1	11.6	10.3	12.2	12.9	11	9.9	10
6	Top 3 Data	14.2	16.6	13.8	14.6	14.9	13.6	16.2	17.9	16	16.1	15.2	16.5	15.2	15.6	14.7	17.8	17.6	16.2	20	20.9	21.9
7	Top 3 Data	8.5	8.6	7.5	6.6	6.6	7	7.7	8.8	9.3	6.2	6.5	7.5	6.5	6.6	7.5	7.3	7.1	7	10	9.8	9.9
8	Top 3 Data	2.9	1.7	2.8	3.3	2.1	3.1	3.3	4	2.5	3.2	3.6	3.4	2.7	3.1	3.9	2	2.8	2.8	1	2.8	1.3
9	Top 3 Data	3.9	3.6	3.9	5.6	7.1	5.8	4.5	4.2	4	6.1	7.1	4.8	4.3	6.6	4.4	4	4.1	4.2	2.4	3.1	3.8
Sheet3

 

[Fluff]

How & where do you want the output?

 

[sksanjeev786]

How & where do you want the output?

Hi Fluff

Thanks for looking into it.

I need highlighted color on the cells

Regards,
Sanjeev

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
2
3	Top 3 Data	11.9	13.2	13.3	14.9	14.5	11.6	12.3	11.2	11.3	12.2	12	11.7	14.2	12.5	13.3	13.5	12.2	12.5	11.9	10.3	10.7
4	Top 3 Data	6.8	7.3	6.3	4.9	6.5	5.2	5.8	6.1	6.2	5.7	6.2	5.4	3.8	5.6	5.1	6.1	6.2	4.9	4.3	4.5	3.9
5	Top 3 Data	11.9	12.9	12.8	9	12.2	12.4	9.2	11.8	12.5	8.2	9.9	10.8	8.1	11.1	11.6	10.3	12.2	12.9	11	9.9	10
6	Top 3 Data	14.2	16.6	13.8	14.6	14.9	13.6	16.2	17.9	16	16.1	15.2	16.5	15.2	15.6	14.7	17.8	17.6	16.2	20	20.9	21.9
7	Top 3 Data	8.5	8.6	7.5	6.6	6.6	7	7.7	8.8	9.3	6.2	6.5	7.5	6.5	6.6	7.5	7.3	7.1	7	10	9.8	9.9
8	Top 3 Data	2.9	1.7	2.8	3.3	2.1	3.1	3.3	4	2.5	3.2	3.6	3.4	2.7	3.1	3.9	2	2.8	2.8	1	2.8	1.3
9	Top 3 Data	3.9	3.6	3.9	5.6	7.1	5.8	4.5	4.2	4	6.1	7.1	4.8	4.3	6.6	4.4	4	4.1	4.2	2.4	3.1	3.8
Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=1),3),MOD(COLUMN(B3),3)=1)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=0),3),MOD(COLUMN(B3),3)=0)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=2),3),MOD(COLUMN(B3),3)=2)	text	NO

 

[sksanjeev786]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
2
3	Top 3 Data	11.9	13.2	13.3	14.9	14.5	11.6	12.3	11.2	11.3	12.2	12	11.7	14.2	12.5	13.3	13.5	12.2	12.5	11.9	10.3	10.7
4	Top 3 Data	6.8	7.3	6.3	4.9	6.5	5.2	5.8	6.1	6.2	5.7	6.2	5.4	3.8	5.6	5.1	6.1	6.2	4.9	4.3	4.5	3.9
5	Top 3 Data	11.9	12.9	12.8	9	12.2	12.4	9.2	11.8	12.5	8.2	9.9	10.8	8.1	11.1	11.6	10.3	12.2	12.9	11	9.9	10
6	Top 3 Data	14.2	16.6	13.8	14.6	14.9	13.6	16.2	17.9	16	16.1	15.2	16.5	15.2	15.6	14.7	17.8	17.6	16.2	20	20.9	21.9
7	Top 3 Data	8.5	8.6	7.5	6.6	6.6	7	7.7	8.8	9.3	6.2	6.5	7.5	6.5	6.6	7.5	7.3	7.1	7	10	9.8	9.9
8	Top 3 Data	2.9	1.7	2.8	3.3	2.1	3.1	3.3	4	2.5	3.2	3.6	3.4	2.7	3.1	3.9	2	2.8	2.8	1	2.8	1.3
9	Top 3 Data	3.9	3.6	3.9	5.6	7.1	5.8	4.5	4.2	4	6.1	7.1	4.8	4.3	6.6	4.4	4	4.1	4.2	2.4	3.1	3.8
Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=1),3),MOD(COLUMN(B3),3)=1)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=0),3),MOD(COLUMN(B3),3)=0)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=2),3),MOD(COLUMN(B3),3)=2)	text	NO

PERFECT...!!!

Thank you so much Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[sksanjeev786]

You're welcome & thanks for the feedback.

Hi Fluff,

I have eager to know how you and your team know many things in Excel :P

I know Experience matters but still wanted to know how you are able to solve anything that means anything in excel depends on the requirements.

I know everything is possible but sometimes I am not able to find the way to solve in excel but you and your team is Simply Awosome!!!

The team is really doing a GREAT job and supporting us in many ways!!!!

Regards,
Sanjeev

 

[Fluff]

I've learnt most of what I know from posting here & seeing how others do things.

 

[sksanjeev786]

I've learnt most of what I know from posting here & seeing how others do things.

Thank you so much Fluff for sharing your view on above things

 

[sksanjeev786]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3	1	2	3
2
3	Top 3 Data	11.9	13.2	13.3	14.9	14.5	11.6	12.3	11.2	11.3	12.2	12	11.7	14.2	12.5	13.3	13.5	12.2	12.5	11.9	10.3	10.7
4	Top 3 Data	6.8	7.3	6.3	4.9	6.5	5.2	5.8	6.1	6.2	5.7	6.2	5.4	3.8	5.6	5.1	6.1	6.2	4.9	4.3	4.5	3.9
5	Top 3 Data	11.9	12.9	12.8	9	12.2	12.4	9.2	11.8	12.5	8.2	9.9	10.8	8.1	11.1	11.6	10.3	12.2	12.9	11	9.9	10
6	Top 3 Data	14.2	16.6	13.8	14.6	14.9	13.6	16.2	17.9	16	16.1	15.2	16.5	15.2	15.6	14.7	17.8	17.6	16.2	20	20.9	21.9
7	Top 3 Data	8.5	8.6	7.5	6.6	6.6	7	7.7	8.8	9.3	6.2	6.5	7.5	6.5	6.6	7.5	7.3	7.1	7	10	9.8	9.9
8	Top 3 Data	2.9	1.7	2.8	3.3	2.1	3.1	3.3	4	2.5	3.2	3.6	3.4	2.7	3.1	3.9	2	2.8	2.8	1	2.8	1.3
9	Top 3 Data	3.9	3.6	3.9	5.6	7.1	5.8	4.5	4.2	4	6.1	7.1	4.8	4.3	6.6	4.4	4	4.1	4.2	2.4	3.1	3.8
Database

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=1),3),MOD(COLUMN(B3),3)=1)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=0),3),MOD(COLUMN(B3),3)=0)	text	NO
B3:V9	Expression	=AND(B3>=LARGE(FILTER($B3:$V3,MOD(COLUMN($B3:$V3),3)=2),3),MOD(COLUMN(B3),3)=2)	text	NO

Hi Fluff,

Now I need a Top score (1 highlighted in each section) on the same data file and I tried to modify in the below formula but did not get only top score data instead of top 3

=AND(B25>=LARGE(FILTER($B25:$V25,MOD(COLUMN($B25:$V25),3)=1),1),MOD(COLUMN(B25),3)=1)

Regards,
Sanjeev