Welcome Guest
You last visited August 20, 2017, 11:08 am
All times shown are
Eastern Time (GMT-5:00)

# Excel Formula

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

 Page 1 of 1
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.

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.

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

rainbow lake
Member #25177
November 2, 2005
10765 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!

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

Pennsylvania
United States
Member #2218
September 1, 2003
5573 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.

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.

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

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

 Page 1 of 1