Welcome Guest
Log In | Register )
You last visited December 7, 2016, 8:59 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Formula

Topic closed. 8 replies. Last post 11 years ago by scott_one.

Page 1 of 1
PrintE-mailLink
scott_one's avatar - abstract
Lapeer, MI
United States
Member #9977
December 29, 2004
31 Posts
Offline
Posted: January 15, 2006, 4:36 pm - IP Logged

Hello excel pros,

 I am trying to list the dates that a certain number has been drawn. I can get the last time a number has been drawn by using this formula:

=VLOOKUP(J3,$E:$F,2,FALSE)

J3 is the number i'm looking for - ex. 002

E is the column with drawn numbers and F is the column with the date.

002 has been drawn 9 times and I want a list of all the dates it was drawn.

My problem is looking past the last time it was drawn to the time before that that it was drawn and so on.

I'm hoping that this makes sense to you, I know its kinda confusing.....sorry.

Thanks for any help in advance.

    hypersoniq's avatar - 8ball
    Pennsylvania
    United States
    Member #1340
    April 6, 2003
    2450 Posts
    Offline
    Posted: January 15, 2006, 7:08 pm - IP Logged

    you want to see the number drawn before and also the number after?

    I often wondered how to do that... I came up with a simplisticyet effective solution...

    1. create a column for the number drawn before (for example E12 has a draw, to capture it on the same line, make F12=E11), do the same for the next draw (G12=E13)...

    now, use autofilter (select the columns first by highlighting)

    if you search the main draw for 002, it will pull up not only the date, but the number before and after also.

    Playing more than one ticket per game is betting against yourself.

      scott_one's avatar - abstract
      Lapeer, MI
      United States
      Member #9977
      December 29, 2004
      31 Posts
      Offline
      Posted: January 15, 2006, 8:36 pm - IP Logged

      The left 2 columns are the draw history and date of draw. 002 is listed 3 times and I want to know the dates of each draw. Date Last Drawn is what I want to find.


      NUM     DATE         NUM     DATE LAST DRAWN                 
      002     01/08/06         002     01/08/06     01/03/06     12/27/05         
      322     01/07/06         003     01/06/06     12/30/05             
      003     01/06/06                             
      503     01/05/06                             
      568     01/04/06                             
      002     01/03/06                             
      044     01/02/06                             
      632     01/01/06                             
      674     12/31/05                             
      003     12/30/05                             
      581     12/29/05                             
      318     12/28/05                             
      002     12/27/05                             
      410     12/26/05                             
      653     12/25/05             



        Amazing Grace's avatar - lion
        rainbow lake
        Canada
        Member #25177
        November 2, 2005
        10764 Posts
        Offline
        Posted: January 15, 2006, 9:40 pm - IP Logged

        If these are actual numbers entered here as drawn, they look like they follow my Predicatable Sequential Trending method!

          lottaloot's avatar - AvatarZ56
          Redford/MI
          United States
          Member #3396
          January 18, 2004
          4867 Posts
          Offline
          Posted: January 15, 2006, 10:12 pm - IP Logged

          Well, when you guys get this one, I will be all over it.

          I thought it would have to be in VBA code. Guess I was wrong.

          L ttaL   T

            winsumloosesum's avatar - Lottery-060.jpg
            Pennsylvania
            United States
            Member #2218
            September 1, 2003
            5387 Posts
            Offline
            Posted: January 16, 2006, 10:29 am - IP Logged

            Scott,

            I have an Excel Pivot Program that might help.

            All you do is use the drop down menu to pick the 3 digit number and the corresponding dates will appear next to it.

            I can send you the file for California Pick 3 if you like.

            All you would need to do is copy and paste your states drawings in the Drawings Tab.

             

              scott_one's avatar - abstract
              Lapeer, MI
              United States
              Member #9977
              December 29, 2004
              31 Posts
              Offline
              Posted: January 16, 2006, 9:45 pm - IP Logged

              Scott,

              I have an Excel Pivot Program that might help.

              All you do is use the drop down menu to pick the 3 digit number and the corresponding dates will appear next to it.

              I can send you the file for California Pick 3 if you like.

              All you would need to do is copy and paste your states drawings in the Drawings Tab.

               

              winsumloosesum,

              Thanks that would be great.

               scott_one@yahoo.com

                Avatar
                toms river
                United States
                Member #27817
                December 6, 2005
                32 Posts
                Offline
                Posted: January 16, 2006, 10:03 pm - IP Logged

                If these are actual numbers entered here as drawn, they look like they follow my Predicatable Sequential Trending method!

                it  does  not  work

                  scott_one's avatar - abstract
                  Lapeer, MI
                  United States
                  Member #9977
                  December 29, 2004
                  31 Posts
                  Offline
                  Posted: January 16, 2006, 10:38 pm - IP Logged

                  If these are actual numbers entered here as drawn, they look like they follow my Predicatable Sequential Trending method!

                  These are just numbers for an example.

                  These are actual numbers from the Michigan midday and evening for the last 2 weeks.

                  01/16/06

                  9

                  7

                  4

                  01/16/06

                  4

                  8

                  2

                  01/15/06

                  4

                  2

                  1

                  01/15/06

                  9

                  5

                  5

                  01/14/06

                  0

                  0

                  3

                  01/14/06

                  4

                  1

                  7

                  01/13/06

                  1

                  3

                  3

                  01/13/06

                  3

                  6

                  0

                  01/12/06

                  2

                  1

                  8

                  01/12/06

                  6

                  1

                  8

                  01/11/06

                  9

                  8

                  8

                  01/11/06

                  4

                  7

                  4

                  01/10/06

                  0

                  8

                  9

                  01/10/06

                  2

                  8

                  2

                  01/09/06

                  3

                  3

                  9

                  01/09/06

                  5

                  4

                  7

                  01/08/06

                  5

                  9

                  5

                  01/08/06

                  3

                  2

                  2

                  01/07/06

                  1

                  7

                  3

                  01/07/06

                  5

                  0

                  3

                  01/06/06

                  5

                  6

                  8

                  01/06/06

                  5

                  7

                  0

                  01/05/06

                  0

                  4

                  4

                  01/05/06

                  6

                  3

                  2

                  01/04/06

                  6

                  7

                  4

                  01/04/06

                  9

                  8

                  0

                  01/03/06

                  5

                  8

                  1

                  01/03/06

                  3

                  1

                  8