Welcome Guest
Log In | Register )
You last visited January 19, 2017, 11:54 am
All times shown are
Eastern Time (GMT-5:00)

Excel Assistance

Topic closed. 37 replies. Last post 5 years ago by time*treat.

Page 1 of 3
PrintE-mailLink
Avatar
homeland security
United States
Member #82523
November 15, 2009
98 Posts
Offline
Posted: September 21, 2011, 10:44 am - IP Logged

If someone could help me with this problem in excel, then the lottery would be solved!

Look at the below example as this is as simple as I can explain it! Please don’t over think this problem or you will get stuck like me!

 

Take the three words for example, Cake, Lace, Tape:

 

In the above three words, the common string letters in each word is “ae”

 

I am trying to develop a macro that can look at per say these three words for simplicity and find the common string letters amongst the words and tell me how many times the common strings appeared in a list of words- in the above example, the macro would tell me strings “ae” appeared in three words.

 

Look at the following words for a    more complex, yet another   simple example.    Don’t limit yourselves to traditional words:

 

Abxdfg

Ltrad

Dad

Ltm

 

As you can see the macro would tell me string “ad” is the common strings and it appeared in three words. The macro would have told me also that string “lt” appeared was a common string and it appeared in 2 words, but the reason why it wouldn’t tell this information is because I had specified only find a 2 letter length common string that appeared in at least 3 words or more.

I can easily right a macro with this subroutine logic for 6,000 words with 5 or less letter lengths long.. but keep reading..

 

I know this sounds easy but what if the word length is 100 letters long?   This is my dilemma!   Lets say I need the macro to find me a 20 string length of common letters among 6,000 words, which are each 100 letter length. This is where it gets challenging because each letter in a 100 length word can be chosen from A-Z. When I run the macro, according to my calculations it will take centuries to scan 6000(100 letter length words) and find a 25 common string amongst the words.  Any thoughts?

 

Maybe it's the control logic that I wrote in the macro to decipher each word, which causes the lengthy cross examinamination. I have a quad core processor desktop and it's still taking the calculations a long time to do.  Excel tells me it will take 800 years to complete the examinations Unhappy

 

it's like technology haven't caught up with expressions I would like a program to do.. Maybe I lack enginuity, but nevertheless, maybe you guys can pitch in

    karinda's avatar - WINGS
    New Jersey
    United States
    Member #108445
    March 25, 2011
    2855 Posts
    Offline
    Posted: September 21, 2011, 1:24 pm - IP Logged

    Hi thinktank,

    I saw your post in another Forum and now I don't know where it is, so I'll just respond to you here. Todd just added a new tool for the Pick3 and the Pick4 that will isolate strings of numbers.  If I understand your question correctly, it seems to me that you are looking to do something similar, but for the Cash-5. Good luck with your research. Hope this points you in the right direction. Here's the link to the discussion about the tool.

    http://www.lotterypost.com/thread/236768

      karinda's avatar - WINGS
      New Jersey
      United States
      Member #108445
      March 25, 2011
      2855 Posts
      Offline
      Posted: September 21, 2011, 5:17 pm - IP Logged

      If someone could help me with this problem in excel, then the lottery would be solved!

      Look at the below example as this is as simple as I can explain it! Please don’t over think this problem or you will get stuck like me!

       

      Take the three words for example, Cake, Lace, Tape:

       

      In the above three words, the common string letters in each word is “ae”

       

      I am trying to develop a macro that can look at per say these three words for simplicity and find the common string letters amongst the words and tell me how many times the common strings appeared in a list of words- in the above example, the macro would tell me strings “ae” appeared in three words.

       

      Look at the following words for a    more complex, yet another   simple example.    Don’t limit yourselves to traditional words:

       

      Abxdfg

      Ltrad

      Dad

      Ltm

       

      As you can see the macro would tell me string “ad” is the common strings and it appeared in three words. The macro would have told me also that string “lt” appeared was a common string and it appeared in 2 words, but the reason why it wouldn’t tell this information is because I had specified only find a 2 letter length common string that appeared in at least 3 words or more.

      I can easily right a macro with this subroutine logic for 6,000 words with 5 or less letter lengths long.. but keep reading..

       

      I know this sounds easy but what if the word length is 100 letters long?   This is my dilemma!   Lets say I need the macro to find me a 20 string length of common letters among 6,000 words, which are each 100 letter length. This is where it gets challenging because each letter in a 100 length word can be chosen from A-Z. When I run the macro, according to my calculations it will take centuries to scan 6000(100 letter length words) and find a 25 common string amongst the words.  Any thoughts?

       

      Maybe it's the control logic that I wrote in the macro to decipher each word, which causes the lengthy cross examinamination. I have a quad core processor desktop and it's still taking the calculations a long time to do.  Excel tells me it will take 800 years to complete the examinations Unhappy

       

      it's like technology haven't caught up with expressions I would like a program to do.. Maybe I lack enginuity, but nevertheless, maybe you guys can pitch in

      Thinktank, 

      Essentially, this is what a program like Windows Explorer does when a file search is done.

      For example, yesterday, I wanted to find a program on my computer that began with av,

      so I searched for av*.*.  It found me what I needed.  If I left the wildcard out, it found me more

      than I needed.  So there are programs that will do this and not take 800 years, even on my

      machine. :)  What I'm not sure about is what you're trying to accomplish. I don't know what

      the file length limit is in Windows XP for example.  File names are longer than they used

      to be.  But I think you would need to have a fixed size, even if the numbers are delimited with

      leading zeroes. Once you generate the list that you want to extract from, you can convert each

      list item into small individual text files, or some other format, put them in thier own directory and use a

      canned program to search the files. Then one you have the list, you can create a file from that and import it

      the results back into Excel.  I think what you are trying to do is already done for you. I think you just need

       to adapt what's available to the data that you have.

        Avatar
        bgonçalves
        Brasil
        Member #92564
        June 9, 2010
        2134 Posts
        Online
        Posted: September 21, 2011, 8:04 pm - IP Logged

        Hello, think I can give a suggestion, an example of a lottery 49 / 6, get the list of results already passed and assign drive letters, so we have 6 positions, get 4 results, can not be followed here if you hit these intervals of 4 drawing, which will have to get halfway to success, then 4 6 letter will give results to 6x4 = 24 letters, doing so takes   The factor that rarely repeats the numbers from previous draws 6 pieces but rather as pairs and trios. After doing this to give up to 6 letters 24 letters in four contests, do in diver   Sets, but all the previous draws, which is the reference (base), then join   The letters AM = ofa block with another block, or AMC with 2nd block (block 4 are the results represented by 24 letters, never joining the same block, ok

          RJOh's avatar - chipmunk
          mid-Ohio
          United States
          Member #9
          March 24, 2001
          19900 Posts
          Offline
          Posted: September 22, 2011, 7:25 am - IP Logged

          "If someone could help me with this problem in excel, then the lottery would be solved!"

          If it was that easy, it would have been done by now. 

          When I read earlier this month about the Michigan couple who traveled to Massachusetts to spend $150K exploiting its lottery rundown, it proved there are plenty of folks who are looking for ways to beat the lotteries and willing to spend the money and time to do it if it is at all possible.

           * you don't need to buy more tickets, just buy a winning ticket * 
             
                       Evil Looking       

            Avatar
            homeland security
            United States
            Member #82523
            November 15, 2009
            98 Posts
            Offline
            Posted: September 22, 2011, 1:20 pm - IP Logged

            Thinktank, 

            Essentially, this is what a program like Windows Explorer does when a file search is done.

            For example, yesterday, I wanted to find a program on my computer that began with av,

            so I searched for av*.*.  It found me what I needed.  If I left the wildcard out, it found me more

            than I needed.  So there are programs that will do this and not take 800 years, even on my

            machine. :)  What I'm not sure about is what you're trying to accomplish. I don't know what

            the file length limit is in Windows XP for example.  File names are longer than they used

            to be.  But I think you would need to have a fixed size, even if the numbers are delimited with

            leading zeroes. Once you generate the list that you want to extract from, you can convert each

            list item into small individual text files, or some other format, put them in thier own directory and use a

            canned program to search the files. Then one you have the list, you can create a file from that and import it

            the results back into Excel.  I think what you are trying to do is already done for you. I think you just need

             to adapt what's available to the data that you have.

            Karinda thank you so much for your input, but I do wish it was so simple!  When writing programs, it comes down to one scenario:

            1) You must enter an input for the computer to calculate an output.

             

            In your case your input variable was av*.* and windows explorer gave you your output results (what you needed), which was a program that started with av.

            However, my input variable is more complex. It is as follows:

            I have two inputs for my program. The first input is the minimum number length for a common string as in my above example, that number was 2. The second input I enter is the minimum amount a string had to appear, which in my case I entered the number 3 , which means it had to appear in 3 words or more!

            So the computer gave me the output string "ad" because it met all the conditions.

            But the complex part is that the computer has to draw up all the 2 combinations strings and search for them.  However, it would be easy to enter a known variable like in your example you knew that your windows search program needed to start with the letters "av", however, what if the only thing you knew was that you used your program, in which you were searching in windows, alot in the past- and you forgot the name of the program, then the  logical search you can do is just search for the program that appeared at least 3 times in a day in the past (assuming you used it that often a day), and then windows returns (output) your list of programs. So, finally, you would scan the name of output list of programs windows found for you, and you may recognize the name of the program that look familiar to you, which jogged your memory that it started with "av", and so your program was among the list.

            I hope my example was well explained

             

            In myy case i don't know what letters my variable need to start with except i'm only looking

              Avatar
              homeland security
              United States
              Member #82523
              November 15, 2009
              98 Posts
              Offline
              Posted: September 22, 2011, 1:27 pm - IP Logged

              Hello, think I can give a suggestion, an example of a lottery 49 / 6, get the list of results already passed and assign drive letters, so we have 6 positions, get 4 results, can not be followed here if you hit these intervals of 4 drawing, which will have to get halfway to success, then 4 6 letter will give results to 6x4 = 24 letters, doing so takes   The factor that rarely repeats the numbers from previous draws 6 pieces but rather as pairs and trios. After doing this to give up to 6 letters 24 letters in four contests, do in diver   Sets, but all the previous draws, which is the reference (base), then join   The letters AM = ofa block with another block, or AMC with 2nd block (block 4 are the results represented by 24 letters, never joining the same block, ok

              dr. san thanks for your input, yet I'm still trying to grasp where you are going in your example! Please be patient with me, as I'm not quite the sharpest tool in the shed, so can you elaborate a bit more? With an example with using the actual numbers.

                Avatar
                bgonçalves
                Brasil
                Member #92564
                June 9, 2010
                2134 Posts
                Online
                Posted: September 22, 2011, 8:45 pm - IP Logged

                Hello, think, care, with the translation of google, have to see that perfect google Naoe think it is easy example of a lottery 49 / 6 01 13 15 21 32 45 A b c d e f Make the switch up to 24 letters, so they are 4 results   All chosen from previous results.   Choose several blocks of results (all the list of past results) After ab block combines well with a 2 cd pack, and get the idea   DAPs pieces or segments and join previous results, if any other forum member    Understand the macro can help ok!


                 
                Clique para mostrar traduções alternativas
                Arraste com a tecla Shift para reorganizar.
                Avaliar tradução
                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  460 Posts
                  Offline
                  Posted: September 22, 2011, 9:42 pm - IP Logged

                  think tank

                  I did not bother to try to figure how many possible combinations you would have with just 1 line of 100 characters of which you are looking for twenty. given your question i would offer three choices

                  1 forget it  2. wait for much faster computers  3. change the question

                  change the question . what if you generated a 20 character string of random characters  then searched your database of 6000 lines for this string. repeat as many times as you want or have time for.

                  a compiled program will run many times faster than a macro if you know a programmer.

                  good luck

                    Avatar
                    homeland security
                    United States
                    Member #82523
                    November 15, 2009
                    98 Posts
                    Offline
                    Posted: September 22, 2011, 9:48 pm - IP Logged

                    think tank

                    I did not bother to try to figure how many possible combinations you would have with just 1 line of 100 characters of which you are looking for twenty. given your question i would offer three choices

                    1 forget it  2. wait for much faster computers  3. change the question

                    change the question . what if you generated a 20 character string of random characters  then searched your database of 6000 lines for this string. repeat as many times as you want or have time for.

                    a compiled program will run many times faster than a macro if you know a programmer.

                    good luck

                    Thanks for your input phil, the only programming language I've done outside of macros, is C++ and that was like a decade ago in college..lol..Perhaps I should consider picking that software up and getting back to it

                      time*treat's avatar - radar

                      United States
                      Member #13130
                      March 30, 2005
                      2171 Posts
                      Offline
                      Posted: September 27, 2011, 1:54 pm - IP Logged

                      If someone could help me with this problem in excel, then the lottery would be solved!

                      Look at the below example as this is as simple as I can explain it! Please don’t over think this problem or you will get stuck like me!

                       

                      Take the three words for example, Cake, Lace, Tape:

                       

                      In the above three words, the common string letters in each word is “ae”

                       

                      I am trying to develop a macro that can look at per say these three words for simplicity and find the common string letters amongst the words and tell me how many times the common strings appeared in a list of words- in the above example, the macro would tell me strings “ae” appeared in three words.

                       

                      Look at the following words for a    more complex, yet another   simple example.    Don’t limit yourselves to traditional words:

                       

                      Abxdfg

                      Ltrad

                      Dad

                      Ltm

                       

                      As you can see the macro would tell me string “ad” is the common strings and it appeared in three words. The macro would have told me also that string “lt” appeared was a common string and it appeared in 2 words, but the reason why it wouldn’t tell this information is because I had specified only find a 2 letter length common string that appeared in at least 3 words or more.

                      I can easily right a macro with this subroutine logic for 6,000 words with 5 or less letter lengths long.. but keep reading..

                       

                      I know this sounds easy but what if the word length is 100 letters long?   This is my dilemma!   Lets say I need the macro to find me a 20 string length of common letters among 6,000 words, which are each 100 letter length. This is where it gets challenging because each letter in a 100 length word can be chosen from A-Z. When I run the macro, according to my calculations it will take centuries to scan 6000(100 letter length words) and find a 25 common string amongst the words.  Any thoughts?

                       

                      Maybe it's the control logic that I wrote in the macro to decipher each word, which causes the lengthy cross examinamination. I have a quad core processor desktop and it's still taking the calculations a long time to do.  Excel tells me it will take 800 years to complete the examinations Unhappy

                       

                      it's like technology haven't caught up with expressions I would like a program to do.. Maybe I lack enginuity, but nevertheless, maybe you guys can pitch in

                      Q: Lets say I need the macro to find me a 20 string length of common letters among 6,000 words, which are each 100 letter length.

                      I: 20 character string target, 100 characters per line, 6000 lines

                      (This will require 3 or 4 arrays, 2 or 3 counters, and judicious use of for-next loops)

                      set counter(s) to 0


                      A: grab chars 1~20 from line 1.
                      (this system will work much faster if the 20 characters are placed individually in an array of length 20, rather than a single string of 20 chars.)

                      check char 1 against each char in line 2, until there's a match, then check char 2 against the adjacent char that follows that first match
                      do that until you either have a 20 string match or you have a non-match somewhere before 20 chars, or you run to the end of the 2nd line.
                      --If you have a non-match then start checking again at the first next match for char 1
                      --if you get a perfect 20 string match, increment counter by 1. go to next line (line 3)
                      --if your first char match is past the 81 char mark, go to next line (line 3) and start checking against line 1 (looking for a match with the first character in the string)

                      do that for each of the lines, then if your counter is high enough, record the string and the count. (this might be done in 2 arrays) -- add 1 to counter for TOTAL appearances of target string

                      go back to line 1, grab chars 2~21. Compare that string to the string(s) in your output list (that becomes important, later, as the list grows -- keeps you from recounting "popular" strings)
                      --If you find a match, drop that string and grab chars 3~22 (repeat shifting over, as necessary)
                      -- if you don't find a match, compare it to lines 2 ~ 6000, like normal

                      Over all, you will shift from chars 1~20 to chars 81~100 and from master line 1 down to master line 5999 (because there's nothing to compare line 6000 to)

                      It will take you longer to write & debug it, than to run it. Cool

                      In neo-conned Amerika, bank robs you.
                      Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                        Avatar
                        homeland security
                        United States
                        Member #82523
                        November 15, 2009
                        98 Posts
                        Offline
                        Posted: September 27, 2011, 7:35 pm - IP Logged

                        Q: Lets say I need the macro to find me a 20 string length of common letters among 6,000 words, which are each 100 letter length.

                        I: 20 character string target, 100 characters per line, 6000 lines

                        (This will require 3 or 4 arrays, 2 or 3 counters, and judicious use of for-next loops)

                        set counter(s) to 0


                        A: grab chars 1~20 from line 1.
                        (this system will work much faster if the 20 characters are placed individually in an array of length 20, rather than a single string of 20 chars.)

                        check char 1 against each char in line 2, until there's a match, then check char 2 against the adjacent char that follows that first match
                        do that until you either have a 20 string match or you have a non-match somewhere before 20 chars, or you run to the end of the 2nd line.
                        --If you have a non-match then start checking again at the first next match for char 1
                        --if you get a perfect 20 string match, increment counter by 1. go to next line (line 3)
                        --if your first char match is past the 81 char mark, go to next line (line 3) and start checking against line 1 (looking for a match with the first character in the string)

                        do that for each of the lines, then if your counter is high enough, record the string and the count. (this might be done in 2 arrays) -- add 1 to counter for TOTAL appearances of target string

                        go back to line 1, grab chars 2~21. Compare that string to the string(s) in your output list (that becomes important, later, as the list grows -- keeps you from recounting "popular" strings)
                        --If you find a match, drop that string and grab chars 3~22 (repeat shifting over, as necessary)
                        -- if you don't find a match, compare it to lines 2 ~ 6000, like normal

                        Over all, you will shift from chars 1~20 to chars 81~100 and from master line 1 down to master line 5999 (because there's nothing to compare line 6000 to)

                        It will take you longer to write & debug it, than to run it. Cool

                        Thanks time*treat for your thorough input, and to be honest one word is over 300 letters long, but I really see where you are going with your example, as I will try to write such a scheme in a macro. Feel free if you want to help develop this algorythm.  My original program was already allocated such that each word/ 300 letters were placed in their own cell in excel.  This made things easier to decipher.  My only issue was how do I choose the specific 20 letters out of the 300 letters to do the search, but I see what you are proposing.  According to your example, I will choose the first 20 letters out of the 300 and progress towards the end of the 300 letters in 20 letter selection.

                          time*treat's avatar - radar

                          United States
                          Member #13130
                          March 30, 2005
                          2171 Posts
                          Offline
                          Posted: September 27, 2011, 8:21 pm - IP Logged

                          I'll help out where I can. This might illustrate better, too (very, very simplified).

                          Let's say I'm looking for 3-digit repeats and this is my list:
                          25 chars per line, 16 lines

                          Line #01: 1415926535897932384626433
                          Line #02: 8327950288419716939937510
                          Line #03: 5820974944592307816406286
                          Line #04: 2089986280348253421170679
                          Line #05: 8214808651328230664709384
                          Line #06: 4609550582231725359408128
                          Line #07: 4811174502841027019385211
                          Line #08: 0555964462294895493038196
                          Line #09: 4428810975665933446128475
                          Line #10: 6482337867831652712019091
                          Line #11: 4564856692346034861045432
                          Line #12: 6648213393607260249141273
                          Line #13: 7245870066063155881748815
                          Line #14: 2092096282925409171536436
                          Line #15: 7892590360011330530548820
                          Line #16: 4665213841469519415116094

                          ----------------------------

                          The original way would have you check each line for 000, 001, 002, and so on; checking sets that don't even exist. 

                          The way I'm suggesting is the code grabs 141 (first three chars) then check each line until it finds "1"
                          if it finds "1", check and see if there is "4" next to it. If there is, check and see if there is another "1" after that.

                          In the set above, "1"s are found on many lines, but their neighbors are wrong. Same for "14"
                          But, a proper "141" is found on line 12.

                          After checking every line I add 141 to my "sets that repeated" list.
                          Now I start back up top, but now I grab 415 and check against my "sets that repeated" list to make sure it's not already there. (I know this step looks like overkill, but it will save lots of time on really long lists).

                          "41" and "15" show up, but a proper 415 (finally) repeats on line 16. Now my "sets that repeated" list looks like
                          numbers: 141, 415
                          count: 2,2

                          Back up top. Grab 159. This set doesn't repeat
                          Back up top. Grab 592. This set repeats on line 3

                          Now my "sets that repeated" list looks like
                          numbers: 141, 415, 592
                          count: 2,2,2

                          So on and so on.

                          In neo-conned Amerika, bank robs you.
                          Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                            Avatar
                            homeland security
                            United States
                            Member #82523
                            November 15, 2009
                            98 Posts
                            Offline
                            Posted: September 28, 2011, 8:41 am - IP Logged

                            I see we are on the same page, just to clarify one more thing.  It's imperative to realize that according to your example the order of arrangement of strings are not important, ie: like your first 3 set of string, 141.

                            if the macro sees a 1,4,1 in line#2, then it's suppose to consider that string found in line#2. It doesn't matter if the string is found in exact order as it is written, or if it find letters 1,4,1 scattered throughout the line, but as long as it finds the letters 1,4,1 in a line, then the condition is true, and thus increment the counter.  I will pm you the particular thing I'm doing because I see you have the insight where I'm going, so I will fill in the holes. It's pretty complicated but I think you will fill it out.

                              time*treat's avatar - radar

                              United States
                              Member #13130
                              March 30, 2005
                              2171 Posts
                              Offline
                              Posted: September 28, 2011, 9:54 am - IP Logged

                              I see we are on the same page, just to clarify one more thing.  It's imperative to realize that according to your example the order of arrangement of strings are not important, ie: like your first 3 set of string, 141.

                              if the macro sees a 1,4,1 in line#2, then it's suppose to consider that string found in line#2. It doesn't matter if the string is found in exact order as it is written, or if it find letters 1,4,1 scattered throughout the line, but as long as it finds the letters 1,4,1 in a line, then the condition is true, and thus increment the counter.  I will pm you the particular thing I'm doing because I see you have the insight where I'm going, so I will fill in the holes. It's pretty complicated but I think you will fill it out.

                              You could do it that way, but it would be a little bit more coding.

                              I was thinking you were looking for the string to be a contiguous set of characters.

                              e.g. if target = 1,4,1 then

                              1,2,5,6,1,4,1 = pass

                              but

                              1,2,5,4,6,1,1 = fail



                              In neo-conned Amerika, bank robs you.
                              Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.