VBA, Check only selected betting row with entire results

[Kishan]

Using Excel 2000

Hi,


I got results in the columns C:P (R1 Through R14)
I got betting sets in the columns S:AF (B1 Through B14)

Bit complicate, I want to check any "1" betting ROW with entire results

For now I am using the formula which check if find grater than 9
Formula is in the cell Q6 and copied to down

=IF(SUMPRODUCT(--($S$6:$AF$6=C6:P6))>9,SUMPRODUCT(--($S$6:$AF$6=C6:P6)),"")

What is the problem I need to change formula every time I want to check desire row

I require VBA solution, which can give me an input option where I can enter desire combi nº is shown in column R and macro could populate the result in column Q

Please need help with this...

Example results in column Q are shown by above formula

Book1
	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	AA	AB	AC	AD	AE	AF	AG
1
2
3
4
5			R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13	R14	EM	Combi n	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12	B13	B14
6			X	1	1	1	2	1	2	1	2	X	1	1	X	1		1	1	1	1	2	1	1	X	1	1	2	2	1	1	1
7			X	X	2	1	X	X	1	1	2	1	X	2	1	1		2	2	1	2	1	1	X	1	2	1	2	X	2	1	1
8			X	1	1	1	2	1	2	1	2	X	1	1	X	1		3	2	1	X	2	1	1	X	2	2	1	2	X	1	2
9			2	1	1	X	2	1	X	1	1	2	2	1	1	1	11	4	1	X	X	2	2	X	1	1	1	1	1	X	1	2
10			1	X	1	X	1	1	2	1	1	X	X	X	1	1		5	1	2	X	1	2	1	2	1	X	2	1	X	1	X
11			X	2	X	X	2	X	1	1	2	1	1	1	1	2		6	X	1	X	2	1	1	1	X	1	X	2	2	2	1
12			X	1	1	2	1	1	1	2	1	1	X	1	X	1		7	2	1	1	1	X	1	X	1	X	2	2	1	1	X
13			1	X	X	2	1	X	1	1	1	2	1	1	X	1		8	2	X	X	X	1	2	1	1	X	X	X	X	2	X
14			1	1	1	2	X	X	X	1	X	X	1	X	X	X		9	X	2	2	X	1	1	2	2	X	1	1	2	2	2
15			1	X	X	1	1	1	1	1	X	1	1	2	X	1		10	1	1	1	1	X	2	1	X	2	1	1	X	1	X
16			X	1	X	2	1	2	X	X	X	1	1	1	X	X		11	X	1	1	1	X	X	1	2	2	2	1	1	1	1
17			1	1	X	1	2	1	X	1	1	1	2	1	X	1		12	2	2	X	X	X	1	2	1	1	1	1	2	1	X
18			1	1	1	1	1	X	1	1	1	2	2	X	1	1	10	13	X	1	1	2	X	2	2	2	1	1	2	X	1	1
19			X	1	1	X	X	1	1	1	1	2	1	1	X	X		14	1	2	X	X	1	2	X	1	1	X	X	X	X	X
20			1	1	1	1	2	1	X	X	1	2	X	1	X	2		15	1	1	X	1	1	X	X	X	1	2	X	2	2	X
21			1	1	1	2	2	1	1	X	1	2	1	1	X	1		16	1	2	1	1	2	2	2	1	1	1	1	1	X	X
22			2	1	X	1	1	X	1	1	1	2	1	1	1	2		17	X	2	1	1	X	2	X	2	1	X	1	2	X	2
23			1	1	1	1	1	2	1	1	2	1	X	X	X	1		18	X	1	X	1	1	2	1	2	2	1	2	1	1	1
24			X	1	1	2	1	1	2	1	1	X	1	1	1	X		19	1	1	X	1	1	2	2	2	1	1	X	1	1	X
25			1	1	1	1	X	1	1	1	1	1	1	1	1	1		20	2	1	X	2	1	2	2	1	2	2	2	X	1	X
26			X	1	1	1	1	2	1	1	X	X	1	1	X	1		21	2	1	1	1	X	2	X	2	1	X	X	X	2	1
27			1	2	X	X	X	X	1	2	1	1	1	X	1	1		22	1	X	1	2	1	X	1	1	1	1	X	1	X	1
28			1	1	1	2	1	2	X	X	X	X	1	X	1	X		23	X	2	1	2	2	1	X	1	2	1	2	2	1	1
29			1	1	1	2	2	1	X	1	X	1	2	1	X	2		24	1	1	1	X	X	X	1	1	1	1	X	X	1	X
30			1	1	1	X	1	1	1	X	1	1	X	1	1	2		25	1	2	X	1	2	1	2	1	X	1	2	2	2	X
31			1	1	1	X	1	1	1	1	1	1	1	1	1	1	10	26	2	X	2	2	X	2	1	2	1	2	2	2	1	1
32			1	X	1	1	2	1	1	X	X	X	1	X	2	1		27	2	1	X	1	1	1	1	X	1	1	X	1	X	1
33			2	2	1	1	X	1	X	2	1	2	2	1	X	1		28	2	1	1	X	2	X	1	1	X	1	2	X	2	X
34			1	X	1	2	1	1	1	1	1	1	X	1	X	1		29	X	1	2	2	2	1	X	X	2	1	1	1	X	1
35			2	1	1	1	2	X	1	X	X	1	1	X	X	1		30	1	2	1	1	X	1	2	2	X	X	2	X	1	X
36			1	X	X	2	1	1	2	X	1	1	X	1	2	X		31	1	1	1	X	X	1	1	2	1	1	X	1	X	1
37			X	1	1	1	1	1	1	1	1	2	X	X	X	X		32	X	1	2	X	2	1	1	1	1	1	1	X	1	X
38			X	2	1	1	X	X	1	1	1	2	X	1	1	1		33	X	2	2	1	1	2	1	1	2	X	1	X	X	X
39			X	1	2	1	2	1	X	X	X	X	X	1	1	2		34	1	1	1	2	2	1	2	X	1	X	1	2	2	1
40			1	1	1	X	X	1	2	X	1	2	1	X	X	1		35	2	2	1	2	2	1	1	1	2	X	2	X	2	2
41			X	X	X	1	1	X	1	1	1	1	X	X	1	1		36	X	X	X	1	1	X	X	2	1	1	1	2	X	2
42			2	X	2	1	X	1	X	1	1	X	1	2	2	1		37	1	2	X	X	1	X	2	1	1	X	1	1	1	X
43			X	X	1	2	X	1	2	1	1	1	1	1	2	1		38	1	X	1	X	2	1	1	2	X	1	2	1	1	1
44			X	X	1	X	1	1	1	1	X	1	2	1	1	X		39	1	2	1	X	1	1	2	1	X	1	2	1	1	2
45			1	1	X	1	1	2	1	1	1	1	1	X	1	1		40	X	X	2	1	1	1	1	1	1	1	1	1	1	X
46			1	1	1	1	X	2	2	1	1	1	1	1	X	1		41	1	1	2	1	2	2	1	1	2	2	1	1	1	2
47			2	X	1	1	1	1	1	X	1	1	1	1	X	1		42	1	1	1	1	2	2	2	1	X	X	X	2	X	1
48			1	2	1	X	2	1	2	1	1	1	1	1	1	1		43	2	1	2	X	1	1	X	1	X	X	X	2	2	1
49			X	1	X	1	2	1	2	1	X	2	X	X	2	1		44	1	1	1	1	X	X	2	1	X	1	X	1	2	1
50			X	1	1	1	1	X	1	1	1	1	1	1	1	2		45	1	X	2	2	X	2	1	2	1	X	1	1	X	1
51
sheet1

Thank you in advance

Regards,
Kishan

 

[Marcelo Branco]

Hi Marcelo Branco, I never thought it could be done this way it is very practical, brilliant idea!!

I like your formula non-vba solution!!

Thank you very much for your time and help

Regards,
Kishan

You are very welcome. Glad to help.

M.