Linking numerical value occurrence to a date

**b_rianv** · 07-12-2014, 05:59 AM

I am trying to get some info for my lottery pick 6 to find out how long it has been since a lottery number hit with in a time frame of 6 months. This number can appear in any position with in a row BUT the Row that it is in corresponds to the date of that row with in a 6 month time period in my database.
Example, Lottery database
Cells A B C D E F G
1 7/27/2014 1 2 13 11 29 36
2 7/04/2014 7 15 21 32 33 53
3 6/25/2014 1 5 12 25 36 51
4 6/15/2014 4 10 17 21 22 49
4 5/30/2014 19 22 27 36 44 53
5 5/03/2014 1 2 3 14 25 46

Example, What I am trying to do for a reference Table
Last 6 months of Activity
Cells X Y Z
Number Last Date
9 1 7/27/2014
10 2 7/27/2014
11 3 5/03/2014
12 4 6/15/2014
13 5 6/25/2014

I am using xl 2010
Here is my code that I am working with [=IFERROR(LOOKUP(2,1/(L$2:N$31=$A2),$K$2:$K$31),"") ]. This code does not refer to the above examples it is from my workbook...

I would like for this to be able to go back for the last 6 months of drawings and also would like for the range of dates to be expandable so that when I update the drawings which I do on a weekly basis that I will not have to go in and adjust the formulas to accommodate the recently added cells. Any help that any one can or is willing to provide to me on this matter will be greatly appreciated and a thousand kudos in advance. Thank You,

**newdoverman** · 07-12-2014, 09:00 PM

I think that this does what you want. It finds the date of the last entry of the values from 1 to 49 within the matrix.

**b_rianv** · 07-12-2014, 10:30 PM

Thank you so much I think this is a WINNERRRR!!!!!!

**newdoverman** · 07-13-2014, 08:25 AM

Thank you for the feedback.

To increase the number of entries, expand the cell ranges in the formula to accommodate expansion as the formula that I gave you only covers the small matrix of the example.

**b_rianv** · 07-13-2014, 02:23 PM

Thank you for your help. I have input your formula in to my table to check my database. It looks like it is trying to do something but the only dates that come in to my table are this...1/0/00/, this is for every number 1-53. Any ideas??

**b_rianv** · 07-13-2014, 02:31 PM

Ok, I forgot to add, is this because I have empty cells? I don't believe so as I did a mock drawing to fill these cell and it still produced the same results...1/0/00. So I am a little lost on this one.

**newdoverman** · 07-13-2014, 03:00 PM

I suspect that the dates in your table are TEXT dates and not real dates or there is something wrong with the values entered in your table.

I will examine your workbook if you upload it with the errors that you are talking about. That way I will have a better chance to figure out what is going on.

Note: all dates in the workbook that I uploaded to you are sorted in order Oldest to newest. The formula is entered with Ctrl + Shift + Enter.

Here is your workbook with the formula expanded to accommodate numbers from 1 to 53 and up to row 100.

**b_rianv** · 07-13-2014, 03:33 PM

hello, I believe you might be right with dates. My dates in my workbook are oldest being at the bottom of the worksheet and the newest at the top. Could this be causing the problem?

**b_rianv** · 07-13-2014, 03:34 PM

Everything else looks great the right cells highlighted, I was able to adjust cell references within the formula you gave me, just this date thing.

**newdoverman** · 07-13-2014, 03:46 PM

The order of the dates will certainly affect the results. The formula is looking for the LAST entry of each number in each column (bottom of the column) in turn from left to right. When a value is found then the date in column A on that row is returned. In addition, inserting rows at the top to add new data breaks the formulae so you will not get correct results if you get results at all.

**b_rianv** · 07-13-2014, 03:53 PM

Well my friend so how to fix this ? This database is over 3,000 rows And I really don't have the time to redo this database

**b_rianv** · 07-13-2014, 04:15 PM

Ok, I changed "LARGE" to "SMALL" in the formula and dates came across BUT just like you said not correct. So now looking in to how to correct this.

**newdoverman** · 07-13-2014, 04:24 PM

The only way to fix this is to select your data and sort it into ascending date order. Changing the Large to Small doesn't fix the broken cell references that are introduced by the insertion of rows.

Right now, I can't see a way around this.

**newdoverman** · 07-13-2014, 05:41 PM

I think that I have a solution for you. This file uses a table for the data and to enter new data insert a row at the top of the data. The formula has been changed to use SMALL and a correction to the row counter.

**b_rianv** · 07-13-2014, 06:05 PM

Thank you for ALL your time in this matter. I am sure I will get this fixed, hell what can't excel do I'm sure I'm looking right at the solution. After dinner I will look at this other xls and let you know.

**newdoverman** · 07-13-2014, 07:21 PM

The last file that I sent to you uses the functionality of the of the table to handle the necessity of dynamic columns. I tried a few entries into the table (sorted with new entries at the top) and it seemed to work properly.

If you convert your data to a table, note the last row and change the ranges to go to that last row and make the other change to the row counter and it should work for you.

**b_rianv** · 07-13-2014, 07:36 PM

Everything has a date now, it seams that it is off by some rows. So lets say after inputting this formula (#1) has the last draw date of 4/30/14 which is row 27, if I count up to row 24 it's date is 5/10/2014 and that's it's last draw date in my database but it's coming across as 4/30/2014 in the table. This is with all numbers with there dates. But we at lease now have dates. Does this make any sense to you?

**newdoverman** · 07-13-2014, 08:00 PM

Is you data in a TABLE like the last post to you? If it is, what is the first row # under the headers for the columns and what is the cell address of the first formula?

You could try changing the -1 a larger value. Try -3 (replace all the -1 values or it won't work) and see what the result is.

If you can upload a copy of your workbook, it sure would be easier to diagnose the problems.

**b_rianv** · 07-13-2014, 08:11 PM

Yes a lot of these are off by just 3 rows, while a few numbers have a date BUT the do not exist with in the combination. I don't know how to upload my workbook or work sheet for that matter. If you can show me I will do it. I believe a wizard like you could see the problem .

**newdoverman** · 07-13-2014, 08:11 PM

I've tried to simulate where your data starts (row 24) and this seems to work.

**newdoverman** · 07-13-2014, 08:13 PM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Haseeb Avarakkan** · 07-13-2014, 08:20 PM

Hello,

I haven't gone through all the pasts. So if this is wrong answer, sorry for that.

If you are looking to return MAX date based on numbers, try this Array Formula in J24, then copy down.

=IFERROR(LARGE(IF($B$24:$G$30=I24,$A$24:$A$30),1),"")

**b_rianv** · 07-13-2014, 09:03 PM

Here you go sir. I have uploaded my whole workbook.Please go to Florida Lottery Database this database is almost done. As you know we are working on linking latest dates with numbers. You will see a table called "Last 6 months Activity" this is where I am applying this formula you have offered to me. As the other tables are already done by me. Please feel free to let me know what you think and help you can offer.

As for the other databases they are not done as I am focusing on this one "Florida Lottery Database" as it will be the template for the others. This is the last formula I have to do for my workbook and it has been a challenge to do this. I hope it can be done.

**b_rianv** · 07-14-2014, 06:11 AM

Good morning Gentleman,
The formula Mr.Haseeb gave me ...=IFERROR(LARGE(IF($B$24:$G$30=I24,$A$24:$A$30),1),"") as a array is very close. While it gives me the right dates with numbers it isn't counting the " last draw " of that number.

Example 1...his formula did this
#18 draw date of 5/14/2014 which is correct, but #18 was drawn again on 5/28/2014 and 6/21/2014 but has not been drawn as of current date of today 07/14/2014.

Now for numbers that were only drawn once with in my time line (6 MONTHS BACK)in my database the # and date match are correct. IF a number was drawn more than once with in my time line his formula only shows the first time drawn and not the last time drawn, Which is what I am looking for.

Example 2... "IF" #18 was drawn on.. 5/14/2014 .. 5/28/2014 .. 6/21/2014, "THEN" 6/21/2014 should show up on my table " NOT " 5/14/2014....Are you guys following me??

**b_rianv** · 07-14-2014, 11:59 AM

I'm starting to think maybe there needs to be some type of "offset" or "match" with this formula.......=IFERROR(LARGE(IF($B$24:$G$30=I24,$A$24:$A$30),1),""),cause I see where the number is drawn more then once the formula doesn't go to the next date for that number.

**newdoverman** · 07-14-2014, 12:13 PM

There is a problem because what works in my workbook doesn't work properly in yours. You have been adding data into a range that has formulae. Data entry should be in the hidden columns B:G. I don't see the point of columns I:N other than to sort the values into order.

I have created something that works well in my workbook but when copied into yours, doesn't work very well at all.

Here is the workbook that I have created which uses a table for data entry. To insert a new row, select A5:G5 right click and choose INSERT, TABLE ROWS ABOVE.

I wish that I had seen this workbook at the beginning as it would have saved a lot of work by using a different approach. As it is, I don't know why

**newdoverman** · 07-14-2014, 12:57 PM

I finally got a table to work in your workbook for columns A:G....deleted the empty column B and inserted a new column H to have a blank column to the right of the table.

To insert new rows, select A5:G5 right click choose INSERT, TABLE ROWS ABOVE. You can insert more than one row at a time but leaving empty rows in a table isn't a good practice. The insertion of rows doesn't seem to affect the dates next to the number values until new data is entered for those values.

Columns I:N appear to have no value other than to sort the values in the table from left to right. If you want to continue to use columns I:N in the manner that it is designed for, you will have to insert the same number of rows as inserted into the table and copy up the formulae to fill the new cells.

The date format of column A I hope will change to what you are used to. The format automatically changed to D/M/YYYY when I loaded the file on my computer.

**b_rianv** · 07-14-2014, 01:53 PM

Thank you sir, I will look at it tonight when I get home from work and let you know.

**b_rianv** · 07-14-2014, 07:33 PM

This is wonderful my friend!! Just what I am looking for, works nicely with everything else I have.

Now I am wondering. Can you either teach me how to do this OR Can you offer your services to do the other 2 database (Powerball and Mega Millions? But I don't have either of those finished yet.

**newdoverman** · 07-14-2014, 08:20 PM

Would you be interested in having a simplified presentation like this? This combines the table in the lower right with the table of numbers and dates and eliminates the ranking table and the table in the lower right that does a count of the numbers. Also, the columns beside the main table have been eliminated.

Would you find this a workable/useful/desirable setup for this and the other worksheets of a similar nature?

This arrangement puts all the data in one chart where everything can be seen at once.

**b_rianv** · 07-14-2014, 09:27 PM

I am speechless, what a wonderful job you have done to my basic layout!! Yes I would like the other 2 database (Powerball, Mega Millions) just like this. But please remember I am not done entering the past draws data which for Powerball goes back to 1997 I think and the same for Mega Millions. So I don't know how it would affect the counts because I like the most current draw date at the top.

So if you could please do the other databases this way. And please remember I add the latest draw date and combination at the top so I will have to add rows going down.

Thank you so much for your time and passion in addressing my issues.

**newdoverman** · 07-15-2014, 08:51 AM

Just a few things before I start that I need to know. For each of the other two lotteries, what is the number range covered by each? Is there anything different between them other than the number range that would affect the calculations?

The data for the past draws can be added after the worksheets are done, so a completed worksheet is not necessary.

These worksheets will calculate based on the total amount of data (the larger the dataset the more accurate the results) and not just the last 6 months. If you only want the last 6 months, when you insert rows at the top of the table, delete the same number of rows from the bottom of the table....simple and easy to do. Personally, I would keep all the data.

If you like, I could do one lottery and you could try and do the other in order to learn how this is done? The 3 lotteries are basically the same and using the first lottery, the layout and formulae could easily be converted to the other two.

Let me know the parameters of the lotteries and what you would like to do.

**b_rianv** · 07-15-2014, 10:58 AM

Ok, The number range for Powerball is 1-59 which are the first 5 balls drawn then the Powerball which is red the last one drawn is 1-35. So a drawing can look like this

1-2-3-4-5, 5 or 1-2-3-4-5, 7.

The Powerball the last ball drawn is a independent pool of balls that are drawn after the first 5 balls are drawn, so it is possible to have a Powerball the last ball drawn that matches the same number drawn in the first 5 balls up to 35 or not match any number drawn in the first 5 balls.

Mega Millions number range for the first 5 balls is 1-75 and the Mega Ball number range is 1-15 which is gold. The Mega Ball the last ball drawn is a independent pool of balls that are drawn after the first 5 balls are drawn, so it is possible to have a Mega ball the last ball drawn that matches the same number drawn in the first 5 balls up to 15 or not match any number drawn in the first 5 balls.

1-2-3-4-5, 5 or 1-2-3-4-5, 10

Yes I agree with you on keeping all my data in my databases so I don't want to have to delete any data as I enter a new row to add the new draw data. I would like to have all databases match for information like you did for Florida lottery, where I see the #, then last date drawn, count of that number to how many times it has been drawn, then it ranking overall against all other numbers. So can you fix the Florida database to include all data range in stead of 6 months?
I guess that would be it unless you have other things you could add to the databases that I have missed or think it my help me.

Thank you,
Brian

**b_rianv** · 07-15-2014, 11:10 AM

One more thing, do you think for the Powerball 1-35 and Mega Ball1-15 will they have to have a different table to show their information #,last time drawn, count of that number drawn, then ranking against all other numbers. because they don't count towards the main combinations drawn for data? If so please feel free to create these tables.

**newdoverman** · 07-15-2014, 04:10 PM

Here is you workbook with all three lotteries converted to table format. Insert new records by selecting the top row of data in the table (all columns) right click and choose INSERT, TABLE ROWS ABOVE. PowerBall numbers from 1-35 have their own table. Megapix numbers also have a table of their own. Both have the their statistics just like the main draw numbers. The Mega Millions worksheet has some numbers with x beside them in the Megaplier column. I left them as they were but they are not being counted in the table for those counts. If you change them to not having the x then they will be counted.

All statistics are calculated from the main tables. No other entries are necessary.

**b_rianv** · 07-15-2014, 06:32 PM

Ok my friend I just got home and I will look at it after supper and a shower and then reply later in the evening.

**b_rianv** · 07-15-2014, 09:03 PM

Thank you for your work on my book, I really don't know what to say. You took my basic workbook and turned it in to a power house. You are a "excel wizard" my friend! Again thank you
Brian

**newdoverman** · 07-15-2014, 09:26 PM

Thank you for the feedback. It is very nice to have one's work appreciated.

Thank you. I was glad to do it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**b_rianv** · 07-16-2014, 05:53 AM

Yes I will do that. Just one more question. Is there anyway to have this workbook highlight a row when I click on a cell? Example when I am looking at my All Activity table which you fixed/set up and I want to see where a number last draw date was and I click the number it would highlight that row, instead of manually looking for it?

Thank you again for your time and effort towards making this all possible.

**newdoverman** · 07-16-2014, 08:03 AM

That will take VBA which I am not at all familiar with. I suggest that you post the question in the VBA section where the experts in VBA "hang out".

**b_rianv** · 07-16-2014, 12:09 PM

Ok, thanks again for everything.....

**newdoverman** · 07-22-2014, 11:13 AM

This workbook has the odd/even column added.

**b_rianv** · 07-22-2014, 01:15 PM

Thank you, it looks great !! Now my book is completed.

**newdoverman** · 07-22-2014, 02:26 PM

Thank you for the feedback.

Good luck!

**b_rianv** · 07-23-2014, 05:48 AM

Good morning sir,

There is a little problem with the formula for the "even/odd" pattern. It doesn't update I have to go in and change the $A$:$G$ when I add a row to my table to input a new lottery draw combination...Then copy the formula down on my other table for "All Activity" so the OOEEOE shows up right. If not I get a #N/A when I add a new row.

=IF(ISEVEN(VLOOKUP(M9,$A7:$G174[/U][/B],2,0)),"E","O")&IF(ISEVEN(VLOOKUP(M9,$A7:$G174,3,0)),"E","O")&IF(ISEVEN(VLOOKUP(M9,$A7:$G174,4,0)),"E","O")&IF(ISEVEN(VLOOKUP(M9,$A7:$G174,5,0)),"E","O")&IF(ISEVEN(VLOOKUP(M9,$A7:$G174,6,0)),"E","O")&IF(ISEVEN(VLOOKUP(M9,$A7:$G174,7,0)),"E","O").

Can you tell my what to do to fix this?

Brian

**newdoverman** · 07-23-2014, 09:38 AM

As far as I can tell, this works.

**b_rianv** · 07-23-2014, 08:36 PM

Yes my friend we have a WINNER!!!! It is working perfectly.

Thank you

**newdoverman** · 07-23-2014, 09:38 PM

You're welcome.

Good luck.

Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Re: Linking numerical value occurrence to a date

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions