Excel help (dates and draws)

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!

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

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.

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

CG

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

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.

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

Very Nice Serge

Thank You

Carlig

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?

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

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.