Need formula help (multiple results check?)

[Kishan]

Hi,

Formula in H2 =MAX(MMULT(COUNTIF(C3:G3,L$3:P$5),{1;1;1;1;1})) copied to down, check multiple result are in column K:O (In this example there are 3 results) find matches with data are in column C:G....is working perfect.

Here below is an example sheet.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5
2	1	1	2	3	4	5		5		1	1	2	3	4	5
3	2	1	2	3	4	6		4		2	1	2	3	6	7
4	3	1	2	3	4	7		4		3	1	4	5	6	7
5	4	1	2	3	5	6		4
6	5	1	2	3	5	7		4
7	6	1	2	3	6	7		5
8	7	1	2	4	5	6		4
9	8	1	2	4	5	7		4
10	9	1	2	4	6	7		4
11	10	1	2	5	6	7		4
12	11	1	3	4	5	6		4
13	12	1	3	4	5	7		4
14	13	1	3	4	6	7		4
15	14	1	3	5	6	7		4
16	15	1	4	5	6	7		5
17	16	2	3	4	5	6		4
18	17	2	3	4	5	7		4
19	18	2	3	4	6	7		4
20	19	2	3	5	6	7		4
21	20	2	4	5	6	7		4
22	21	3	4	5	6	7		4

When I change the data as shown below the above formula give me the result as shown in column H2 which is not correct (correct result must be as shown in the column Q) please can someone take a look a provide me correct formula which can work with this data situation.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Combi	n1	n2	n3	n4	n5		Match		Combi	n1	n2	n3	n4	n5		Correct Match
2	1	X	2	X	2	1		9		1	X	2	X	2	1		5
3	2	1	2	2	1	2		11		2	1	1	X	X	X		3
4	3	2	2	1	X	1		10		3	1	1	1	1	2		2
5	4	X	1	X	2	1		10									4
6	5	2	1	2	X	2		9									2
7	6	1	2	2	1	1		14									2
8	7	1	1	2	2	2		11									3
9	8	1	1	1	1	1		20									4
10	9	1	1	X	X	X		13									5
11	10	X	2	2	2	X		10									3
12	11	1	2	1	1	1		17									3
13	12	1	1	X	2	X		10									4
14	13	X	2	X	2	X		10									4
15	14	X	X	2	2	2		10									2
16	15	X	X	X	X	1		14									3
17	16	1	1	2	1	X		13									3
18	17	2	1	2	1	X		10									2
19	18	1	1	1	1	2		17									5
20	19	1	1	1	2	2		14									4
21	20	X	1	2	1	X		10									2
22	21	2	1	2	1	2		11									3

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi, actually there is my error in the post#1 this is actually picture I am checking example 3 result of column O2:S4 one by one with my data in column B2:F22 in Column H, I, J...

Formula H2 =SUMPRODUCT(--($O$2:$S$2=$B2:$F2)) copied to down
Formula I2 =SUMPRODUCT(--($O$2:$S$2=$B3:$F3)) copied to down
Formula J2 =SUMPRODUCT(--($O$2:$S$2=$B4:$F4)) copied to down

Formula L2 =MAX(H2:J2)

I need single formula in column L which gives me the desire result as shows? I am not sure is it possible.

Example sheet

Combi	n1	n2	n3	n4	n5		Result 1	Result 2	Result 3		Max Match		Result	n1	n2	n3	n4	n5
1	X	2	X	2	1		5	1	0		5		1	X	2	X	2	1
2	1	2	2	1	2		1	1	3		3		2	1	1	X	X	X
3	2	2	1	X	1		2	1	1		2		3	1	1	1	1	2
4	X	1	X	2	1		4	2	1		4
5	2	1	2	X	2		0	2	2		2
6	1	2	2	1	1		2	1	2		2
7	1	1	2	2	2		1	2	3		3
8	1	1	1	1	1		1	2	4		4
9	1	1	X	X	X		1	5	2		5
10	X	2	2	2	X		3	1	0		3
11	1	2	1	1	1		2	1	3		3
12	1	1	X	2	X		2	4	2		4
13	X	2	X	2	X		4	2	0		4
14	X	X	2	2	2		2	0	1		2
15	X	X	X	X	1		3	2	0		3
16	1	1	2	1	X		0	3	3		3
17	2	1	2	1	X		0	2	2		2
18	1	1	1	1	2		0	2	5		5
19	1	1	1	2	2		1	2	4		4
20	X	1	2	1	X		1	2	2		2
21	2	1	2	1	2		0	1	3		3

Regards,
Kishan

 

[Kishan]

May be if formula is not possible VBA solution will be ok for me

 

[Kishan]

BUMP

 

[Kishan]

Really I need help to complete my project
Please take a look also link below
Thread 'Find lottery 5_50 3rd price combinations from 12 numbers' Find lottery 5_50 3rd price combinations from 12 numbers
Thank you

 

[Kishan]

BUMP

 

[Kishan]

Please is there any way to find a solution for post#2...single formua in column "L" or if there is no formula please help with VBA. Thank you

Regards,
Kishan

 

[Kishan]

BUMP