|Posted: August 21, 2015, 3:22 pm - IP Logged|
Depending on Your data source there are several ways to do this. For CA Fantasy 5 I have used this approach.
Dates (A), Game# (B) and Picks (C-G) are listed in the first 6 columns in the second row.
in the next 39 (H-AT) columns (top row) enter 1 - 39
under each number enter the following formula. =if(countif($C3:$G3,H$1)=1,+$B3,-1)
This formula places the game number in the cell if the number in line 1 was in the set drawn and -1 if it is not.
Copy the formula in all of the cells under the 1-39 numbers for as many games as you want to go back.
To Process this Copy Columns H-AT onto a separate worksheet in the work book and paste as values.
You can then sort each column in descending order and get rid of all of the -1 values with find and replace.
Highlight all of the cells in this new block that contains all of the numbers and games and name it "_Game<snip>" or anything you want.
Now you have the ability to access this data using a vlookup function
For example: For number 1 The formula =vlookup(1,_Game<snip>,2) would give you the game# at the second cell under 1. You can create a list of the hits for all of the numbers by using a cell addresses instead of 1 and 2.
For example: to look again at 1 Enter 1 in the cell at the top of the column you want to place the lookup values. I you do this on s separate worksheet in the workbook you could enter 1-39 in cells (B1:AN1). In Column A starting in Row 2 enter 1-20 in increasing order down the column ( enter more if you want to look back farther. In cell B2 inter the formula =vlookup(B$1,__Game<snip>,$A2)-vlookup(B$1,_Game<snip>,$A3).
This gives you the Skip between the times the number is picked. You can then use a Filter on the Column to select any value or calculated value using the filters criteria.
If the vlookup stuff throws you. Insert a column between each number in the new block next to the first game under a number enter a formula to add te adjacent cell minus the next cell down to get the skips between picks. You can then use filters with values and ranges of values to select the the skips in the column
This probably seams like a lot of work; but once you do it it is easy to update with macros and the data can be used in any table or formula you want. Options are many
Good Luck. I can't say I did not have any typos in the formulas above so if it does not work let me know and I will help. I have been doing this with fantasy 5 for a while and could give you some other examples of how I use the data if you are interested.