1. ## Lottery Checker (E2003)

Hi

I have been placed in charge of the Companies lottery which involves 84 lines of unique combinations, checking them each week will be a nightmare as the UK lottery site only checks 1 line at a time, I though that this could be automated in Excel and wondered if anybody could help or offer advice.

<font color=blue>1. THE UK LOTTERY</font color=blue>

there are 49 numbers ranging from 1 - 49, 6 each week are picked randomly and then a seventh ball is picked as a bonus ball.

The prizes are awarded for the following matches

match 3, match 4, match 5, match 5 + bonus ball and match 6 (jackpot)

<font color=blue>2. OUR NUMBERS / MY EXCEL WORKSHEET</font color=blue>

We have 9 members of our lottery, each of them have picked a unique number between 1 - 49, HansV in another forum helped me determine that for each line to be unique using these 9 number our syndicate must complete 84 lines, for instance if our members had picked 1,2,3,4,5,6,7,8,9 our lines would be something like:-

Line 1 = 1,2,3,4,5,6
Line 2 = 1,2,3,4,5,7
Line 3 = 1,2,3,4,5,8
Line 4 = 1,2,3,4,5,9
Line 5 = 1,2,3,4,6,9
Line 6 = 1,2,3,4,7,9
etc. to line 84

My worksheet has 6 columns A-F into which I have typed in all of the 84 unique combinations of our actual numbers

<font color=blue>3. WHAT I ENVISAGE (IF POSSIBLE)</font color=blue>

7 Cells on the worksheet to type in that weeks actual numbers, the 6 normal numbers and the bonus number, a button which when clicked will compare each of my 84 lines with the number and create a result in column G

for example if this weeks numbers were 1,2,3,9,10,11 - bonus 13 the command would compare each of my lines and show result as below

Winning numbers this week
Line 1 = 1,2,3,4,5,6 3
Line 2 = 1,2,3,4,5,7 3
Line 3 = 1,2,3,4,5,8 3
Line 4 = 1,2,3,4,5,9 4
Line 5 = 1,2,3,4,6,9 4
Line 6 = 1,2,3,4,7,9 4

Any help much appreciated

Cheers

Danny

Edited by Danny, the above results have had the spaces that I put in draft mode removed? the winning numbers this week column should be on the right with the results below it not next to the picked numbers.

2. ## Re: Lottery Checker (E2003)

Is this what you are after?

Steve

3. ## Re: Lottery Checker (E2003)

See screenshot. The formulas in H2 and I2 are array formulas; don't type the brackets { } yourself, and confirm the formulas with Ctrl+Shift+Enter instead of Enter. You can fill down H2:I2 as far as needed.

4. ## Re: Lottery Checker (E2003)

Thanks Steve and Hans

Steve: thanks for the example, I will copy it down to disc and have a crack at modifying it as there are actual 6 numbers + the bonus, expect an SOS on Monday!!

Cheers

Danny

5. ## Re: Lottery Checker (E2003)

Hi Steve,

I have noiw added another column for the 6th number and used it at the weekend, checking the 6 numbers worked fine but the problem I have is the bonus ball, perhaps I did not explain its function correctly.

The lottery user selects 6 numbers which are compared to 6 numbers drawn by the lottery machine, exactly how the main part of your worksheet works, the bonus ball however is selected by the lottery machine and compared against the 6 numbers choosen by the lottery user and if the main lottery numbers match 5 and the users 6th number matches the bonus ball he wins the 2nd prize, something like

if five number from \$A\$1:\$F\$1 exactly match 5 number \$A\$4:\$F\$84 and the 6th number matches G1 = match 5 + bonus ball

This is the only time that the bonus ball comes into play

Hope that makes sense

Steve

6. ## Re: Lottery Checker (E2003)

See attached version of Steve's example.