Check my played combinations with results database

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Hi,

I ask question 9th apr 2013 under this link regarding multi lottery checker
http://www.mrexcel.com/forum/excel-questions/695997-check-result-played-combinations.html

I find thread below which check multiple results I tried 5 numbers using with my data base but it does not seem to work
http://www.mrexcel.com/forum/excel-...-applications-multi-slip-lottery-checker.html

Here is my question need VBA that work with following database perhaps….

Results database B2:O13 (Shown 12 results), Total played combinations = 10 Cells W1:AJ11
I want VBA which check my all play slips with each celebrated draw and list them in cells Q2:U13 all match find >9 for example 10,11, 12, 13, 14

For example with my 10 played combinations matching with result 1 find only 1 instance with 10 and which is showing in cells Q2:U2 under 10 find 1 instance with 10 same result 2 will check all play slip and result in next row Q2:U3…same procedure will be applied with the remains

Practical example


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1ResultsP1P2P3P4P5P6P7P8P9P10P11P12P13P141011121314PlayedP1P2P3P4P5P6P7P8P9P10P11P12P13P14Match Find
21X1X111X1X1121111X1X1X1XX1X121110
32XXX1X1X1111111222222XXX21XX213
4311X121XX1X111231X111X211211XX4
54X2X112X11X1211422X11XX211XXX25
65X1X1X1211X222152X1111X2X1XX226
76X1X1X2XX11XX116X11X12122211224
87122X2X1X21XX2X7112111X111X1218
98222XX2XX21XX218111X1X1221XX1X4
109221222X221XX2291XX12111X211X26
111022X22XXXX1XXX2101X211X12X211X24
12111X1212X11212XX
131212121X2X1211X2
Football Checker


Thank you in advance
Using Excel 2000

Regards,
Kishan
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Fully commented so hopefully you can learn from it:

Code: 
Public Sub LotteryChecker()

Dim lastRow As Long
Dim resultLoop As Long
Dim slipLoop As Long
Dim i As Long
Dim j As Long
Dim matchCount As Long
Dim firstDraw As Range
Dim allSlips As Range
Dim allCounts As Range
Dim minMatch As Long

' The first row of results
Set firstDraw = Range("$B$2:$O$2")

' The first column of the play slips
Set allSlips = Range("$W$2:$W$11")

' The top left cell of where we will put the results
Set allCounts = Range("$Q$2")

' The minimum number of matches
minMatch = 10

' Find the last possible row of results
lastRow = Cells(Rows.Count, firstDraw.Column).End(xlUp).Row

' Loop through each result
For resultLoop = firstDraw.Row To lastRow
    ' Reset the array which counts how many slips we have for each number of matches
    ReDim numCount(firstDraw.Columns.Count) As Long
    
    ' Check through all the slips
    For slipLoop = allSlips.Row To allSlips.Row + allSlips.Rows.Count - 1
        ' Reset the number of matches to zero
        matchCount = 0
        
        ' Look at each result
        For i = firstDraw.Column To firstDraw.Column + firstDraw.Columns.Count - 1
            ' If the result matches the corresponding item on the play slip then add 1 to the match count
            matchCount = matchCount + IIf(Cells(resultLoop, i).Value = Cells(slipLoop, i - firstDraw.Column + allSlips.Column).Value, 1, 0)
        Next i
        
        ' Increment the number of play slips that have [matchCount] matches
        numCount(matchCount) = numCount(matchCount) + 1
    Next slipLoop
    
    ' Now we've processed all the play slips, let's show the results starting with the minimum number of matches
    For i = minMatch To firstDraw.Columns.Count
        ' Set to the number of slips that match or blank if it's zero
        Cells(resultLoop, allCounts.Column + i - minMatch) = IIf(numCount(i) = 0, "", numCount(i))
    Next i
Next resultLoop

End Sub

WBD
 
Upvote 0
wideboydixon said:
Fully commented so hopefully you can learn from it:
WBD
Click to expand...
wideboydixon, I am very grateful to you for giving a solution of my request, which I was looking since 2013.

Also I appreciate a lot to you for spending a lot of time writing comments on each line how does it works.

It is working absolutely perfect!!

I am highly obliged to you.

Regards,
Kishan :)
 
Upvote 0
No problem; it wasn't hard to adapt the previous solution to your situation. Glad it's all working for you.

WBD
 
Upvote 0
Kishan said:
Hi,

I ask question 9th apr 2013 under this link regarding multi lottery checker
http://www.mrexcel.com/forum/excel-questions/695997-check-result-played-combinations.html

I find thread below which check multiple results I tried 5 numbers using with my data base but it does not seem to work
http://www.mrexcel.com/forum/excel-...-applications-multi-slip-lottery-checker.html

Here is my question need VBA that work with following database perhaps….

Results database B2:O13 (Shown 12 results), Total played combinations = 10 Cells W1:AJ11
I want VBA which check my all play slips with each celebrated draw and list them in cells Q2:U13 all match find >9 for example 10,11, 12, 13, 14

For example with my 10 played combinations matching with result 1 find only 1 instance with 10 and which is showing in cells Q2:U2 under 10 find 1 instance with 10 same result 2 will check all play slip and result in next row Q2:U3…same procedure will be applied with the remains

Practical example

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Results[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]P4[/TD]
[TD="align: center"]P5[/TD]
[TD="align: center"]P6[/TD]
[TD="align: center"]P7[/TD]
[TD="align: center"]P8[/TD]
[TD="align: center"]P9[/TD]
[TD="align: center"]P10[/TD]
[TD="align: center"]P11[/TD]
[TD="align: center"]P12[/TD]
[TD="align: center"]P13[/TD]
[TD="align: center"]P14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]Played[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]P4[/TD]
[TD="align: center"]P5[/TD]
[TD="align: center"]P6[/TD]
[TD="align: center"]P7[/TD]
[TD="align: center"]P8[/TD]
[TD="align: center"]P9[/TD]
[TD="align: center"]P10[/TD]
[TD="align: center"]P11[/TD]
[TD="align: center"]P12[/TD]
[TD="align: center"]P13[/TD]
[TD="align: center"]P14[/TD]
[TD="align: center"]Match Find[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Football Checker​




Thank you in advance
Using Excel 2000

Regards,
Kishan
Click to expand...



Hi Kishan,

Here is another approach if you interested:


Code: 
Sub Kishan()
Dim i       As Long
Dim j       As Long
Dim k       As Long
Dim c       As Long
Dim rng     As Range

Application.ScreenUpdating = False

Set rng = Range("AK2:AK11")
j = 2: k = 2

For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    Do While j < 10
        If Cells(i, k) = Cells(j, k).Offset(, 21) Then
            c = c + 1
        End If
        If k = 15 Then
            Cells(j + 2, 37) = c
            j = j + 1: k = 1: c = 0
        End If
        k = k + 1
    Loop
j = 2
Range("Q" & i) = Application.CountIf(rng, 10): Range("R" & i) = Application.CountIf(rng, 11)
Range("S" & i) = Application.CountIf(rng, 12): Range("T" & i) = Application.CountIf(rng, 13)
Range("U" & i) = Application.CountIf(rng, 14): rng.ClearContents
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
How to check this database for duplicates?
Replies
4
Views
972
exceloony
E
B
  • Question Question
A feature to create pairs of combinations
Replies
9
Views
1K
bansche123
B
R
  • Question Question
Extracting data via Do Until loops and Nested Ifs vs Autofilter.. Reliability?
Replies
5
Views
720
RoryA
RoryA
S
  • Question Question
Need help with VBA for Lottery Combination Generator
2
Replies
17
Views
3K
studfin
S
U
  • Question Question
Creating a search across multiple worksheets using checkbox criteria
Replies
0
Views
733
urz2006
U

Forum statistics

Threads
1,222,562
Messages
6,166,804
Members
452,073
Latest member
akinch

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top