How to reduce betting blokes that can guarantee max 1 incorrect

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Here I have a question how to reduce betting blokes that can guarantee max 1 incorrect, what I mean this, for example "Euromillions" playing 8 numbers direct we are playing 56 lines it cost 140€ "Formula=COMBIN(8,5)" if out of 8 numbers 5 numbers are correct we get the 1st price guarantee 100%. </SPAN></SPAN>
But instead 8 numbers direct if played in reduction we play only 5 lines it cost 12.50€ but it guarantee only 4 numbers can be correct if numbers drawn any 5 out of played 8. So far 1 number less. </SPAN></SPAN>

My question is about to play football reduction...it is difficult how do I explain it but here is my attempt...</SPAN></SPAN>

Say for in example1 there are 2 teams the can have the end result in a 9 different ways as shown in the cells C6:D14...I can bet all 9 but as it is a money question I want to play less combinations but ensuring 1 must be correct out of any 9 results if I bet 3 lines as shown in the cells H6:I8 there is minimum 1 correct and 1 wrong. </SPAN></SPAN>

Example1...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5		Combi n	Team1	Team2			Combi n	Team1	Team2
6		1	1	1			1	1	1
7		2	1	X			2	X	X
8		3	1	2			3	2	1
9		4	X	1
10		5	X	X
11		6	X	2
12		7	2	1
13		8	2	X
14		9	2	2
15
16
17
18
Sheet2

Say for in example2 there are 3 teams the can have the end result in a 27 different ways as shown in the cells C6:E32...I can bet all 27 but as it is a money question I want to play less combinations but ensuring 1 must be correct out of any 27 results if I bet 6 lines as shown in the cells H6:J11 there is minimum 2 correct and one wrong.</SPAN></SPAN>

Example2...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5		Combi n	Team1	Team2	Team3		Combi n	Team1	Team2	Team3
6		1	1	1	1		1	1	1	1
7		2	1	1	X		2	X	X	X
8		3	1	1	2		3	2	2	2
9		4	1	X	1		4	X	1	1
10		5	1	X	X		5	2	1	1
11		6	1	X	2		6	1	X	X
12		7	1	2	1
13		8	1	2	X
14		9	1	2	2
15		10	X	1	1
16		11	X	1	X
17		12	X	1	2
18		13	X	X	1
19		14	X	X	X
20		15	X	X	2
21		16	X	2	1
22		17	X	2	X
23		18	X	2	2
24		19	2	1	1
25		20	2	1	X
26		21	2	1	2
27		22	2	X	1
28		23	2	X	X
29		24	2	X	2
30		25	2	2	1
31		26	2	2	X
32		27	2	2	2
33
34
35
36
Sheet3

</SPAN></SPAN>

The above result I have come up checking one by one it has took 2, 3 days to ensure. </SPAN></SPAN>
And I guess they are right but not sure 100%</SPAN></SPAN>

As the team increases lines increases...now I want to work out with 4 teams there would be 81 lines as per example3, is there any macro or VBA can extract only those lines out of 81 which has 3 correct and 1 wrong... manually I think it is not possible for me.</SPAN></SPAN>

Example3...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4
5		Combi n	Team1	Team2	Team3	Team4			Combi n	Team1	Team2	Team3	Team4
6		1	1	1	1	1			1
7		2	1	1	1	X			?
8		3	1	1	1	2			?
9		4	1	1	X	1			?
10		5	1	1	X	X			?
11		6	1	1	X	2			?
12		7	1	1	2	1			?
13		8	1	1	2	X			?
14		9	1	1	2	2			?
15		10	1	X	1	1			?
16		11	1	X	1	X			?
17		12	1	X	1	2			?
18		13	1	X	X	1			?
19		14	1	X	X	X			?
20		15	1	X	X	2
21		16	1	X	2	1
22		17	1	X	2	X
23		18	1	X	2	2
24		19	1	2	1	1
25		20	1	2	1	X
26		21	1	2	1	2
27		22	1	2	X	1
28		23	1	2	X	X
29		24	1	2	X	2
30		25	1	2	2	1
31		26	1	2	2	X
32		27	1	2	2	2
33		28	X	1	1	1
34		29	X	1	1	X
35		30	X	1	1	2
36		31	X	1	X	1
37		32	X	1	X	X
38		33	X	1	X	2
39		34	X	1	2	1
40		35	X	1	2	X
41		36	X	1	2	2
42		37	X	X	1	1
43		38	X	X	1	X
44		39	X	X	1	2
45		40	X	X	X	1
46		41	X	X	X	X
47		42	X	X	X	2
48		43	X	X	2	1
49		44	X	X	2	X
50		45	X	X	2	2
51		46	X	2	1	1
52		47	X	2	1	X
53		48	X	2	1	2
54		49	X	2	X	1
55		50	X	2	X	X
56		51	X	2	X	2
57		52	X	2	2	1
58		53	X	2	2	X
59		54	X	2	2	2
60		55	2	1	1	1
61		56	2	1	1	X
62		57	2	1	1	2
63		58	2	1	X	1
64		59	2	1	X	X
65		60	2	1	X	2
66		61	2	1	2	1
67		62	2	1	2	X
68		63	2	1	2	2
69		64	2	X	1	1
70		65	2	X	1	X
71		66	2	X	1	2
72		67	2	X	X	1
73		68	2	X	X	X
74		69	2	X	X	2
75		70	2	X	2	1
76		71	2	X	2	X
77		72	2	X	2	2
78		73	2	2	1	1
79		74	2	2	1	X
80		75	2	2	1	2
81		76	2	2	X	1
82		77	2	2	X	X
83		78	2	2	X	2
84		79	2	2	2	1
85		80	2	2	2	X
86		81	2	2	2	2
87
88
89
90
Sheet4

</SPAN></SPAN>


Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hi,</SPAN></SPAN>

In other words in the example-3 I want those combinations in the columns J, K, L, M which can pass at least 3 correct out of 4 "with any of one results come out from 81 total combinations out of columns C, D, E, F" </SPAN></SPAN>

Note: As in example-2 if any of 27 combinations columns C, D, E is checked with betting columns H, I, J there are 2 correct always </SPAN></SPAN>

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

bump

 

[Kishan]

Bump

 

[Kishan]

Hi,</SPAN></SPAN>

May I have not explained it well in the #post1 some time it complicate to explain but may this can describe better my need hope so...</SPAN></SPAN>

Example...result has come out X-1-1-2 if we check with 81 bets it has passed 4 with combi nº 30 shown highlighted in yellow cells H34:K34 so far betting 81 we sure that always at least 1 of the 4 will passed correct</SPAN></SPAN>

As we already have the 81 bets of the of 4 triples (3*3*3*3=81) we are going to see what needs to be done to have a smaller combination but that always fails at most one sign, that is to say that it has at least one bet with 3 hits.</SPAN></SPAN>

Now for example result is a same X-1-1-2 and if I would have bet only 2 out of 81...</SPAN></SPAN>
1st...1-X-X-X in this one passed only 2 </SPAN></SPAN>
2nd...X-X-2-X in this one passed only 2</SPAN></SPAN>

So far these are not valid because we need the combinations at least one bet with 3 hits.</SPAN></SPAN>

Example for 81 bets could be...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4		Team	Win	Draw	Loss		Combi N	P1	P2	P3	P4
5		1	1	X	2		1	1	1	1	1
6		2	1	X	2		2	1	1	1	X
7		3	1	X	2		3	1	1	1	2
8		4	1	X	2		4	1	1	X	1
9							5	1	1	X	X
10							6	1	1	X	2
11							7	1	1	2	1
12							8	1	1	2	X
13							9	1	1	2	2
14							10	1	X	1	1
15							11	1	X	1	X
16							12	1	X	1	2
17							13	1	X	X	1
18							14	1	X	X	X
19							15	1	X	X	2
20							16	1	X	2	1
21							17	1	X	2	X
22							18	1	X	2	2
23							19	1	2	1	1
24							20	1	2	1	X
25							21	1	2	1	2
26							22	1	2	X	1
27							23	1	2	X	X
28							24	1	2	X	2
29							25	1	2	2	1
30							26	1	2	2	X
31							27	1	2	2	2
32							28	X	1	1	1
33							29	X	1	1	X
34							30	X	1	1	2
35							31	X	1	X	1
36							32	X	1	X	X
37							33	X	1	X	2
38							34	X	1	2	1
39							35	X	1	2	X
40							36	X	1	2	2
41							37	X	X	1	1
42							38	X	X	1	X
43							39	X	X	1	2
44							40	X	X	X	1
45							41	X	X	X	X
46							42	X	X	X	2
47							43	X	X	2	1
48							44	X	X	2	X
49							45	X	X	2	2
50							46	X	2	1	1
51							47	X	2	1	X
52							48	X	2	1	2
53							49	X	2	X	1
54							50	X	2	X	X
55							51	X	2	X	2
56							52	X	2	2	1
57							53	X	2	2	X
58							54	X	2	2	2
59							55	2	1	1	1
60							56	2	1	1	X
61							57	2	1	1	2
62							58	2	1	X	1
63							59	2	1	X	X
64							60	2	1	X	2
65							61	2	1	2	1
66							62	2	1	2	X
67							63	2	1	2	2
68							64	2	X	1	1
69							65	2	X	1	X
70							66	2	X	1	2
71							67	2	X	X	1
72							68	2	X	X	X
73							69	2	X	X	2
74							70	2	X	2	1
75							71	2	X	2	X
76							72	2	X	2	2
77							73	2	2	1	1
78							74	2	2	1	X
79							75	2	2	1	2
80							76	2	2	X	1
81							77	2	2	X	X
82							78	2	2	X	2
83							79	2	2	2	1
84							80	2	2	2	X
85							81	2	2	2	2
86
87
88
Sheet5

I need to find the few smaller combination but that always fails at most one sign. In the any case any of the 1 result is out of the 81 bets. </SPAN></SPAN>

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN></SPAN></SPAN>

 

[Eric W]

From a mathematical point of view, this is really interesting. I'm sure someone has done some research on this. Mathematically it would be defined something like: "Given a set S containing sets of ordered elements, find a subset T of S containing a minimal number of sets of ordered elements such that every set in S differs by at most 1 element from a set in T." Something in graph theory maybe. Doing a quick web search didn't reveal anything, so I tried coming up with my own algorithm.

First up is a basic "greedy" method. Just go down the list of combinations, and every time you find one that doesn't match your rules in the subset, add it to the subset. It certainly works, but is not guaranteed to find a minimal subset. In your 3-team example, it came up with 8 teams, while you had 6.

Next I tried a brute force method. I figured with a small enough set, a fast computer could find an optimal set. It worked quite well for 1 team. For 2 teams it came up with the same sets you did. For 3 teams, I got this result:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Values	1	2	X
2	Teams	3
3
4
5		Combination	Team 1	Team 2	Team 3	Closest to:		Combination	Team 1	Team 2	Team 3
6		1	1	1	1	1		1	1	1	1
7		2	1	1	2	1		5	1	2	2
8		3	1	1	X	1		11	2	1	2
9		4	1	2	1	1		13	2	2	1
10		5	1	2	2	5		27	X	X	X
11		6	1	2	X	5
12		7	1	X	1	1
13		8	1	X	2	5
14		9	1	X	X	27
15		10	2	1	1	1
16		11	2	1	2	11
17		12	2	1	X	11
18		13	2	2	1	13
19		14	2	2	2	5
20		15	2	2	X	13
21		16	2	X	1	13
22		17	2	X	2	11
23		18	2	X	X	27
24		19	X	1	1	1
25		20	X	1	2	11
26		21	X	1	X	27
27		22	X	2	1	13
28		23	X	2	2	5
29		24	X	2	X	27
30		25	X	X	1	27
31		26	X	X	2	27
32		27	X	X	X	27
Sheet2

You only need 5 sets instead of the 6 you have.

Looking good so far, but here's where the vast number of combinations kicks in. The macro ran for hours without finding a solution. I do know that it would require at least 8 sets to make it work. I'm trying to find a way to use various symmetries to reduce the number of calculations. I'll let you know if I figure it out.

 

[Kishan]

From a mathematical point of view, this is really interesting. I'm sure someone has done some research on this. Mathematically it would be defined something like: "Given a set S containing sets of ordered elements, find a subset T of S containing a minimal number of sets of ordered elements such that every set in S differs by at most 1 element from a set in T." Something in graph theory maybe. Doing a quick web search didn't reveal anything, so I tried coming up with my own algorithm.

First up is a basic "greedy" method. Just go down the list of combinations, and every time you find one that doesn't match your rules in the subset, add it to the subset. It certainly works, but is not guaranteed to find a minimal subset. In your 3-team example, it came up with 8 teams, while you had 6.

Next I tried a brute force method. I figured with a small enough set, a fast computer could find an optimal set. It worked quite well for 1 team. For 2 teams it came up with the same sets you did. For 3 teams, I got this result:

You only need 5 sets instead of the 6 you have.

Looking good so far, but here's where the vast number of combinations kicks in. The macro ran for hours without finding a solution. I do know that it would require at least 8 sets to make it work. I'm trying to find a way to use various symmetries to reduce the number of calculations. I'll let you know if I figure it out.

Eric, your hunt for reducing down at least minimum to 2 must pass, within the 5 matches you discovery is accurate.</SPAN></SPAN>

Here are the results I did check are spot on!</SPAN></SPAN>

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
1	Values	1	2	X
2	Teams	3
3
4
5		Combination	Team 1	Team 2	Team 3	Closest to:		Pass 1	Pass 5	Pass 11	Pass 13	Pass 27	Check >2		Combination	Team 1	Team 2	Team 3		Check 1	Check 5	Check 11	Check 13	Check 27
6		1	1	1	1	1		3					3		1	1	1	1		3	1	1	1	0
7		2	1	1	2	1		2	2	2			2		5	1	2	2		2	2	2	0	0
8		3	1	1	X	1		2					2		11	2	1	2		2	1	1	0	1
9		4	1	2	1	1		2	2		2		2		13	2	2	1		2	2	0	2	0
10		5	1	2	2	5			3				3		27	X	X	X		1	3	1	1	0
11		6	1	2	X	5			2				2							1	2	0	1	1
12		7	1	X	1	1		2					2							2	1	0	1	1
13		8	1	X	2	5			2				2							1	2	1	0	1
14		9	1	X	X	27						2	2							1	1	0	0	2
15		10	2	1	1	1		2		2	2		2							2	0	2	2	0
16		11	2	1	2	11				3			3							1	1	3	1	0
17		12	2	1	X	11				2			2							1	0	2	1	1
18		13	2	2	1	13					3		3							1	1	1	3	0
19		14	2	2	2	5			2	2	2		2							0	2	2	2	0
20		15	2	2	X	13					2		2							0	1	1	2	1
21		16	2	X	1	13					2		2							1	0	1	2	1
22		17	2	X	2	11				2			2							0	1	2	1	1
23		18	2	X	X	27						2	2							0	0	1	1	2
24		19	X	1	1	1		2					2							2	0	1	1	1
25		20	X	1	2	11				2			2							1	1	2	0	1
26		21	X	1	X	27						2	2							1	0	1	0	2
27		22	X	2	1	13					2		2							1	1	0	2	1
28		23	X	2	2	5			2				2							0	2	1	1	1
29		24	X	2	X	27						2	2							0	1	0	1	2
30		25	X	X	1	27						2	2							1	0	0	1	2
31		26	X	X	2	27						2	2							0	1	1	0	2
32		27	X	X	X	27						3	3							0	0	0	0	3
33
34
Sheet6

Cell Formulas
Range	Formula
H6	=IF(T6>1,T6,"")
I6	=IF(U6>1,U6,"")
J6	=IF(V6>1,V6,"")
K6	=IF(W6>1,W6,"")
L6	=IF(X6>1,X6,"")
M6	=MAX(H6:L6)
T6	=SUMPRODUCT(--($C6:$E6=$P$6:$R$6))
U6	=SUMPRODUCT(--($C6:$E6=$P$7:$R$7))
V6	=SUMPRODUCT(--($C6:$E6=$P$8:$R$8))
W6	=SUMPRODUCT(--($C6:$E6=$P$9:$R$9))
X6	=SUMPRODUCT(--($C6:$E6=$P$10:$R$10))

Thank you for your assistance may be now looking to your idea I got some clue will try if got some positive results with 4 teams will show here... </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

From a mathematical point of view, this is really interesting. You only need 5 sets instead of the 6 you have. Looking good so far, but here's where the vast number of combinations kicks in. The macro ran for hours without finding a solution. I do know that it would require at least 8 sets to make it work. I'm trying to find a way to use various symmetries to reduce the number of calculations. I'll let you know if I figure it out.

Eric, really your viewpoint has been great to discover a way out of this complicate task, here is the clue I got form you solution it could be VBA solution but don't know how to create also weather it is correct or not. </SPAN></SPAN>
With the 3 teams you got 5 results. Based on those results I made 3 columns to count the 1, X and 2 and then filter as per column K "filter criteria" and found the column as you discovered. </SPAN></SPAN>

Here are the results...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Values	1	2	X
2	Teams	3
3
4											Filter Criteria
5		Combination	Team 1	Team 2	Team 3		1	X	2		1-X-2	Combi n	Team 1	Team 2	Team 3
6		1	1	1	1		3	0	0		3-0-0	1	1	1	1
7		2	1	1	2		2	0	2		1-0-2	2	1	2	2
8		3	1	1	X		2	1	1		1-0-2	3	2	1	2
9		4	1	2	1		2	0	1		1-0-2	4	2	2	1
10		5	1	2	2		1	0	1		0-3-0	5	X	X	X
11		6	1	2	X		1	1	0
12		7	1	X	1		2	1	1
13		8	1	X	2		1	1	1
14		9	1	X	X		1	2	0
15		10	2	1	1		2	0	1
16		11	2	1	2		1	0	1
17		12	2	1	X		1	1	0
18		13	2	2	1		1	0	0
19		14	2	2	2		0	0	1
20		15	2	2	X		0	1	0
21		16	2	X	1		1	1	0
22		17	2	X	2		0	1	1
23		18	2	X	X		0	2	0
24		19	X	1	1		2	0	1
25		20	X	1	2		1	0	1
26		21	X	1	X		1	1	0
27		22	X	2	1		1	0	0
28		23	X	2	2		0	0	1
29		24	X	2	X		0	1	0
30		25	X	X	1		1	1	0
31		26	X	X	2		0	1	1
32		27	X	X	X		0	2	0
33
34
Sheet8

Cell Formulas
Range	Formula
G6	=COUNTIF(C6:E6,1)
H6	=COUNTIF(D6:F6,"X")
I6	=COUNTIF(E6:G6,2)

And here are using the same criteria 8-result for 4 teams...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4													Filter Criteria
5			Combi n	Team1	Team2	Team3	Team4		1	X	2		1-X-2	Combination	Team 1	Team 2	Team 3	Team 4
6			1	1	1	1	1		4	0	0		4-0-0	1	1	1	1	1
7			2	1	1	1	X		3	1	0		0-4-0	2	X	X	X	X
8			3	1	1	1	2		3	0	1		2-0-2	3	1	1	2	2
9			4	1	1	X	1		3	1	0		2-0-2	4	1	2	1	2
10			5	1	1	X	X		2	2	0		2-0-2	5	1	2	2	1
11			6	1	1	X	2		2	1	1		2-0-2	6	2	1	1	2
12			7	1	1	2	1		3	0	1		2-0-2	7	2	1	2	1
13			8	1	1	2	X		2	1	1		2-0-2	8	2	2	1	1
14			9	1	1	2	2		2	0	2
15			10	1	X	1	1		3	1	0
16			11	1	X	1	X		2	2	0
17			12	1	X	1	2		2	1	1
18			13	1	X	X	1		2	2	0
19			14	1	X	X	X		1	3	0
20			15	1	X	X	2		1	2	1
21			16	1	X	2	1		2	1	1
22			17	1	X	2	X		1	2	1
23			18	1	X	2	2		1	1	2
24			19	1	2	1	1		3	0	1
25			20	1	2	1	X		2	1	1
26			21	1	2	1	2		2	0	2
27			22	1	2	X	1		2	1	1
28			23	1	2	X	X		1	2	1
29			24	1	2	X	2		1	1	2
30			25	1	2	2	1		2	0	2
31			26	1	2	2	X		1	1	2
32			27	1	2	2	2		1	0	3
33			28	X	1	1	1		3	1	0
34			29	X	1	1	X		2	2	0
35			30	X	1	1	2		2	1	1
36			31	X	1	X	1		2	2	0
37			32	X	1	X	X		1	3	0
38			33	X	1	X	2		1	2	1
39			34	X	1	2	1		2	1	1
40			35	X	1	2	X		1	2	1
41			36	X	1	2	2		1	1	2
42			37	X	X	1	1		2	2	0
43			38	X	X	1	X		1	3	0
44			39	X	X	1	2		1	2	1
45			40	X	X	X	1		1	3	0
46			41	X	X	X	X		0	4	0
47			42	X	X	X	2		0	3	1
48			43	X	X	2	1		1	2	1
49			44	X	X	2	X		0	3	1
50			45	X	X	2	2		0	2	2
51			46	X	2	1	1		2	1	1
52			47	X	2	1	X		1	2	1
53			48	X	2	1	2		1	1	2
54			49	X	2	X	1		1	2	1
55			50	X	2	X	X		0	3	1
56			51	X	2	X	2		0	2	2
57			52	X	2	2	1		1	1	2
58			53	X	2	2	X		0	2	2
59			54	X	2	2	2		0	1	3
60			55	2	1	1	1		3	0	1
61			56	2	1	1	X		2	1	1
62			57	2	1	1	2		2	0	2
63			58	2	1	X	1		2	1	1
64			59	2	1	X	X		1	2	1
65			60	2	1	X	2		1	1	2
66			61	2	1	2	1		2	0	2
67			62	2	1	2	X		1	1	2
68			63	2	1	2	2		1	0	3
69			64	2	X	1	1		2	1	1
70			65	2	X	1	X		1	2	1
71			66	2	X	1	2		1	1	2
72			67	2	X	X	1		1	2	1
73			68	2	X	X	X		0	3	1
74			69	2	X	X	2		0	2	2
75			70	2	X	2	1		1	1	2
76			71	2	X	2	X		0	2	2
77			72	2	X	2	2		0	1	3
78			73	2	2	1	1		2	0	2
79			74	2	2	1	X		1	1	2
80			75	2	2	1	2		1	0	3
81			76	2	2	X	1		1	1	2
82			77	2	2	X	X		0	2	2
83			78	2	2	X	2		0	1	3
84			79	2	2	2	1		1	0	3
85			80	2	2	2	X		0	1	3
86			81	2	2	2	2		0	0	4
87
88
sheet9

Cell Formulas
Range	Formula
I6	=COUNTIF(D6:G6,1)
J6	=COUNTIF(D6:G6,"X")
K6	=COUNTIF(D6:G6,2)

Please could you check it?</SPAN></SPAN>

Thank you for your help and guidance </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

Eric, really your viewpoint has been great to discover a way out of this complicate task, here is the clue I got form you solution it could be VBA solution but don't know how to create also weather it is correct or not. </SPAN></SPAN>Kind Regards,</SPAN></SPAN>Kishan </SPAN></SPAN>

Hi Eric, I were checking it would be 14 not 8 as shown below with 8 there are some with 2 pass the goal is find with minimum 3 within the 4 teams...</SPAN></SPAN>

Correction #post8....</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4			Filter Criteria For 1	Filter Criteria For X	Filter Criteria For 2
5			1	X	2		Combination	Team 1	Team 2	Team 3	Team 4
6			4	0	0		1	1	1	1	1
7			2	1	1		2	1	1	2	X
8			1	1	2		3	1	X	2	2
9			1	2	1		4	1	2	X	X
10			3	1	0		5	X	1	1	1
11			2	1	1		6	X	1	2	1
12			0	4	0		7	X	X	X	X
13			1	1	2		8	X	2	1	2
14			1	2	1		9	X	2	X	1
15			0	2	2		10	X	2	2	X
16			1	1	2		11	2	1	X	2
17			1	2	1		12	2	X	1	X
18			1	2	1		13	2	X	X	1
19			1	0	3		14	2	2	2	1
20
Sheet10

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hi Eric, I were checking it would be 14 not 8 as shown below with 8 there are some with 2 pass the goal is find with minimum 3 within the 4 teams...</SPAN></SPAN>Kind Regards,</SPAN></SPAN>Kishan</SPAN></SPAN>

Hi Eric, sorry for the inconvenience may be following is bit better I guess but do not find the accurate method... </SPAN></SPAN>

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	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1
2
3
4																				Filter 1	Filter X	Filter 2
5			Combi n	Team1	Team2	Team3	Team4		Pass 1	Pass 2	Pass 3	Pass 4	Pass 5	Pass 6	Pass 7	Pass 8	Pass 9	Check >2		1	X	2		1	X	2		Combination	Team 1	Team 2	Team 3	Team 4		Check 1	Check 2	Check 3	Check 4	Check 5	Check 6	Check 7	Check 8	Check 9
6			1	1	1	1	1		4									4		4	0	0		4	0	0		1	1	1	1	1		4	1	1	1	1	1	1	1	1
7			2	1	1	1	X		3	2				2	2			3		1	3	0		3	1	0		2	1	X	X	X		3	2	1	1	0	2	2	1	0
8			3	1	1	1	2		3		2	2				2		3		1	0	3		3	0	1		3	1	2	2	2		3	1	2	2	0	1	1	2	0
9			4	1	1	X	1		3	2		2					2	3		1	2	1		3	1	0		4	X	1	X	2		3	2	1	2	1	0	1	0	2
10			5	1	1	X	X		2	3		2			2			3		1	2	1		2	2	0		5	X	X	2	1		2	3	1	2	0	1	2	0	1
11			6	1	1	X	2		2	2	2	3						3		1	2	1		2	1	1		6	X	2	1	X		2	2	2	3	0	0	1	1	1
12			7	1	1	2	1		3		2		2		2			3		1	1	2		3	0	1		7	2	1	2	X		3	1	2	1	2	0	2	0	1
13			8	1	1	2	X		2	2	2				3			3		1	1	2		2	1	1		8	2	X	1	2		2	2	2	1	1	1	3	0	0
14			9	1	1	2	2		2		3	2			2			3		1	1	2		2	0	2		9	2	2	X	1		2	1	3	2	1	0	2	1	0
15			10	1	X	1	1		3	2			2			2		3						3	1	0								3	2	1	0	2	1	0	2	1
16			11	1	X	1	X		2	3				2		2		3						2	2	0								2	3	1	0	1	2	1	2	0
17			12	1	X	1	2		2	2	2					3		3						2	1	1								2	2	2	1	1	1	0	3	0
18			13	1	X	X	1		2	3			2				2	3						2	2	0								2	3	1	1	2	0	0	1	2
19			14	1	X	X	X			4								4						1	3	0								1	4	1	1	1	1	1	1	1
20			15	1	X	X	2			3	2	2				2		3						1	2	1								1	3	2	2	1	0	0	2	1
21			16	1	X	2	1		2	2	2		3					3						2	1	1								2	2	2	0	3	0	1	1	1
22			17	1	X	2	X			3	2		2		2			3						1	2	1								1	3	2	0	2	1	2	1	0
23			18	1	X	2	2			2	3		2			2		3						1	1	2								1	2	3	1	2	0	1	2	0
24			19	1	2	1	1		3		2			2			2	3						3	0	1								3	1	2	0	1	2	0	1	2
25			20	1	2	1	X		2	2	2			3				3						2	1	1								2	2	2	0	0	3	1	1	1
26			21	1	2	1	2		2		3			2		2		3						2	0	2								2	1	3	1	0	2	0	2	1
27			22	1	2	X	1		2	2	2						3	3						2	1	1								2	2	2	1	1	1	0	0	3
28			23	1	2	X	X			3	2			2			2	3						1	2	1								1	3	2	1	0	2	1	0	2
29			24	1	2	X	2			2	3	2					2	3						1	1	2								1	2	3	2	0	1	0	1	2
30			25	1	2	2	1		2		3		2				2	3						2	0	2								2	1	3	0	2	1	1	0	2
31			26	1	2	2	X			2	3			2	2			3						1	1	2								1	2	3	0	1	2	2	0	1
32			27	1	2	2	2				4							4						1	0	3								1	1	4	1	1	1	1	1	1
33			28	X	1	1	1		3			2	2	2				3						3	1	0								3	0	0	2	2	2	1	1	1
34			29	X	1	1	X		2			2		3	2			3						2	2	0								2	1	0	2	1	3	2	1	0
35			30	X	1	1	2		2			3		2		2		3						2	1	1								2	0	1	3	1	2	1	2	0
36			31	X	1	X	1		2			3	2				2	3						2	2	0								2	1	0	3	2	1	1	0	2
37			32	X	1	X	X			2		3		2	2			3						1	3	0								1	2	0	3	1	2	2	0	1
38			33	X	1	X	2					4						4						1	2	1								1	1	1	4	1	1	1	1	1
39			34	X	1	2	1		2			2	3		2			3						2	1	1								2	0	1	2	3	1	2	0	1
40			35	X	1	2	X					2	2	2	3			3						1	2	1								1	1	1	2	2	2	3	0	0
41			36	X	1	2	2				2	3	2		2			3						1	1	2								1	0	2	3	2	1	2	1	0
42			37	X	X	1	1		2				3	2		2		3						2	2	0								2	1	0	1	3	2	0	2	1
43			38	X	X	1	X			2			2	3		2		3						1	3	0								1	2	0	1	2	3	1	2	0
44			39	X	X	1	2					2	2	2		3		3						1	2	1								1	1	1	2	2	2	0	3	0
45			40	X	X	X	1			2		2	3				2	3						1	3	0								1	2	0	2	3	1	0	1	2
46			41	X	X	X	X			3		2	2	2				3						0	4	0								0	3	0	2	2	2	1	1	1
47			42	X	X	X	2			2		3	2			2		3						0	3	1								0	2	1	3	2	1	0	2	1
48			43	X	X	2	1						4					4						1	2	1								1	1	1	1	4	1	1	1	1
49			44	X	X	2	X			2			3	2	2			3						0	3	1								0	2	1	1	3	2	2	1	0
50			45	X	X	2	2				2	2	3			2		3						0	2	2								0	1	2	2	3	1	1	2	0
51			46	X	2	1	1		2				2	3			2	3						2	1	1								2	0	1	1	2	3	0	1	2
52			47	X	2	1	X							4				4						1	2	1								1	1	1	1	1	4	1	1	1
53			48	X	2	1	2				2	2		3		2		3						1	1	2								1	0	2	2	1	3	0	2	1
54			49	X	2	X	1					2	2	2			3	3						1	2	1								1	1	1	2	2	2	0	0	3
55			50	X	2	X	X			2		2		3			2	3						0	3	1								0	2	1	2	1	3	1	0	2
56			51	X	2	X	2				2	3		2			2	3						0	2	2								0	1	2	3	1	2	0	1	2
57			52	X	2	2	1				2		3	2			2	3						1	1	2								1	0	2	1	3	2	1	0	2
58			53	X	2	2	X				2		2	3	2			3						0	2	2								0	1	2	1	2	3	2	0	1
59			54	X	2	2	2				3	2	2	2				3						0	1	3								0	0	3	2	2	2	1	1	1
60			55	2	1	1	1		3						2	2	2	3						3	0	1								3	0	0	1	1	1	2	2	2
61			56	2	1	1	X		2					2	3	2		3						2	1	1								2	1	0	1	0	2	3	2	1
62			57	2	1	1	2		2			2			2	3		3						2	0	2								2	0	1	2	0	1	2	3	1
63			58	2	1	X	1		2			2			2		3	3						2	1	1								2	1	0	2	1	0	2	1	3
64			59	2	1	X	X			2		2			3		2	3						1	2	1								1	2	0	2	0	1	3	1	2
65			60	2	1	X	2					3			2	2	2	3						1	1	2								1	1	1	3	0	0	2	2	2
66			61	2	1	2	1		2				2		3		2	3						2	0	2								2	0	1	1	2	0	3	1	2
67			62	2	1	2	X								4			4						1	1	2								1	1	1	1	1	1	4	1	1
68			63	2	1	2	2				2	2			3	2		3						1	0	3								1	0	2	2	1	0	3	2	1
69			64	2	X	1	1		2				2			3	2	3						2	1	1								2	1	0	0	2	1	1	3	2
70			65	2	X	1	X			2				2	2	3		3						1	2	1								1	2	0	0	1	2	2	3	1
71			66	2	X	1	2									4		4						1	1	2								1	1	1	1	1	1	1	4	1
72			67	2	X	X	1			2			2			2	3	3						1	2	1								1	2	0	1	2	0	1	2	3
73			68	2	X	X	X			3					2	2	2	3						0	3	1								0	3	0	1	1	1	2	2	2
74			69	2	X	X	2			2		2				3	2	3						0	2	2								0	2	1	2	1	0	1	3	2
75			70	2	X	2	1						3		2	2	2	3						1	1	2								1	1	1	0	3	0	2	2	2
76			71	2	X	2	X			2			2		3	2		3						0	2	2								0	2	1	0	2	1	3	2	1
77			72	2	X	2	2				2		2		2	3		3						0	1	3								0	1	2	1	2	0	2	3	1
78			73	2	2	1	1		2					2		2	3	3						2	0	2								2	0	1	0	1	2	1	2	3
79			74	2	2	1	X							3	2	2	2	3						1	1	2								1	1	1	0	0	3	2	2	2
80			75	2	2	1	2				2			2		3	2	3						1	0	3								1	0	2	1	0	2	1	3	2
81			76	2	2	X	1										4	4						1	1	2								1	1	1	1	1	1	1	1	4
82			77	2	2	X	X			2				2	2		3	3						0	2	2								0	2	1	1	0	2	2	1	3
83			78	2	2	X	2				2	2				2	3	3						0	1	3								0	1	2	2	0	1	1	2	3
84			79	2	2	2	1				2		2		2		3	3						1	0	3								1	0	2	0	2	1	2	1	3
85			80	2	2	2	X				2			2	3		2	3						0	1	3								0	1	2	0	1	2	3	1	2
86			81	2	2	2	2				3				2	2	2	3						0	0	4								0	0	3	1	1	1	2	2	2
87
Sheet11

Cell Formulas
Range	Formula
I6	=IF(AH6>1,AH6,"")
J6	=IF(AI6>1,AI6,"")
K6	=IF(AJ6>1,AJ6,"")
L6	=IF(AK6>1,AK6,"")
M6	=IF(AL6>1,AL6,"")
N6	=IF(AM6>1,AM6,"")
O6	=IF(AN6>1,AN6,"")
P6	=IF(AO6>1,AO6,"")
Q6	=IF(AP6>1,AP6,"")
R6	=MAX(I6:Q6)
T6	=COUNTIF(AC6:AF6,1)
U6	=COUNTIF(AC6:AF6,"X")
V6	=COUNTIF(AC6:AF6,2)
X6	=COUNTIF(D6:G6,1)
Y6	=COUNTIF(D6:G6,"X")
Z6	=COUNTIF(D6:G6,2)
AH6	=SUMPRODUCT(--($D6:$G6=$AC$6:$AF$6))
AI6	=SUMPRODUCT(--($D6:$G6=$AC$7:$AF$7))
AJ6	=SUMPRODUCT(--($D6:$G6=$AC$8:$AF$8))
AK6	=SUMPRODUCT(--($D6:$G6=$AC$9:$AF$9))
AL6	=SUMPRODUCT(--($D6:$G6=$AC$10:$AF$10))
AM6	=SUMPRODUCT(--($D6:$G6=$AC$11:$AF$11))
AN6	=SUMPRODUCT(--($D6:$G6=$AC$12:$AF$12))
AO6	=SUMPRODUCT(--($D6:$G6=$AC$13:$AF$13))
AP6	=SUMPRODUCT(--($D6:$G6=$AC$14:$AF$14))

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>