Extract that set which passes minimum 3 matches

[motilulla]

Using Excel 2010

Hello,

I got something in net I need an answer my head is broken how it is possible. There is some maths that is far beyond my knowledge.

There are 81 set of 4 numbers in the range C6:F86, out of these 81 sets there are only 9 unique set in the range I1:Q4, if I match these unique 9 with 81 sets there is 100 match of minimum 3 and max 4 match within these 9 sets.

To compare match there is formula in cells I6:Q6 which can be copied to down Q86.

Here is my question how and which formula or VBA has applied to bring these 9 unique those have match minimum 3 and 4 with all 81 combinations. Please help with this odd query.

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

Cell Formulas
Range	Formula
I6:Q6	I6	=IF(SUMPRODUCT(--($C6:$F6=TRANSPOSE(I$1:I$4)))>2,SUMPRODUCT(--($C6:$F6=TRANSPOSE(I$1:I$4))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6:Q86	Cell Value	=4	text	YES

Thank you.

Regards,
Moti

 

[motilulla]

Hello, in other word it can be said find out of 81 only those small sets which can guarantee minimum of 3 with all 81 sets. Please any VBA or formula help to solve this unusual task?

Thank you.

Regards,
Moti

 

[motilulla]

BUMP

 

[motilulla]

Hello, here is my explanation how I have found 9 set which has match minimum 3 (with each 81 sets)

Please help with VBA to find these matches 3 9 sets with all 81 sets ...

Match Minimum 3.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1									Set1	Set2	Set3	Set4	Set5	Set6	Set7	Set8	Set9		Count 2
2									0	0	0	1	1	1	2	2	2		0	<-----	Formula Count If 2 In range S7:S87
3									0	1	2	0	1	2	0	1	2
4	9	81							0	1	2	1	2	0	2	0	1		81	<-----	Total 81 match 1 per row
5	Selected	TT:Sets							0	2	1	1	0	2	2	1	0		Count 1
6
7	Set1	Set1	0	0	0	0			4										1	<-----	Formula Count Match only 1 In the Row
8		Set2	0	0	0	1			3										1
9		Set3	0	0	0	2			3										1
10		Set4	0	0	1	0			3										1		Thread has had 261 views yet nobody has offered anything. I assume the people didn't understand my explanation I guess this needs better explanation. Sorry for the inconvenience.
11		Set5	0	0	1	1						3							1
12		Set6	0	0	1	2				3									1
13		Set7	0	0	2	0			3										1
14		Set8	0	0	2	1					3								1
15		Set9	0	0	2	2									3				1
16		Set10	0	1	0	0			3										1
17		Set11	0	1	0	1										3			1		Since I summit the question I was trying to find the solution here is manually what I have come up…Need VBA to Find these 9 sets Automatically which has minimum 1 match per row 3 or greater.
18		Set12	0	1	0	2				3									1
19		Set13	0	1	1	0				3									1
20		Set14	0	1	1	1				3									1
21	Set2	Set15	0	1	1	2				4									1
22		Set16	0	1	2	0							3						1
23		Set17	0	1	2	1					3								1		Step-1, I placed the formula in S2 Count If Find 2 in the Range S7:S87 Step-2, Placed Formula in Range I7:Q87 Find Match >2iIn range C7:Q87
24		Set18	0	1	2	2				3									1
25		Set19	0	2	0	0			3										1
26		Set20	0	2	0	1					3								1
27		Set21	0	2	0	2								3					1		Step-3, Filled Set1 in Range I2:I5 (0 0 0 0) that has 1 Match >2 With 9 Combinations, I7, I8, I9, I10, I13, I16, I25, I34 & I62
28		Set22	0	2	1	0											3		1
29		Set23	0	2	1	1					3								1
30		Set24	0	2	1	2				3									1
31		Set25	0	2	2	0					3								1		Step-4, Placed Next Set2 in Range J2:J5 (0 0 0 1) and find in Range S2 Count = 3 I keep placing next sets till Count 2 = 0 which found set (0 1 1 2)
32	Set3	Set26	0	2	2	1					4								1
33		Set27	0	2	2	2					3								1
34		Set28	1	0	0	0			3										1
35		Set29	1	0	0	1						3							1
36		Set30	1	0	0	2								3					1		Then Continued till find all 9 out of 81 that has matched >2….1 in each row total 81 match 9 with 4 and 72 with 3 all >2
37		Set31	1	0	1	0						3							1
38	Set4	Set32	1	0	1	1						4							1
39		Set33	1	0	1	2						3							1
40		Set34	1	0	2	0							3						1
41		Set35	1	0	2	1						3							1
42		Set36	1	0	2	2									3				1
43		Set37	1	1	0	0							3						1		Please need VBA which can find these automatically Hope This Help
44		Set38	1	1	0	1										3			1
45		Set39	1	1	0	2								3					1
46		Set40	1	1	1	0							3						1
47		Set41	1	1	1	1						3							1
48		Set42	1	1	1	2				3									1
49	Set5	Set43	1	1	2	0							4						1
50		Set44	1	1	2	1							3						1
51		Set45	1	1	2	2							3						1
52		Set46	1	2	0	0								3					1
53		Set47	1	2	0	1								3					1
54	Set6	Set48	1	2	0	2								4					1
55		Set49	1	2	1	0											3		1
56		Set50	1	2	1	1						3							1
57		Set51	1	2	1	2								3					1
58		Set52	1	2	2	0							3						1
59		Set53	1	2	2	1					3								1
60		Set54	1	2	2	2								3					1
61		Set55	2	0	0	0			3										1
62		Set56	2	0	0	1										3			1
63		Set57	2	0	0	2									3				1
64		Set58	2	0	1	0											3		1
65		Set59	2	0	1	1						3							1
66		Set60	2	0	1	2									3				1
67		Set61	2	0	2	0									3				1
68		Set62	2	0	2	1									3				1
69	Set7	Set63	2	0	2	2									4				1
70		Set64	2	1	0	0										3			1
71	Set8	Set65	2	1	0	1										4			1
72		Set66	2	1	0	2										3			1
73		Set67	2	1	1	0											3		1
74		Set68	2	1	1	1										3			1
75		Set69	2	1	1	2				3									1
76		Set70	2	1	2	0							3						1
77		Set71	2	1	2	1										3			1
78		Set72	2	1	2	2									3				1
79		Set73	2	2	0	0											3		1
80		Set74	2	2	0	1										3			1
81		Set75	2	2	0	2								3					1
82	Set9	Set76	2	2	1	0											4		1
83		Set77	2	2	1	1											3		1
84		Set78	2	2	1	2											3		1
85		Set79	2	2	2	0											3		1
86		Set80	2	2	2	1					3								1
87		Set81	2	2	2	2									3				1
Sheet5

Cell Formulas
Range	Formula
S2	S2	=COUNTIF(S7:S87,2)
S4	S4	=SUM(S7:S87)
I7:Q7	I7	=IF(I$2="","",IF(SUMPRODUCT(--($C7:$F7=TRANSPOSE(I$2:I$5)))>2,SUMPRODUCT(--($C7:$F7=TRANSPOSE(I$2:I$5))),""))
S7	S7	=IF(COUNTIF(I7:Q7,">0")=0,"",COUNTIF(I7:Q7,">0"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you.

Regards,
Moti

 

[motilulla]

bump