Welcome Guest
You last visited January 23, 2017, 11:43 am
All times shown are
Eastern Time (GMT-5:00)

# Excel help (dates and draws)

Topic closed. 33 replies. Last post 4 years ago by alchemist7.

 Page 2 of 3
Florida
United States
Member #66575
October 30, 2008
3549 Posts
Offline
 Posted: October 27, 2012, 5:09 pm - IP Logged

If your good at adjusting formulas, I think this will do what you want with no macro

If I get a chance later , I will play with it    Its an array formula so need to  control/shift/enter

=IF(ROW(\$A2)-ROW(\$A\$1)>COUNTIF(\$A\$3:\$A\$261,\$I\$3),"",INDEX(\$D\$1:\$D\$261,SMALL(IF(\$A\$3:\$A\$261=\$I\$3,ROW(\$D\$3:\$D\$261)),ROW(\$A2)-ROW(\$A\$1))))

Carlig

United States
Member #65711
October 4, 2008
39 Posts
Offline
 Posted: October 27, 2012, 6:39 pm - IP Logged

If your good at adjusting formulas, I think this will do what you want with no macro

If I get a chance later , I will play with it    Its an array formula so need to  control/shift/enter

=IF(ROW(\$A2)-ROW(\$A\$1)>COUNTIF(\$A\$3:\$A\$261,\$I\$3),"",INDEX(\$D\$1:\$D\$261,SMALL(IF(\$A\$3:\$A\$261=\$I\$3,ROW(\$D\$3:\$D\$261)),ROW(\$A2)-ROW(\$A\$1))))

Carlig

Thanks CarliG,

I will give it a shot and let you know how it works.

Thanks again!

NC
United States
Member #29378
January 1, 2006
552 Posts
Offline
 Posted: October 28, 2012, 12:13 am - IP Logged

If your good at adjusting formulas, I think this will do what you want with no macro

If I get a chance later , I will play with it    Its an array formula so need to  control/shift/enter

=IF(ROW(\$A2)-ROW(\$A\$1)>COUNTIF(\$A\$3:\$A\$261,\$I\$3),"",INDEX(\$D\$1:\$D\$261,SMALL(IF(\$A\$3:\$A\$261=\$I\$3,ROW(\$D\$3:\$D\$261)),ROW(\$A2)-ROW(\$A\$1))))

Carlig

Excellent Work! Wow, everytime I start touching column or row... It blows up in my face! lol -Pogo

MD
United States
Member #1701
June 18, 2003
8395 Posts
Offline
 Posted: October 28, 2012, 12:22 am - IP Logged

I wanted to know if anyone in the forum has developed a system that can tell you dates of a combination of the number that was drawn. Below is an example off my state lottery web site for tonights drawn number.  If no one has a system, can someone point me in the right direction of getting started? I was trying to have this for a large group of numbers not just one or enter just one at a time.

Thanks for any help!

Digit1Digit2Digit3Digit4DrawDate
4492Evening10/26/12
4429Midday09/18/09
2494Midday07/21/08
4294Midday02/03/06
9442Evening02/25/05
9424Midday07/26/03

Lottery post has a data base of every states numbers look on the blue menu bar under results.

You may be able to obtain a number history file from your state lottery in excel format also.

Big John says. You don't hit the number. The number hits you!!!!

I'm not Big John, I'm Four4me, Big John's a friend.
Florida
United States
Member #66575
October 30, 2008
3549 Posts
Offline
 Posted: October 28, 2012, 12:31 am - IP Logged

Excellent Work! Wow, everytime I start touching column or row... It blows up in my face! lol -Pogo

I can get the formula to work but I cant get the format right for like the date so it will pick it up .

but if I put in something besides date it does give me what I want  %^&*(*&^* ..    Heck, At least Im trying   lol

If I put in red it gives me red, but when I put date , changes it up

 xxx xxxxx xxx xxx Lookup 6843 Mid-Day Thursday Red 6843 Thursday Red 1500 Mid-Day Wednesday October 24, 2012 * Monday 41204 4449 Mid-Day Tuesday October 23, 2012 * Friday 39885

CG

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: October 28, 2012, 8:31 am - IP Logged

The bad thing about a macro it is generally writen for a specific purpose and a specific data set. if you can follow the code it can be modified, but you need to understand what the programmer was trying to do.  If you understand that you would would probably just write your own.

Alchemist7  if your data is set up like your example, and it probably is not then this macro will look for 4 values you place in row 1 col J-M and show any matches below. I have included many comments to make it easier to follow.

Sub find_number()
' #'s data starts on line 2 col A-D date is in col F
' comb to look for is in line 1 col J-M this can also be stated as Column 10-13 numbers are usually easier to program
' where is the results going to be displayed.  I will choose line 3 starting at col J or 10
' do you want exact match or box match.  I am going to assume a box match
Dim tn(10), dn(10) ' these array variables will let me easily check for a box match
Range("j3:p5000").Select
Selection.ClearContents ' clear this space
Selection.Interior.ColorIndex = 0 ' remove coloring if any
Range("I1").Select' moves cursor out of the way
Ln = 3: cl = 10 'initial result display location
For y = 10 To 13: a = Cells(1, y): tn(a) = tn(a) + 1: Next y ' target #'s to look for loaded in array tn()
'set up a loop to look thru all of your data
For x = 2 To 5000 ' data starts on line 2 and goes thru 5000 change as appropiate end # is not critical can be well beyond available data lines
For y = 0 To 10: dn(y) = 0: Next y ' this clears the dn() array after every line checked
For y = 1 To 4 ' four data elements
If a = "" Then a = 5000: GoTo done ' terminate loop if out of data
a = Cells(x, y): dn(a) = dn(a) + 1: Next y ' this draw "x" is now in array dn()ready to be compared to tn()
GoSub compare 'a gosub is not an absolute requirement here but in my opinion makes the code less cluttered

done:
Next x

Exit Sub

compare: 'compare the two arrays and display whatever you choose
m = 0 ' will be used to count # of matches. this clears the variable before each use
For v = 0 To 9
If tn(v) = dn(v) Then m = m + 1
Next v ' at this point if all of the numbers are the same m will =10 order does not matter this macro looks for a box match
If m = 10 Then GoSub display 'again convienance
Return

display: ' display whatever we want I have chosen to show all six elements
Cells(Ln, cl) = Cells(x, 1): Cells(Ln, cl + 1) = Cells(x, 2): Cells(Ln, cl + 2) = Cells(x, 3): Cells(Ln, cl + 3) = Cells(x, 4)
Cells(Ln, cl + 4) = Cells(x, 5): Cells(Ln, cl + 5) = Cells(x, 6)
Ln = Ln + 1: cl = 10
Return

end sub

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: October 28, 2012, 8:49 am - IP Logged

I wanted to know if anyone in the forum has developed a system that can tell you dates of a combination of the number that was drawn. Below is an example off my state lottery web site for tonights drawn number.  If no one has a system, can someone point me in the right direction of getting started? I was trying to have this for a large group of numbers not just one or enter just one at a time.

Thanks for any help!

Digit1Digit2Digit3Digit4DrawDate
4492Evening10/26/12
4429Midday09/18/09
2494Midday07/21/08
4294Midday02/03/06
9442Evening02/25/05
9424Midday07/26/03

Date when a number was drawn.

When you mean digit by number
New headers 0 to 9 on the right. Enter function below,  if number is in this row, then write the date (6th column here, should be first one)

When you mean number by number
Concatenate and convert to number the 4 digits. New headers: 0 to 9999. Enter function:  if number is in this row, then write the date.

Now you have under each number the full list of dates. Adding a new drawing under the list, copy paste function above to that row. Lock the first row, so you can scroll. You can also make room above to place functions. The logical way is to add new drawings below.

No VBA involved. If the computer gets slow, then copy and paste the cells as values, use copy paste special.

When you want to see the dates for a certain number, you can use the built in filter. Excel is made for this.

To find what you posted, you can do with 51 columns extra.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: October 28, 2012, 11:26 am - IP Logged

Example
Show me all dates with couples of 4's.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: October 28, 2012, 1:10 pm - IP Logged

Florida
United States
Member #66575
October 30, 2008
3549 Posts
Offline
 Posted: October 28, 2012, 3:09 pm - IP Logged

Very Nice Serge

Thank You

Carlig

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: October 28, 2012, 5:43 pm - IP Logged

The bad thing about a macro it is generally writen for a specific purpose and a specific data set. if you can follow the code it can be modified, but you need to understand what the programmer was trying to do.  If you understand that you would would probably just write your own.

Alchemist7  if your data is set up like your example, and it probably is not then this macro will look for 4 values you place in row 1 col J-M and show any matches below. I have included many comments to make it easier to follow.

Sub find_number()
' #'s data starts on line 2 col A-D date is in col F
' comb to look for is in line 1 col J-M this can also be stated as Column 10-13 numbers are usually easier to program
' where is the results going to be displayed.  I will choose line 3 starting at col J or 10
' do you want exact match or box match.  I am going to assume a box match
Dim tn(10), dn(10) ' these array variables will let me easily check for a box match
Range("j3:p5000").Select
Selection.ClearContents ' clear this space
Selection.Interior.ColorIndex = 0 ' remove coloring if any
Range("I1").Select' moves cursor out of the way
Ln = 3: cl = 10 'initial result display location
For y = 10 To 13: a = Cells(1, y): tn(a) = tn(a) + 1: Next y ' target #'s to look for loaded in array tn()
'set up a loop to look thru all of your data
For x = 2 To 5000 ' data starts on line 2 and goes thru 5000 change as appropiate end # is not critical can be well beyond available data lines
For y = 0 To 10: dn(y) = 0: Next y ' this clears the dn() array after every line checked
For y = 1 To 4 ' four data elements
If a = "" Then a = 5000: GoTo done ' terminate loop if out of data
a = Cells(x, y): dn(a) = dn(a) + 1: Next y ' this draw "x" is now in array dn()ready to be compared to tn()
GoSub compare 'a gosub is not an absolute requirement here but in my opinion makes the code less cluttered

done:
Next x

Exit Sub

compare: 'compare the two arrays and display whatever you choose
m = 0 ' will be used to count # of matches. this clears the variable before each use
For v = 0 To 9
If tn(v) = dn(v) Then m = m + 1
Next v ' at this point if all of the numbers are the same m will =10 order does not matter this macro looks for a box match
If m = 10 Then GoSub display 'again convienance
Return

display: ' display whatever we want I have chosen to show all six elements
Cells(Ln, cl) = Cells(x, 1): Cells(Ln, cl + 1) = Cells(x, 2): Cells(Ln, cl + 2) = Cells(x, 3): Cells(Ln, cl + 3) = Cells(x, 4)
Cells(Ln, cl + 4) = Cells(x, 5): Cells(Ln, cl + 5) = Cells(x, 6)
Ln = Ln + 1: cl = 10
Return

end sub

to phileight:

That code is full of mistakes and won't work.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: October 28, 2012, 6:26 pm - IP Logged

to phileight:

That code is full of mistakes and won't work.

It works on my computer. what specific problem or problems  are you having?

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: October 28, 2012, 6:45 pm - IP Logged

CarliG  can you set a file ,same format as alchemist7, see if you find any problem with the code. value to be looked for needs to be in JKLM row 1

thanks

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: October 28, 2012, 6:55 pm - IP Logged

It works on my computer. what specific problem or problems  are you having?

I have no problem with it, I debugged it.

If you are running that code without problems then you are a liar.

United States
Member #41846
June 23, 2006
460 Posts
Offline
 Posted: October 28, 2012, 8:03 pm - IP Logged

I have no problem with it, I debugged it.

If you are running that code without problems then you are a liar.

Sergio  I don't  play these games. glad you were able to correct my problems.  share so we can see the error of my ways.

 Page 2 of 3