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

 

[Vogateer]

It sounds like you're trying to understand the SUMPRODUCT formula. I'll try to explain what's going on with one of them, and I think the rest of the formula will come together for you after that.

This is used twice, and only shown if it's greater than 2:

=SUMPRODUCT(--($C6:$F6=TRANSPOSE(I$1:I$4))

Transpose just takes the horizontal I1:I4 and makes it vertical, like the C6:F6 is vertical.
Now that they're both vertical, the next part of the formula gives you a series of True or False answers, so
( C6:F6 = I1:I4 ) --> ( C6=I1, D6=I2, E6=I3, F6=I4) --> (TRUE, TRUE, TRUE, TRUE)

Now you want to add up which ones are true, which is where the "double-unary" comes into play. When you apply math to a TRUE or FALSE answer in Excel, Excel makes TRUE = 1, and FALSE = 0. The double negative (--) just turns them to negative numbers and back to positive numbers again. So now:

--(TRUE, TRUE, TRUE, TRUE) --> (1, 1, 1, 1)

That gets fed into SUMPRODUCT, which just adds those numbers together to make 4.

The rest of the formula is just making sure it's greater than 2 before actually showing it.

 

[motilulla]

It sounds like you're trying to understand the SUMPRODUCT formula. I'll try to explain what's going on with one of them, and I think the rest of the formula will come together for you after that.

This is used twice, and only shown if it's greater than 2:

=SUMPRODUCT(--($C6:$F6=TRANSPOSE(I$1:I$4))

Transpose just takes the horizontal I1:I4 and makes it vertical, like the C6:F6 is vertical.
Now that they're both vertical, the next part of the formula gives you a series of True or False answers, so
( C6:F6 = I1:I4 ) --> ( C6=I1, D6=I2, E6=I3, F6=I4) --> (TRUE, TRUE, TRUE, TRUE)

Now you want to add up which ones are true, which is where the "double-unary" comes into play. When you apply math to a TRUE or FALSE answer in Excel, Excel makes TRUE = 1, and FALSE = 0. The double negative (--) just turns them to negative numbers and back to positive numbers again. So now:

--(TRUE, TRUE, TRUE, TRUE) --> (1, 1, 1, 1)

That gets fed into SUMPRODUCT, which just adds those numbers together to make 4.

The rest of the formula is just making sure it's greater than 2 before actually showing it.

Hello Vogateer, thank you for explaining SUMPRODUCT formula in detail.

But I need if you look in the column C, D, E and F there are 81 combinations vertically with set of 4. And In horizontal range I1:Q4 there are 9 combinations with set of 4…is it correct?

I want to know how these 8 horizontal range I1:Q4 combinations has extracted from 81 which has minimum match with 3 and 4…..you think in other way I don’t have these 9 horizontal range I1:Q4 combinations and I want to pull out from 81…how it could be done?

I am sure they are not selected randomly is it correct?

Good Luck

Regards,
Moti

 

[Vogateer]

I think I am confused about what you want. Are you saying that you want to come up with the I6:Q86 numbers without using the I1:Q4 numbers above? If not, maybe you could help me understand what numbers and cell addresses you would have to start, then what the final result might look like.

 

[motilulla]

I think I am confused about what you want. Are you saying that you want to come up with the I6:Q86 numbers without using the I1:Q4 numbers above? If not, maybe you could help me understand what numbers and cell addresses you would have to start, then what the final result might look like.

Have another look as I put 9 set of 4 in Range I2:Q5 that has created confusion…

Here is a revised question suppose now we have 81 set only in columns “C:F” of 4 in 4…as 0000, 0001,0002…so on, and nothing in the Range I2:Q5…is it clear till here.

Now I want out of 81 extract only those unique set which give me matches of 3 and 4 with each of 81 sets as shown in the range I7:Q87. how could this be find and done?

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

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

Good Luck

Regards,
Moti

 

[bebo021999]

So, the final step is to find "4" in the range I7:Q87. If found, then copy the 4 cells to the left into the 4 cells above, right?
For example, Set 2, columns J, and the number 4 is found in row 21, we would copy the range C21:F21 (0,1,1,2) into the range J2:J5.
Then, number "3" will be ignored.
Is that right?

 

[motilulla]

So, the final step is to find "4" in the range I7:Q87. If found, then copy the 4 cells to the left into the 4 cells above, right?
For example, Set 2, columns J, and the number 4 is found in row 21, we would copy the range C21:F21 (0,1,1,2) into the range J2:J5.
Then, number "3" will be ignored.
Is that right?

Hello bebo021999, thank you for your interest and asking a question.

Now here is a final layout where I got only 81 set of 4 numbers in the each row and in the range I7:Q87….if I match 81 against 81 we defiantly will find max 4 matches in the each row is it correct?

What is here challenge find among 81 set only those unique set which has minimum match of 3 at least for sure with each set once as per opening post results, if match are below the 3 will be ignored or if more in each row matches also will be ignored….

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

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

As massage exceed…continue into next post…

 

[motilulla]

Now we selected one example if we place formula in range I7:Q87 and select 9 random from range C7:Q87… which are highlighted in yellow and paste them in to I2:Q5…look at the result in range I7:Q87…rows I9:Q9, I18:Q18 and more are highlighted in green remain empty. And also find multiple matches in some rows which are highlighted in blue…so far it is not valid selection….look at the example below…..

This is interesting to find unique sets which must have only max 1 match of 3 or 4 against each row are in the range C7:F87…. As you can see in #post1 this is really bit tricky and difficult.

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

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

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

Good Luck

Regards,
Moti

 

[Vogateer]

I believe I understand now, you want to generate 9 numbers in I2:Q5, but do so in a way that all 81 numbers in C7:F87 have at least a 3 number match with one of those 9 numbers. Is this for a practical problem you're trying to solve, or are you just interested in algorithms and formulas for the fun of it?

I'm sorry but I don't think I'm going to be able to help here.

 

[motilulla]

I believe I understand now, you want to generate 9 numbers in I2:Q5, but do so in a way that all 81 numbers in C7:F87 have at least a 3 number match with one of those 9 numbers. Is this for a practical problem you're trying to solve, or are you just interested in algorithms and formulas for the fun of it?

I'm sorry but I don't think I'm going to be able to help here.

Hello Vogateer, you got exactly correct my view point, in brief you have explained perfectly which I were not able to explain, this will help programmers to understand better.

Really when I got this I tried to solve but I could not how intelligently someone has come up with only 9 that have at least 3 matches with whole sequence I like excel too much and I am curious to have some VBA or Formula to get these strange results.

When I saw COMBIN(81,9) = 260.887.834.350 set of 9, do you think the person who has come up with this solution checking all these one by one so much combinations?

I also find it is complicate there could be some complex algorithms to find them.

I really appreciate your interest and nice reply which is easy to understand view point for everyone.

I hope someone defiantly will solve this complex task.

Good Luck

Regards,
Moti