Excel lottery

[dezian]

I've just written some code to check the lottery numbers, I have a large sindicate and need to check 16 columns of 6 numbers against the 6 lottery numbers that are placed in a row of six. Heres what I've got so far, the code works by checking every numbers in each row and returning the matched numbers as a number in a cell, this is very long winded and requires 2 macros as theres so much code. The code bellow shows the first two number to be checked, after that all that changes is the cell references. What I would like to do is condense it, say each column instead of each cell and returning the matched value, any one help please ?

Sub Lottery()

'Resets the counter'
Match = 0
NoMatch = 0

'looks at the 1st column & compares its value to 1st number'
If ActiveSheet.Cells(3, 2) = Cells(12, 2).Value Then
Match = Match + 1
Else: NoMatch = NoMatch + 0
End If
If ActiveSheet.Cells(4, 2) = Cells(12, 2).Value Then
Match = Match + 1
Else: NoMatch = NoMatch + 0
End If

So on and so forth !

Cheers Andy

 

[Bowers74]

OK,

Let's assume that the weekly lottery numbers are located in row 1 and in range "A1:F1". Now let's assume that your 16 columns of 6 rows each are located in range "A510". Let's also assume that you want to know how many of the numbers from the columns, match the numbers in row 1.

Now, let's assume that something like this can be solved without using code, but instead using an Excel Array Formula!

Enter the following formula into the cell A12:

=SUM(IF($A$1:$F$1-A$5:A$10;0;1))

Now press Ctrl+Shift+Enter to enter this formula as an array formula. You should see "curly" brckets appear on either side of the formula {=Formula}.

Now copy this formula into the cells B1212.

And there you go!

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[dezian]

Works a treat, thanks for your time.

Cheers Andy Hickson

 

[Bowers74]

The Traditional way to thank someone in these forums is to give them a star. But since I was Tip Master for a day I will let this one slide.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!