Welcome Guest
Log In | Register )
You last visited January 21, 2017, 8:34 pm
All times shown are
Eastern Time (GMT-5:00)

Help with excel code

Topic closed. 9 replies. Last post 6 years ago by winsumloosesum.

Page 1 of 1
PrintE-mailLink
Avatar
New Member
New York
United States
Member #7355
September 30, 2004
19 Posts
Offline
Posted: July 25, 2010, 2:48 pm - IP Logged

I want to be able to search my spreadsheet for a number entered in an input box and copy all the results  (data from column E  through L ) of that row and the row immediately following  it, into a different section or sheet. Can anyone help me with the code for this.

thanks

    Pogo's avatar - wfl
    NC
    United States
    Member #29378
    January 1, 2006
    552 Posts
    Offline
    Posted: July 25, 2010, 3:34 pm - IP Logged

    I want to be able to search my spreadsheet for a number entered in an input box and copy all the results  (data from column E  through L ) of that row and the row immediately following  it, into a different section or sheet. Can anyone help me with the code for this.

    thanks

    Have you already started an excel sheet yet? If you have... PM me and send it - but before you do go to the cells you need help with and enter notes like search this number, in these rows/columns, place results here, etc. I'll help you as much as I can - Pogo

      Raven62's avatar - binary
      New Jersey
      United States
      Member #17843
      June 28, 2005
      51178 Posts
      Offline
      Posted: July 25, 2010, 3:40 pm - IP Logged

      I want to be able to search my spreadsheet for a number entered in an input box and copy all the results  (data from column E  through L ) of that row and the row immediately following  it, into a different section or sheet. Can anyone help me with the code for this.

      thanks

      It would be very helpful if you could provide an example of what you are trying to do.

      A mind once stretched by a new idea never returns to its original dimensions!

        Avatar
        New Member
        New York
        United States
        Member #7355
        September 30, 2004
        19 Posts
        Offline
        Posted: July 25, 2010, 4:02 pm - IP Logged

        I have a spreadsheet with all the past numbers of pick3. What I'm trying to do is this.

        for example last night 175 came out. I want to be able to be prompted to enter the previous drawing number  then  search column S for that number and  copy and paste in a different section of the sheet the row containing the searched number and the following row. I want to pick all the rows that usually follows 175.

         

        MON 19-Jul-10 7 0 4 704
        SUN 18-Jul-10 9 0 5 175
        SUN 18-Jul-10 1 3 8 138
        SAT 17-Jul-10 0 4 9 049
        SAT 17-Jul-10 6 1 3 613
        FRI 16-Jul-10 9 1 1 911
        FRI 16-Jul-10 6 6 4 664
        THU 15-Jul-10 2 1 5 175
        THU 15-Jul-10 0 0 0 000
        WED 14-Jul-10 6 9 8 698
        WED 14-Jul-10 9 4 1 941
        TUE 13-Jul-10 9 3 5 935
        TUE 13-Jul-10 3 9 0 390
        MON 12-Jul-10 7 7 8 175
        MON 12-Jul-10 8 0 5 805
        SUN 11-Jul-10 2 2 2 175
        SUN 11-Jul-10 9 7 3 973
        SAT 10-Jul-10 3 9 2 392

        MON 19-Jul-10 704

        SUN 18-Jul-10  175  <----

        SUN 18-Jul-10  138  <------

        SAT 17-Jul-10   049

        SAT 17-Jul-10   613

        FRI 16-Jul-10   911

        FRI 16-Jul-10   664

        THU 15-Jul-10  175  <-----

        THU 15-Jul-10   000  <------

        WED 14-Jul-10  698

        WED 14-Jul-10  941

        TUE 13-Jul-10   935

        TUE 13-Jul-10   390

        MON 12-Jul-10  175  <------

        MON 12-Jul-10 805   <------

        SUN 11-Jul-10 973

        SUN 11-Jul-10  392

        SAT 10-Jul-10  392

         

        THU 22-Dec-05 277
        THU 22-Dec-05 475
        WED 21-Dec-05 371
        WED 21-Dec-05 000
        TUE 20-Dec-05 341
        TUE 20-Dec-05 335
        MON 19-Dec-05 329
        MON 19-Dec-05 592
        SUN 18-Dec-05 235
        SUN 18-Dec-05 112
        SAT 17-Dec-05 281
        SAT 17-Dec-05 904
        FRI 16-Dec-05 810

        THU 22-Dec-05 277

        THU 22-Dec-05 175   <----

        WED 21-Dec-05 371   <-----

        WED 21-Dec-05 000

         

        the arrows would be the rows copied to the new location.

          Avatar

          United States
          Member #41846
          June 23, 2006
          460 Posts
          Offline
          Posted: July 26, 2010, 6:26 am - IP Logged

          I have a spreadsheet with all the past numbers of pick3. What I'm trying to do is this.

          for example last night 175 came out. I want to be able to be prompted to enter the previous drawing number  then  search column S for that number and  copy and paste in a different section of the sheet the row containing the searched number and the following row. I want to pick all the rows that usually follows 175.

           

          MON 19-Jul-10 7 0 4 704
          SUN 18-Jul-10 9 0 5 175
          SUN 18-Jul-10 1 3 8 138
          SAT 17-Jul-10 0 4 9 049
          SAT 17-Jul-10 6 1 3 613
          FRI 16-Jul-10 9 1 1 911
          FRI 16-Jul-10 6 6 4 664
          THU 15-Jul-10 2 1 5 175
          THU 15-Jul-10 0 0 0 000
          WED 14-Jul-10 6 9 8 698
          WED 14-Jul-10 9 4 1 941
          TUE 13-Jul-10 9 3 5 935
          TUE 13-Jul-10 3 9 0 390
          MON 12-Jul-10 7 7 8 175
          MON 12-Jul-10 8 0 5 805
          SUN 11-Jul-10 2 2 2 175
          SUN 11-Jul-10 9 7 3 973
          SAT 10-Jul-10 3 9 2 392

          MON 19-Jul-10 704

          SUN 18-Jul-10  175  <----

          SUN 18-Jul-10  138  <------

          SAT 17-Jul-10   049

          SAT 17-Jul-10   613

          FRI 16-Jul-10   911

          FRI 16-Jul-10   664

          THU 15-Jul-10  175  <-----

          THU 15-Jul-10   000  <------

          WED 14-Jul-10  698

          WED 14-Jul-10  941

          TUE 13-Jul-10   935

          TUE 13-Jul-10   390

          MON 12-Jul-10  175  <------

          MON 12-Jul-10 805   <------

          SUN 11-Jul-10 973

          SUN 11-Jul-10  392

          SAT 10-Jul-10  392

           

          THU 22-Dec-05 277
          THU 22-Dec-05 475
          WED 21-Dec-05 371
          WED 21-Dec-05 000
          TUE 20-Dec-05 341
          TUE 20-Dec-05 335
          MON 19-Dec-05 329
          MON 19-Dec-05 592
          SUN 18-Dec-05 235
          SUN 18-Dec-05 112
          SAT 17-Dec-05 281
          SAT 17-Dec-05 904
          FRI 16-Dec-05 810

          THU 22-Dec-05 277

          THU 22-Dec-05 175   <----

          WED 21-Dec-05 371   <-----

          WED 21-Dec-05 000

           

          the arrows would be the rows copied to the new location.

          This would be fairly easy with VBA.  if interested tell me where your input info will be located and where you want the output to go. do you only want the # following the target # or do you want the date? you said your #'s are in col S, but  what row?  where is the date? a guess would be col R but do not like to make assumptions.

          are you familiar with VBA?

            Raven62's avatar - binary
            New Jersey
            United States
            Member #17843
            June 28, 2005
            51178 Posts
            Offline
            Posted: July 26, 2010, 9:36 am - IP Logged

            I have a spreadsheet with all the past numbers of pick3. What I'm trying to do is this.

            for example last night 175 came out. I want to be able to be prompted to enter the previous drawing number  then  search column S for that number and  copy and paste in a different section of the sheet the row containing the searched number and the following row. I want to pick all the rows that usually follows 175.

             

            MON 19-Jul-10 7 0 4 704
            SUN 18-Jul-10 9 0 5 175
            SUN 18-Jul-10 1 3 8 138
            SAT 17-Jul-10 0 4 9 049
            SAT 17-Jul-10 6 1 3 613
            FRI 16-Jul-10 9 1 1 911
            FRI 16-Jul-10 6 6 4 664
            THU 15-Jul-10 2 1 5 175
            THU 15-Jul-10 0 0 0 000
            WED 14-Jul-10 6 9 8 698
            WED 14-Jul-10 9 4 1 941
            TUE 13-Jul-10 9 3 5 935
            TUE 13-Jul-10 3 9 0 390
            MON 12-Jul-10 7 7 8 175
            MON 12-Jul-10 8 0 5 805
            SUN 11-Jul-10 2 2 2 175
            SUN 11-Jul-10 9 7 3 973
            SAT 10-Jul-10 3 9 2 392

            MON 19-Jul-10 704

            SUN 18-Jul-10  175  <----

            SUN 18-Jul-10  138  <------

            SAT 17-Jul-10   049

            SAT 17-Jul-10   613

            FRI 16-Jul-10   911

            FRI 16-Jul-10   664

            THU 15-Jul-10  175  <-----

            THU 15-Jul-10   000  <------

            WED 14-Jul-10  698

            WED 14-Jul-10  941

            TUE 13-Jul-10   935

            TUE 13-Jul-10   390

            MON 12-Jul-10  175  <------

            MON 12-Jul-10 805   <------

            SUN 11-Jul-10 973

            SUN 11-Jul-10  392

            SAT 10-Jul-10  392

             

            THU 22-Dec-05 277
            THU 22-Dec-05 475
            WED 21-Dec-05 371
            WED 21-Dec-05 000
            TUE 20-Dec-05 341
            TUE 20-Dec-05 335
            MON 19-Dec-05 329
            MON 19-Dec-05 592
            SUN 18-Dec-05 235
            SUN 18-Dec-05 112
            SAT 17-Dec-05 281
            SAT 17-Dec-05 904
            FRI 16-Dec-05 810

            THU 22-Dec-05 277

            THU 22-Dec-05 175   <----

            WED 21-Dec-05 371   <-----

            WED 21-Dec-05 000

             

            the arrows would be the rows copied to the new location.

            What order is your file in? (newest to oldest/oldest to newest)

            In your example 704 would follow 175 and 138 would precede 175.

            Cell E1=175

            Cell E3 =IF($C3=$E$1,$C2," ") copy & paste to the rest of the column

            A mind once stretched by a new idea never returns to its original dimensions!


              United States
              Member #79057
              August 26, 2009
              70 Posts
              Offline
              Posted: July 26, 2010, 1:27 pm - IP Logged

              I would write and if statement in the cell next to the three digits - using your example:

              cell a1                               B1                                     C1

              a2 704                             =trunc(a2/100)

              a3 175                            =trunc(a3/100)                  =if(b2=1,1,if(b3=1,1,0))

              a4 138                            =trunc(a4/100)                  =if(b3=1,1,if(b4=1,1,0))

              a5 049                            =trunc(a5/100)                   =if(b4=1,1,if(b5=1,1,0))

               

              Okay - so b2 cell takes 704 and divides by 100 and chops off anything after the decimal - answer 7

              b3 - answer 1  

              b4 answer 1

              b5 answer 0

              Then the c column looks to see if either that row or the row previously began with 1 ... if one of them does it makes a one in row c.

              From there just use a auto filter to filter out anything but ones in column c and then copy and paste everything left to where ever you want to take your data. 

               

              hope that helps

                Pogo's avatar - wfl
                NC
                United States
                Member #29378
                January 1, 2006
                552 Posts
                Offline
                Posted: July 29, 2010, 5:05 pm - IP Logged

                Pick3 one is done here: http://lottostop.tripod.com/

                Enjoy, Pogo Cheers

                BTW, Pick4 one - I'm going to try & get this one up tonight. Also, I'm going to try & update both to hold approximately 1000 past drawings - Stay Tuned!

                  Pogo's avatar - wfl
                  NC
                  United States
                  Member #29378
                  January 1, 2006
                  552 Posts
                  Offline
                  Posted: July 29, 2010, 9:50 pm - IP Logged

                  Pick3 one is done here: http://lottostop.tripod.com/

                  Enjoy, Pogo Cheers

                  BTW, Pick4 one - I'm going to try & get this one up tonight. Also, I'm going to try & update both to hold approximately 1000 past drawings - Stay Tuned!

                  Both Pick3 & Pick4 "Followers" Systems are available @: http://lottostop.tripod.com/

                  Both systems also include the ability to store & follow on the last 1000 draws.

                  Enjoy, Pogo See Ya!

                    winsumloosesum's avatar - Lottery-060.jpg
                    Pennsylvania
                    United States
                    Member #2218
                    September 1, 2003
                    5396 Posts
                    Offline
                    Posted: July 31, 2010, 10:02 am - IP Logged

                    I have a spreadsheet with all the past numbers of pick3. What I'm trying to do is this.

                    for example last night 175 came out. I want to be able to be prompted to enter the previous drawing number  then  search column S for that number and  copy and paste in a different section of the sheet the row containing the searched number and the following row. I want to pick all the rows that usually follows 175.

                     

                    MON 19-Jul-10 7 0 4 704
                    SUN 18-Jul-10 9 0 5 175
                    SUN 18-Jul-10 1 3 8 138
                    SAT 17-Jul-10 0 4 9 049
                    SAT 17-Jul-10 6 1 3 613
                    FRI 16-Jul-10 9 1 1 911
                    FRI 16-Jul-10 6 6 4 664
                    THU 15-Jul-10 2 1 5 175
                    THU 15-Jul-10 0 0 0 000
                    WED 14-Jul-10 6 9 8 698
                    WED 14-Jul-10 9 4 1 941
                    TUE 13-Jul-10 9 3 5 935
                    TUE 13-Jul-10 3 9 0 390
                    MON 12-Jul-10 7 7 8 175
                    MON 12-Jul-10 8 0 5 805
                    SUN 11-Jul-10 2 2 2 175
                    SUN 11-Jul-10 9 7 3 973
                    SAT 10-Jul-10 3 9 2 392

                    MON 19-Jul-10 704

                    SUN 18-Jul-10  175  <----

                    SUN 18-Jul-10  138  <------

                    SAT 17-Jul-10   049

                    SAT 17-Jul-10   613

                    FRI 16-Jul-10   911

                    FRI 16-Jul-10   664

                    THU 15-Jul-10  175  <-----

                    THU 15-Jul-10   000  <------

                    WED 14-Jul-10  698

                    WED 14-Jul-10  941

                    TUE 13-Jul-10   935

                    TUE 13-Jul-10   390

                    MON 12-Jul-10  175  <------

                    MON 12-Jul-10 805   <------

                    SUN 11-Jul-10 973

                    SUN 11-Jul-10  392

                    SAT 10-Jul-10  392

                     

                    THU 22-Dec-05 277
                    THU 22-Dec-05 475
                    WED 21-Dec-05 371
                    WED 21-Dec-05 000
                    TUE 20-Dec-05 341
                    TUE 20-Dec-05 335
                    MON 19-Dec-05 329
                    MON 19-Dec-05 592
                    SUN 18-Dec-05 235
                    SUN 18-Dec-05 112
                    SAT 17-Dec-05 281
                    SAT 17-Dec-05 904
                    FRI 16-Dec-05 810

                    THU 22-Dec-05 277

                    THU 22-Dec-05 175   <----

                    WED 21-Dec-05 371   <-----

                    WED 21-Dec-05 000

                     

                    the arrows would be the rows copied to the new location.

                    JM,

                    Not sure if this is what you wanted.

                    I created an Excel file foe New York Pick 3 game.  There are tabs for Midday, Evening, and Combined draws.  You can add new drawings by clicking the Toolbars in each of the Tabs.

                    I did not include the entire history for the New York Pick 4 game.  If you want to provide it to me I will update the Midday, Evening, and Combined draws.

                    Here is the download link to the Excel file

                    http://www.box.net/shared/n1y1qv2qup

                    In addition to the Midday, Evening, and Combined drawing history tabs there are 3 additional tabs labeled Midday Followers, Evening Followers, and Combined Followers.

                    In the Tab labeled "MiddayFollowers" the total number of Midday Drawings are in cell B2.  The last Midday drawing is in cell B8.  You can enter any combination in cell B10.  The relsults will appear in cell D10.  As you can see in the last 1481 Midday drawings when the combination 905 was drawn only 1 combination followed and that was 440 (in cell D10).  Same for Evening and Combined Followers Tabs.

                    As I mentioned earlier if you need more draws let me know and I will add them for you.

                    Good Luck!!