+ Reply to Thread
Results 1 to 25 of 25

Can lottery filtering drop boxes be done with Excel

  1. #1
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Can lottery filtering drop boxes be done with Excel

    Hi everyone

    Ready made lottery filtering programs are easily available to buy online but I want to know can it be done this way using Excel. See attachment. I don't need anyone to create one because it will probably be a project length of time. I just want to know can it be done? thank you so much

    HHH
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Hi, I haven't taken a look at your file but waht do you mean by lottery. If it's random numbers, yes Excel can generate a list of random and unique numbers.
    I suggest you Google for that and you'll see a large number of links and tips
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93
    Quote Originally Posted by Keebellah View Post
    Hi, I haven't taken a look at your file but waht do you mean by lottery. If it's random numbers, yes Excel can generate a list of random and unique numbers.
    I suggest you Google for that and you'll see a large number of links and tips
    Hi Keebellah,
    I know Excel can generate lottery combinations but I not sure can it filter to my specification. Example remove a few numbers. I need this like a drop box. Hope it can be done, thanks

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I'll download the file and see what I can do, 'everything' is possible with VBA

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I need a little more information, What are the numbers 'behind' the dropboxes to be?
    I think an extra this is what I want included will paint a better picture to understand your question

  6. #6
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi Keebellah,

    The dropboxes don't have any numbers. Each boxes will have some magical formulas or VBA stuff (I don't understand these things) that has a fixed criteria. The box that say REMOVE ALL means if I copy/paste drop numbers 5,18,24 it will remove any combinations in the lottery that contain any one of those numbers. So lottery ticket number 1,2,3,4,5,6 will be filtered away because I dropped number 5 in the box that said REMOVE. Hence in the other box that says Minimum 1 means if I drop numbers 4,14,17,18,24,29,46,57,58,59 into that box. All the combination will have at least one of those numbers. Do you really think it can be done? Thanks
    HHH

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I'm sure it can be done but your explanation about 'dropping' a number, I think you will have to be more clear on what it is you want the code to do.
    Nothing magical about VBA, it's just common sense an correct interpretation of what you want it to do.
    I have very often made the mistake to do something based on an assumption and that didn't get me anywhere.

    I suggest you expand your sample with what you have ( which is probably the first file) and then place a box with what goes into it and mark the cells you say must be removed, maybe then it will start to be clear, sorry if I'm dense but I cannot make heads or tails of your request

  8. #8
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi Keebellah
    Sorry for the confusion, here is a last attempt
    Lets ignore the boxes and say it this way. After generating the lottery unique combinations, I need to apply filters such as remove this or these numbers. I can't type the number in because of human error plus could be thousands of them or a list of them. I could just highlight or select an area that contains those data and then options of which filter to use on those data. Hope that helps, thanks again
    HHH

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Not really, I still do not know what you want to do.
    In the sample file you have a yellow are with what you call lottery drawn numbers.
    Next to it you have a random generated table with numbers you can just 'grab'
    So far okay.
    Now what is the next step?
    Look for you with the final goal in your head your explanation seems clear enough but for a third-party to understand it, that's another story.
    Like I asked earlier, why not reattach the file with step one, then the next step and what you want as result?
    I think this will have to be done with macros but I am not going to start on an assumption.

  10. #10
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Thank you again for your kind attempts to understand. I know exactly what you mean, it is very important to understand my thinking rather than an assumption. I don't think I can explain any better or even attaching a new file would help. The drawn lottery numbers and the ramdomly generated numbers could be said as irrelevant. In-fact I didnt even need to put it there, sorry to confuse matters again.

    Lets do it this way; you can generate unique set of combinations so what would you use if I say to you, can you make a program to filter away combinations based on my criterias? lets ignore the drop boxes for now. Let say, you can create those criterias for me. Example, you made a part that says REMOVE these numbers. I then give you the set of numbers that I want removing, how would you do that fast? bare in mind theirs hundreds of them. Hope this finally help, if you can solve this and maybe even create one, I will find you when I am filthy rich to repay you. NO, am not being stupid, I am NOT trying to find the lottery myself, its still difficult even with this program I want but I am on to something incredible, based on facts.
    HHH

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I honestly do not know if I did get your idea but I came up with the following.
    Sheet2
    One button to generate a new / fresh list and the range is B6 through G and something
    The other button will display a userform with a listbox where you will see the list of unique numbers.
    Select one or more and press the button and all the selected numbers will be removed.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi Keebellah, thank you so much for doing that, I think we are getting close. I have attached my example in sheet 3. Thanks
    Attached Files Attached Files

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I think you've used a lot of forum space to explain what could be said in maybe three sentences
    Okay, Let's see if I got it right now
    What it boils doen to is the following.
    From a random selection of numbers between 1 and 50 you want to generate a number of lottery tickets with as much possible unique combinations.
    The rules you want to apply is that you add two (2) exceptions
    First: a list of numbers you do NOT want in the tickets
    Second: A list of numbers of which at least two (2) are always present.

    Is this the idea?

  14. #14
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Yes, I am terrible at explaining things

    Well, firstly, I want ALL possible 5 number combination from 1 to 50 to be genearated. It will have a total of 2,118,760 combinations. It will naturally all be unique so ensure you don't make it random.

    From here each combination will go through a series of filtering with criteria. There are about 10 different filters. I only show you two for simplicity.

    It basically looks at those 2,118,760 combinations, apply my criteria and remove any combinations that doesn't meet it. So, lottery ticket combination 1,2,3,4,5 will be removed because 1 of my filters says NO number 1 and 3. Likewise ticket 1,2,3,4,6 will also disappear and so on.

    Hope that is clear, thank you

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    That's clear but do you realize that you only have about 1.400.000 and something Rows in Excel so that will be two columns of ticket numbers.
    And generating a batch of unique combinations like take will take some time

    I guess you should start with saying the numbers you don't want and two numbers that you at least want present and generate a batch of x lottery numbers.
    I have no idea how much time will be needed to generate but maybe less than the 1.118.760 combinations

  16. #16
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi Keebellah,
    Two rows or more is fine but length of time to generate, ummm, perhaps no more than 3 hours is ok.
    Yes, good idea to remove numbers 1st should I use that option.
    Do we have a solution to the main issue I address though? how do I get the data to each filter box? last time you created a dropdown list. Its good but I need a faster method. If one filter was called MIN ONE, instead of clicking say 20 numbers in your dropdown list, I want to be able do this in a few clicks.
    Thanks

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Why are you so fixed on filter boxes.
    What is it you need? That's the crucial question.

  18. #18
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi again, Sorry, long day again. What I need is simple. I need to bring the total of all the unique ticket in a lottery down to as small as possible. So 2.1million to say 200,000. To do that, I need filters based on criteria such as REMOVE these numbers. Is this what you are asking?

    It doesn't have to be a filter box. Like I said, it can be a group of numbers on Excel sheet which I can just hightlight then right click (if possible) and it gives me say, 10 different filtering options for those data to be used in that particular filter. A dropbox filter is just an idea that I hope someone can make, its just easier for me to use. Understand that each filters required me to specify what number or numbers to use? You created that droplist and its good but I need something faster. If I use your droplist, some filters requires me too click over 500 times. Does that help?
    HHH

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Well I wrote some code to play with.
    What is does is quite simple
    1. Generate a list of unique combinations to a max of 2.400.000 and some
    2. Select the numbers you want to EXCLUDE and the numbers you want to Include (minimum of 2)
    3. Apply the filter and create a new list from the generated list answering to the filter specified
    The number op lottery tickets can be input in the yellow cell, minimum value is 0 that will do all the 2 mil plus and will take about 2-3 hours
    A lesser number will take shorter and the number entered will result in that number times 2 because it works on two columns.
    There is no check if the unique combinations themselves are unique in the entire list.
    You can write that if you want to
    This will do the most of what you wanted, while generating the list the status bar will show the progress as it goes.

    I suggest you start with 500 (will generate 498 lottery ticket numbers and will take several seconds.
    Have fun.
    Here's a screenshot of the sheet and the file as well

    Happy coding
    Attached Images Attached Images
    Attached Files Attached Files

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Added a final sorting round after the filter is applied
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Hi Keebellah,
    Had a play with it, I have to say, you tried hard to solve this and I can't thank you enough. Its not exactly what I want but I'll marked this post as solved. I feel guilty asking for more. I dont think Excel/VBA is the program for my need. THANK YOU enormously.

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    Well, was fun to think of a way to do this.
    I just want to add that I do not know why you need all the combinations and then filter.
    If you edit the code for the first step to generate the unique combinations and at the same time apply the filter you can directly generate a list of (unique) combinations that answer you request.
    But again, if I fully understood what you want(ed) maybe ...
    Hppy coding and if you hit the jackpot a small token

  23. #23
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    I will remember you. Find a way to create that Dropbox or something similar and tokens will become a goldmine. I can't tell you what I am doing other than its a business getting launched. I can say, am not stupid enough to try and find the jackpot myself. Do you know hundred of thousands of people buy lottery software, books etc to improive chance of winning the lottery? The sellers make rediculous claims and amazing testimonies from people winning using their system. Do buyers not ask to themselves, if something is that good, why are they selling it? People in this world believe in things without facts. I only do facts Find that way for me to input data in those filters using Excel only and I am coming to Holland to see you. Not the dropdown list for me to click, too slow. Oh, are we allowed to exchange emails? My new business idea will go global and fast, its just producing data, intteligently take care and thanks again
    HHH

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Can lottery filtering drop boxes be done with Excel

    I changed it to generate only if exceptions have been selected.
    So what it does now is create a list of unique combinations of 5 numbers between 1-50 without the numbers in the Excluded column and with at least two of the numbers in the Included column
    Works rather fast and if you set the number of tickets to generate to 0 it will go for as long as possible until it reaches about 1.4 mil plus rows.
    There is s fail safe that in case there are no combinations found for the in included and excluded the loop is terminated after 2000 tries.
    I don't see the problem here with selecting a number of numbers press commit and let it do the job.

    It might be necessary to press the Reset button in the userform because sometimes Excel does select numbers on it's own.
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    12-14-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Can lottery filtering drop boxes be done with Excel

    Oh that's much faster. Potentially, it can work using VBA. Just need that part you don't quite grasp yet, I may need to do re-word my explanation Have anice day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Vba : Filtering Using Text Boxes
    By excel_specialist in forum Excel General
    Replies: 1
    Last Post: 08-02-2017, 02:58 AM
  2. Horizontal Data Filtering - Drop down boxes
    By barqujo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-07-2013, 12:26 PM
  3. Filtering validation lists in drop down boxes
    By iamtheexcelguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2013, 09:56 AM
  4. Excel Combo Boxes and Filtering a Data Set
    By dec789 in forum Excel General
    Replies: 5
    Last Post: 09-12-2012, 08:18 AM
  5. excel drop down boxes
    By PBK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2007, 06:49 PM
  6. Drop down boxes in Excel
    By colt in forum Excel General
    Replies: 1
    Last Post: 10-03-2005, 03:05 PM
  7. [SOLVED] Drop Down Boxes in Excel
    By in forum Excel General
    Replies: 2
    Last Post: 04-15-2005, 09:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1