Data Cruncher
New member
- Local time
- Tomorrow, 08:21
- Joined
- May 15, 2007
- Messages
- 7
Hello,
I have a two similar lottery result sheets with columns as follows:-
Sheet 1
A1,B1,C1,D1,E1,F1,G1,H1,J1
Draw,DrawDate, #1,#2,#3,#4,#5,#6,Supp1,Supp2
Below these I have data at present down to row 1428
To the right of the data I have a split table with the following Headings in Cells L1-O1:-
# Main # Drawn Supp # Drawn Main Last Drawn
With the following data ranging from L2-O23
1 198 56
2 183 65
3 209 46
4 179 58
5 215 51
6 190 55
7 211 65
8 213 67
9 203 51
10 172 55
11 205 60
12 207 47
13 188 64
14 176 65
15 197 61
16 189 46
17 177 62
18 202 45
19 212 52
20 200 58
21 187 59
22 206 46
and
Q1,R1,S1,T1
# Main # Drawn Supp # Last Drawn
(cont.) (cont.) Drawn (cont.) (cont.)
23 192 75
24 189 54
25 215 56
26 191 56
27 186 46
28 175 64
29 191 57
30 168 50
31 190 55
32 190 58
33 197 57
34 191 59
35 197 59
36 208 51
37 197 42
38 191 46
39 176 62
40 206 60
41 173 50
42 167 51
43 149 58
44 151 53
45 153 55
with the data ranging from Q2-T24
[The table was split so I could see all the calculations and the last series of draw results when using a split screen].
I am using the COUNTIF function to return the number of times each of the drawn numbers appears in the C2-H1428 range for the Main #s, and I2-J1428 for the Supps, with conditional formatting to show the most frequently drawn numbers in either case, which is helpful for selecting numbers in future draws. So far, so good.
Can anyone tell me how to get Excel to search the source data to find the last time each of these 45 numbers appears in columns C-H for the Main #, and return the value from Column 2, corresponding to the row in which that number last appears? [I'm not interested in when the numbers were last drawn as Supps].
The lowest numbers will naturally occur in the first few columns, while the highest numbers will naturally occur in the last few columns, except where a series of low or high numbers is drawn, so 6 could appear in column H and 40 could appear in column C.
For example:-
# Main # Supp Last Drawn [Which is in]
1 198 56 17/03/2007 [C1418]
2 183 65 28/04/2007 [C1424]
3 209 46 05/05/2007 [C1425]
...
15 197 61 19/05/2007 [C1427
16 189 46 17/02/2007 [F1414]
17 177 62 19/05/2007 [D1427]
...
43 149 58 07/04/2007 [G1421]
44 151 53 03/03/2007 [H1416]
45 153 55 14/04/2007 [H1422]
At present, I can filter for specific numbers a column at a time, but this is highly inefficient and with each draw all the results would need to be recalculated, so I've not even started to record this information.
In Sheet 2, there is only one Supp #, so the data ranges are reduced by 1 column, but the range of numbers drawn includes 1-45 in both the Main #s and the Supp # columns.
I hope this has been fully explained and set out to make it easy for suggestions/answers to be constructed - there's nothing worse than vague references to data you can't see!
Many thanks,
Data Cruncher
Victoria, Australia
I have a two similar lottery result sheets with columns as follows:-
Sheet 1
A1,B1,C1,D1,E1,F1,G1,H1,J1
Draw,DrawDate, #1,#2,#3,#4,#5,#6,Supp1,Supp2
Below these I have data at present down to row 1428
To the right of the data I have a split table with the following Headings in Cells L1-O1:-
# Main # Drawn Supp # Drawn Main Last Drawn
With the following data ranging from L2-O23
1 198 56
2 183 65
3 209 46
4 179 58
5 215 51
6 190 55
7 211 65
8 213 67
9 203 51
10 172 55
11 205 60
12 207 47
13 188 64
14 176 65
15 197 61
16 189 46
17 177 62
18 202 45
19 212 52
20 200 58
21 187 59
22 206 46
and
Q1,R1,S1,T1
# Main # Drawn Supp # Last Drawn
(cont.) (cont.) Drawn (cont.) (cont.)
23 192 75
24 189 54
25 215 56
26 191 56
27 186 46
28 175 64
29 191 57
30 168 50
31 190 55
32 190 58
33 197 57
34 191 59
35 197 59
36 208 51
37 197 42
38 191 46
39 176 62
40 206 60
41 173 50
42 167 51
43 149 58
44 151 53
45 153 55
with the data ranging from Q2-T24
[The table was split so I could see all the calculations and the last series of draw results when using a split screen].
I am using the COUNTIF function to return the number of times each of the drawn numbers appears in the C2-H1428 range for the Main #s, and I2-J1428 for the Supps, with conditional formatting to show the most frequently drawn numbers in either case, which is helpful for selecting numbers in future draws. So far, so good.
Can anyone tell me how to get Excel to search the source data to find the last time each of these 45 numbers appears in columns C-H for the Main #, and return the value from Column 2, corresponding to the row in which that number last appears? [I'm not interested in when the numbers were last drawn as Supps].
The lowest numbers will naturally occur in the first few columns, while the highest numbers will naturally occur in the last few columns, except where a series of low or high numbers is drawn, so 6 could appear in column H and 40 could appear in column C.
For example:-
# Main # Supp Last Drawn [Which is in]
1 198 56 17/03/2007 [C1418]
2 183 65 28/04/2007 [C1424]
3 209 46 05/05/2007 [C1425]
...
15 197 61 19/05/2007 [C1427
16 189 46 17/02/2007 [F1414]
17 177 62 19/05/2007 [D1427]
...
43 149 58 07/04/2007 [G1421]
44 151 53 03/03/2007 [H1416]
45 153 55 14/04/2007 [H1422]
At present, I can filter for specific numbers a column at a time, but this is highly inefficient and with each draw all the results would need to be recalculated, so I've not even started to record this information.
In Sheet 2, there is only one Supp #, so the data ranges are reduced by 1 column, but the range of numbers drawn includes 1-45 in both the Main #s and the Supp # columns.
I hope this has been fully explained and set out to make it easy for suggestions/answers to be constructed - there's nothing worse than vague references to data you can't see!
Many thanks,
Data Cruncher
Victoria, Australia
