Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lottery Checker (E2003)

    Is this what you are after?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lottery Checker (E2003)

    See attached version of Steve's example.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lottery Checker (E2003)

    Hans answered your question, I think.

    Sorry I misunderstood. I thought this was like the "powerball" in the US. You choose a number of balls and have a separate "powerball" to pick. The bonusball (ie powerball) is chosen separately and is distinct from the other balls...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •