VBA!! Multi slip lottery checker

[motilulla]

Hello,

1. Draw results in C3:G22
2. 10 play slip O3:S12 each with 5 numbers

VBA that check 10 play match 2, 3, 4 and 5 list 1^st with result C3:G3 dated 12/02/2004 and write them in I3:L3 (result shows find 2 match only in 1 play slip)
Another example draw dated 19/03/2004 find 2 match in 2 play slips and so on…

Finally I need to check 10-play slip with each draw here is the example showing results in I3:L22

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Year	FECHA	n1	n2	n3	n4	n5	EM	Match	Match	Match	Match	EM 1	Play Slip	n1	n2	n3	n4	n5
2	Year	FECHA	n1	n2	n3	n4	n5	EM	2 num	3 num	4 num	5 num	EM 1	N	n1	n2	n3	n4	n5
3	2004	13/02/2004	16	29	32	36	41		1					Play Slip 1	4	31	32	34	41
4	2004	20/02/2004	7	13	39	47	50		1					Play Slip 2	12	27	46	48	49
5	2004	27/02/2004	14	18	19	31	37							Play Slip 3	9	29	37	45	47
6	2004	05/03/2004	4	7	33	37	39							Play Slip 4	2	11	15	19	26
7	2004	12/03/2004	15	24	28	44	47							Play Slip 5	1	7	18	22	25
8	2004	19/03/2004	33	36	37	42	45		2					Play Slip 6	8	10	13	14	39
9	2004	26/03/2004	3	4	10	23	43							Play Slip 7	16	33	40	43	44
10	2004	02/04/2004	4	12	24	27	36		2					Play Slip 8	17	21	24	36	42
11	2004	09/04/2004	1	4	10	19	23							Play Slip 9	3	5	6	20	35
12	2004	16/04/2004	14	15	28	35	40							Play Slip 10	23	28	30	38	50
13	2004	23/04/2004	6	10	21	45	49
14	2004	30/04/2004	5	6	16	23	27		1
15	2004	07/05/2004	15	16	21	36	38		1
16	2004	14/05/2004	1	3	21	32	39
17	2004	21/05/2004	15	29	37	39	49		1
18	2004	28/05/2004	3	5	6	41	44			1
19	2004	04/06/2004	9	29	34	45	47				1
20	2004	11/06/2004	2	7	8	10	47			1
21	2004	18/06/2004	1	7	18	22	25					1
22	2004	25/06/2004	3	21	30	34	35			1
Results Checker

Thanks In Advance
Using version 2000

Regards,
Moti

 

[motilulla]

Bump

 

[motilulla]

Searching in google and even through excel forums I do not find any multi slip lottery checker?

Do Any body have come across please help

Regards,
Moti

 

[wideboydixon]

Public Sub LotteryChecker()

Dim lastRow As Long
Dim dateLoop As Long
Dim slipLoop As Long
Dim i As Long
Dim j As Long
Dim matchCount As Long
Dim numCount(5) As Long
Dim firstDraw As Range
Dim allSlips As Range
Dim allCounts As Range

Set firstDraw = Range("$C$3")
Set allSlips = Range("$O$3:$S$12")
Set allCounts = Range("$I$3:$L$22")

lastRow = Cells(Rows.Count, firstDraw.Column).End(xlUp).Row

For dateLoop = firstDraw.Row To lastRow
    Erase numCount
    
    For slipLoop = allSlips.Row To allSlips.Row + allSlips.Rows.Count - 1
        matchCount = 0
        For i = firstDraw.Column To firstDraw.Column + 4
            For j = allSlips.Column To allSlips.Column + 4
                matchCount = matchCount + IIf(Cells(dateLoop, i).Value = Cells(slipLoop, j).Value, 1, 0)
            Next j
        Next i
        numCount(matchCount) = numCount(matchCount) + 1
    Next slipLoop
    
    For i = 2 To 5
        Cells(dateLoop, allCounts.Column + i - 2) = IIf(numCount(i) = 0, "", numCount(i))
    Next i
Next dateLoop

End Sub

WBD

 

[motilulla]

Public Sub LotteryChecker()

WBD

Spot on wideboydixon, exactly that is it I was looking for. I think this is the first multiple Lottery checker on this forum

I appreciate a lot your help my query is solved

Thank you

Regards,
Moti

 

[Kishan]

Hi, WBD I see your lottery checker is excellent!!

Can you help me I also need a lottery checker but which may work with football game under link below
http://www.mrexcel.com/forum/excel-...ombinations-results-database.html#post4562435

Thank you

Kishan

 

[Kishan]

Hi, WBD I see your lottery checker is excellent!!

Can you help me I also need a lottery checker but which may work with football game under link below
http://www.mrexcel.com/forum/excel-...ombinations-results-database.html#post4562435

Thank you

Kishan

WEB, I am highly grateful to you for solving my request so quickly.

Thank you very much!!

Regards,
Kishan

 

[B___P]

I tried to solve with formulas:

in I3 then drag till L22

=--(SUMPRODUCT(COUNTIF(C3:G3,$O$3:$S$3))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$4:$S$4))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$5:$S$5))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$6:$S$6))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$7:$S$7))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$8:$S$8))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$9:$S$9))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$10:$S$10))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$11:$S$11))=--LEFT(I$2))+--(SUMPRODUCT(COUNTIF(C3:G3,$O$12:$S$12))=--LEFT(I$2))

I don't like this kind of huge formulas, I prefer a vba solution, but I see on the forum they are used widely. Is there a way to make it smaller?