Welcome Guest
Log In | Register )
The time is now 7:59 pm
You last visited November 22, 2014, 7:31 pm
All times shown are
Eastern Time (GMT-5:00)

Excel help (dates and draws)

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

Page 2 of 3
PrintE-mailLink
Avatar
Florida
United States
Member #66575
October 30, 2008
3544 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

    Avatar

    United States
    Member #65711
    October 4, 2008
    30 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!

      Pogo's avatar - wfl
      NC
      United States
      Member #29378
      January 1, 2006
      533 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

        four4me's avatar - gate1
        MD
        United States
        Member #1701
        June 18, 2003
        7905 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.
          Avatar
          Florida
          United States
          Member #66575
          October 30, 2008
          3544 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

          xxxxxxxxxxxxxxLookup
          6843Mid-DayThursdayRed6843ThursdayRed
          1500Mid-DayWednesdayOctober 24, 2012*Monday41204
          4449Mid-DayTuesdayOctober 23, 2012*Friday39885

          CG

            Avatar

            United States
            Member #41846
            June 23, 2006
            340 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

              SergeM's avatar - alas
              Economy class
              Belgium
              Member #123705
              February 27, 2012
              2921 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.

                SergeM's avatar - alas
                Economy class
                Belgium
                Member #123705
                February 27, 2012
                2921 Posts
                Offline
                Posted: October 28, 2012, 11:26 am - IP Logged

                 

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

                  SergeM's avatar - alas
                  Economy class
                  Belgium
                  Member #123705
                  February 27, 2012
                  2921 Posts
                  Offline
                  Posted: October 28, 2012, 1:10 pm - IP Logged

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

                    Very Nice Serge

                     

                    Thank You

                    Carlig

                      SergeM's avatar - alas
                      Economy class
                      Belgium
                      Member #123705
                      February 27, 2012
                      2921 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.

                        Avatar

                        United States
                        Member #41846
                        June 23, 2006
                        340 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?

                          Avatar

                          United States
                          Member #41846
                          June 23, 2006
                          340 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

                            SergeM's avatar - alas
                            Economy class
                            Belgium
                            Member #123705
                            February 27, 2012
                            2921 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.

                              Avatar

                              United States
                              Member #41846
                              June 23, 2006
                              340 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.