Try this code. The number of 4 matches will be put into the column next to
the numbers you play. The 5 matches in the next column and the 6 matches in
the 3rd column


Sub lottery()

Set Winners = Sheets("Sheet1").Range("A1:A200")

Set OurNumbers = Sheets("Sheet2").Range("A1:A100")

For Each OurNum In OurNumbers
Match4 = 0
Match5 = 0
Match6 = 0

OurArray = Split(OurNum, ",")
For Each WinNum In Winners

WinArray = Split(WinNum, ",")
Matches = 0
For i = LBound(OurArray) To UBound(OurArray)
For j = LBound(WinNum) To UBound(WinNum)
If OurArray(i) = WinNum(j) Then
Matches = Matches + 1
Exit For
End If
Next j
Next i
Select Case Matches

Case 4: Match4 = Match4 + 1
Case 5: Match5 = Match5 + 1
Case 6: Match6 = Match6 + 1
End Select

Next WinNum
OutNum.Offset(0, 1) = Match4
OutNum.Offset(0, 2) = Match5
OutNum.Offset(0, 3) = Match6

Next OurNum

End Sub


" wrote:

Hello All,

I have the 6 winning numbers in a history file in the format:
3,6,13,22,31,32
10,17,25,30,33,36

I also have the numbers we played in another location in the same
format. (Same file)
The formulas I tried became unmanagable and I could not finish.

Q: Does anyone have a bit of code/macro that will check our numbers
against the history and report if there are 4,5 matches? That is,
tell us if our numbers have won before.

I can modify my data format to suit your code if need be.

Thanks for any help.

Greer