Welcome Guest
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
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

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

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

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.

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

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

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.

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

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 *

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

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.

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!

Arraste com a tecla Shift para reorganizar.

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

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

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

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

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.

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

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.

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.

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.

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.

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.

 Page 1 of 3