Help with excel codePrevious TopicNext Topic

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

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

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 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 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?

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

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

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

Enjoy, Pogo

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

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!!