CF formula for rows.

[serge]

Hi, I'm looking for a CF that will highlight the digits in rows according the digit put in cells of row 2.
It is separated this way because there is some more numbers that come in play but I remove to not be confusing, so sorry if there is 3 columns in between !

Original table :

Like in this example below, if I type a 1 in cell AT2, the cells in row 4,5,12,13 and 17 will highlight because they all contain the digit 1 in their second position.

Then if I type digit 3 in cell AO2 row 17 will be removed, because it has no 3 in first position.

Then if I type a 1 in cell BA2 then row 4,5 and 12 also will be removed leaving me with row 13 only.
My goal is to be left with only 1 number at the end according to the selections made in row 2.

Let say if I type a 3 in cell BH2 then it will only highlight row 4 which where I won't need no more selection to reduce to 1 number.
It might be a complicated CF but it will HELP me so much.

Thank you.

 

[Vogateer]

There's one example above that doesn't seem right to me, where you have a 3 and a 1, but rows 4 and 5 still have color despite not having a 3 in the first group of digits. If the example below is correct, then I'm misunderstanding what you want.

You would have to create a lot of rules if you want to keep the colors, but this conditional formatting formula appears to work for the rest of the examples:

=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))

 

[dreid1011]

There's one example above that doesn't seem right to me, where you have a 3 and a 1, but rows 4 and 5 still have color despite not having a 3 in the first group of digits. If the example below is correct, then I'm misunderstanding what you want.

View attachment 115884


You would have to create a lot of rules if you want to keep the colors, but this conditional formatting formula appears to work for the rest of the examples:

=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))

Nice formula, I thought maybe there was something simpler than what I was imagining, but I couldn't think of it. However, it doesn't seem to work for the following combination:

Book1
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	2	3	4				1	2	3				0	1	2				1	2	3
2		3					1						0
3
4	2						1						0								3		2103
5	2						1								2				1				2121
6
7	2							2					0							2			2202
8	2							2						1					1				2211
9
10	2								3				0						1				2301
11
12		3					1						0							2			3102
13		3					1							1					1				3111
14
15		3						2					0						1				3201
16
17			4				1						0						1				4101
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN4:BH17	Expression	=AN4<>""	text	NO
AN4:BH17	Expression	=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))	text	NO

The 1 in BA2 works, but not the 0 in AZ2. Or any 0s really:

Book1
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	2	3	4				1	2	3				0	1	2				1	2	3
2													0						1
3
4	2						1						0								3		2103
5	2						1								2				1				2121
6
7	2							2					0							2			2202
8	2							2						1					1				2211
9
10	2								3				0						1				2301
11
12		3					1						0							2			3102
13		3					1							1					1				3111
14
15		3						2					0						1				3201
16
17			4				1						0						1				4101
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN4:BH17	Expression	=AN4<>""	text	NO
AN4:BH17	Expression	=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))	text	NO

 

[Vogateer]

Nice formula, I thought maybe there was something simpler than what I was imagining, but I couldn't think of it. However, it doesn't seem to work for the following combination:

Book1
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	2	3	4				1	2	3				0	1	2				1	2	3
2		3					1						0
3
4	2						1						0								3		2103
5	2						1								2				1				2121
6
7	2							2					0							2			2202
8	2							2						1					1				2211
9
10	2								3				0						1				2301
11
12		3					1						0							2			3102
13		3					1							1					1				3111
14
15		3						2					0						1				3201
16
17			4				1						0						1				4101
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN4:BH17	Expression	=AN4<>""	text	NO
AN4:BH17	Expression	=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))	text	NO

The 1 in BA2 works, but not the 0 in AZ2. Or any 0s really:

Book1
	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	2	3	4				1	2	3				0	1	2				1	2	3
2													0						1
3
4	2						1						0								3		2103
5	2						1								2				1				2121
6
7	2							2					0							2			2202
8	2							2						1					1				2211
9
10	2								3				0						1				2301
11
12		3					1						0							2			3102
13		3					1							1					1				3111
14
15		3						2					0						1				3201
16
17			4				1						0						1				4101
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AN4:BH17	Expression	=AN4<>""	text	NO
AN4:BH17	Expression	=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))	text	NO

Good catch! I completely missed the 0 throwing things off. I believe this conditional formatting formula will fix that:

=AND(AN4<>"",SUM(EXACT($AN4:$BH4,$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))

 

[serge]

There's one example above that doesn't seem right to me, where you have a 3 and a 1, but rows 4 and 5 still have color despite not having a 3 in the first group of digits. If the example below is correct, then I'm misunderstanding what you want.

View attachment 115884


You would have to create a lot of rules if you want to keep the colors, but this conditional formatting formula appears to work for the rest of the examples:

=AND(AN4<>"",SUM(($AN4:$BH4=$AN$2:$BH$2)*($AN$2:$BH$2<>""))=SUM(--($AN$2:$BH$2<>"")))

Hi Vogateer, you are right row 4 and 5 should have not been colored ( my mistake ) I apologize.

 

[serge]

Vogateer, Thank you so much, your last formula work PERFECTLY fine.
Thank you so much for the help.

 

[serge]

dreid1011, Thank you too also for the help really appreciate it.