Need help with conditional formula

[neil_c]

I have a spreadsheet with thousands of rows containing numbers 1-20. I want to highlight specific numbers 3, 5, 8, 9, 15, 20 (7 numbers), etc.
Is there any formula for highlighting a row that has exactly 7 numbers? Do not highlight if any row contains one or two identical numbers. Basically, highlight the line that has the specific numbers 3,5,8,9,15,20.

1. example: 3,5,8,9,15,20,25= match 7number highlight (if possible a column can tell how many numbers matched with that row e.g.7)
3,5,7,16,21,24= do not highlight(even if it has few same number. ( here column can tell how many matched but do not highlight)
2. In addition, is there way if 7 number match in particular row then a coulum can tell ok 7 number matched in rows number 5.

Huge thanks!!

2.

 

[neil_c]

YES, range names are correct. I think it's because of office 365.

760816f8-7f97-11eb-ba0e-366c9fa44b5f.xlsx
	Y
12	7
Sheet1

Cell Formulas
Range	Formula
Y12	Y12	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$7:$V$7	Y12
MyPick	=Sheet1!$C$3:$I$3	Y7:Y16

 

[neil_c]

It works OK for me. Are you sure you've defined your range names correctly?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3			9	10	21	24	28	29	38
4
5			9	10	66	66	21	24	28	29	66	66	38	66	66	66	66	66	66	66	66	66		7		5
6			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0		11
7			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
8			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
9			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
10			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
11			9	10	66	66	21	24	28	29	38	66	66	66	66	66	66	66	66	66	66	66		7
12			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
13			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
14			66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66	66		0
Sheet1

Cell Formulas
Range	Formula
Z5:Z14	Z5	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
X5:X14	X5	=SUM(COUNTIF(C5:V5,MyPick))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$5:$V$14	X5, Z5
MyPick	=Sheet1!$C$3:$I$3	Z5, X5:X14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:W14	Expression	=AND(SUM(COUNTIF($C5:$V5,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(C5,MyPick,)))	text	NO

YES, range names are correct. I think it's because of office 365.

760816f8-7f97-11eb-ba0e-366c9fa44b5f.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	My pick numbers
3	9	10	21	24	28	29	38
4
5																						Match
6																						0
7	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7	7
8	10	11	12	20	23	26	28	30	31	34	35	43	45	53	54	56	61	71	76	80		2	7
9	3	9	16	19	24	30	32	33	34	35	44	48	59	61	63	66	69	70	75	78		2	7
10	4	5	6	7	10	11	16	17	20	23	24	28	34	35	52	56	59	64	65	70		3	7
11	10	14	17	22	27	28	29	41	42	46	48	50	54	59	60	62	64	66	71	76		3	7
12	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7	7
13	4	6	9	11	20	21	26	33	35	36	40	41	42	47	50	51	57	60	64	73		2	7
14	18	20	21	25	32	36	42	48	53	54	55	57	61	62	63	65	68	75	76	79		1	7
15	3	5	12	21	22	23	32	35	38	42	47	53	59	60	63	66	69	72	74	78		1	7
16	3	9	10	12	21	28	29	36	37	42	48	50	55	59	63	65	71	72	74	75		2	7
17	1	9	10	18	21	22	26	33	34	36	37	40	50	51	53	65	67	71	74	79		5
18	2	8	17	21	23	37	38	39	42	44	50	53	62	64	66	71	72	75	76	79		3
Sheet1

Cell Formulas
Range	Formula
Y7:Y16	Y7	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
X6:X14	X6	=SUM(COUNTIF(C6:V6,MyPick))
X15:X18	X15	=SUM(COUNTIF(C14:V14,MyPick))

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$7:$V$7	X7, Y7:Y16
MyPick	=Sheet1!$C$3:$I$3	X13:X14, Y7:Y16

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:V19674	Expression	=AND(SUM(COUNTIF($C7:$V7,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(C7,MyPick,)))	text	NO

 

[StephenCrump]

YES, range names are correct.

No they're not. You've defined MyData as a single row, and presumably copied the SORT() formula down the column - hence the duplicated result: 5 in Post #9.

I have defined MyData as the entire range of data - all rows. My SORT() formula is an array formula which returns the two results 5 and 11 (and blanks below).

In Post #10, click on the Clipboard icon (above and to the left of cell A1) and you can paste directly into a worksheet to see how my formulae work.

 

[neil_c]

yes are right but now it's giving an error!!

760816f8-7f97-11eb-ba0e-366c9fa44b5f.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	My pick numbers
3	9	10	21	24	28	29	38
4
5																						Match
6
7	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7	#SPILL!
8	10	11	12	20	23	26	28	30	31	34	35	43	45	53	54	56	61	71	76	80		2	#SPILL!
9	3	9	16	19	24	30	32	33	34	35	44	48	59	61	63	66	69	70	75	78		2	#SPILL!
10	4	5	6	7	10	11	16	17	20	23	24	28	34	35	52	56	59	64	65	70		3	#SPILL!
11	10	14	17	22	27	28	29	41	42	46	48	50	54	59	60	62	64	66	71	76		3	#SPILL!
12	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7	#SPILL!
13	4	6	9	11	20	21	26	33	35	36	40	41	42	47	50	51	57	60	64	73		2	#SPILL!
14	18	20	21	25	32	36	42	48	53	54	55	57	61	62	63	65	68	75	76	79		1	#SPILL!
15	3	5	12	21	22	23	32	35	38	42	47	53	59	60	63	66	69	72	74	78		2	#SPILL!
16	3	9	10	12	21	28	29	36	37	42	48	50	55	59	63	65	71	72	74	75		5	#SPILL!
17	1	9	10	18	21	22	26	33	34	36	37	40	50	51	53	65	67	71	74	79		3	#SPILL!
Sheet1

Cell Formulas
Range	Formula
X7:X17	X7	=SUM(COUNTIF(C7:V7,MyPick))
Y7:Y17	Y7	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$7:$V$17	Y7:Y17, X7
MyPick	=Sheet1!$C$3:$I$3	Y7:Y18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:V19674	Expression	=AND(SUM(COUNTIF($C7:$V7,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(C7,MyPick,)))	text	NO

 

[neil_c]

there is something I'm missing. I copied your sheet from the clipboard but it only coping in Text format, even coping with ALT+click.
Named RangesNameRefers ToCells
MyData=Sheet1!$C$5:$V$14 X5, Z5 how did you give reference to these cells.
MyPick=Sheet1!$C$3:$I$3Z5, X5:X14

 

[StephenCrump]

yes are right but now it's giving an error!!

The array formula goes in Y7 only. Delete your formulae in the cells below.

 

[neil_c]

The array formula goes in Y7 only. Delete your formulae in the cells below.

it worked!! I literally learned a new way to sort out this problem. I can't thank you enough for your Help.

760816f8-7f97-11eb-ba0e-366c9fa44b5f.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
2	My pick numbers
3	9	10	21	24	28	29	38
4
5																						Match	Row NO.
6
7	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7	7
8	10	11	12	20	23	26	28	30	31	34	35	43	45	53	54	56	61	71	76	80		2	12
9	3	9	16	19	24	30	32	33	34	35	44	48	59	61	63	66	69	70	75	78		2
10	4	5	6	7	10	11	16	17	20	23	24	28	34	35	52	56	59	64	65	70		3
11	10	14	17	22	27	28	29	41	42	46	48	50	54	59	60	62	64	66	71	76		3
12	9	10	12	19	21	24	28	29	30	34	38	40	46	49	50	58	63	64	71	78		7
13	4	6	9	11	20	21	26	33	35	36	40	41	42	47	50	51	57	60	64	73		2
14	18	20	21	25	32	36	42	48	53	54	55	57	61	62	63	65	68	75	76	79		1
15	3	5	12	21	22	23	32	35	38	42	47	53	59	60	63	66	69	72	74	78		2
16	3	9	10	12	21	28	29	36	37	42	48	50	55	59	63	65	71	72	74	75		5
Sheet1

Cell Formulas
Range	Formula
Y7:Y17	Y7	=SORT(IF(MMULT(--ISNUMBER(MATCH(MyData,MyPick,)),SEQUENCE(COLUMNS(MyData),,1,0))=COLUMNS(MyPick),ROW(MyData),""))
X7:X16	X7	=SUM(COUNTIF(C7:V7,MyPick))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyData	=Sheet1!$C$7:$V$17	X7:Y7
MyPick	=Sheet1!$C$3:$I$3	Y7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:V19674	Expression	=AND(SUM(COUNTIF($C7:$V7,MyPick))=COLUMNS(MyPick),ISNUMBER(MATCH(C7,MyPick,)))	text	NO

 

[StephenCrump]

it worked!!

Yay! I'm glad I could help.