I have generated numbers in the entire of column G which is just over 1million cells. The numbers generated are in the range of a roulette wheel (Numbers 1-38, with 37 and 38 being the 0 and 00 respectively)
I have the spreadsheet set out as the roulette grid is laid out, which is as follows (note, a roulette grid is only 3 columns of numbers, column D and E are for 0 and 00)
A erfB erfeCrfer DererE erfF erferfeeerfG
1 erf2 rferf3 err37 ef38 erferefGenerated Numbers
4 erf5 erf f6
7 rfe8 erfe9
10 e11 erf12
13 e14 erf15
16 e17 erf18
19 e20 erf21
22e 23 erf24
25e 26 erf27
28 e29 erf30
31 e32 erf33
34 e35 erf36
What I need, is a formula that will show me the amount of times a number from the same column, either A,B,C,D,E that show up in succession.
For example: If we take the number set:
Row 1: 6 (Which lies in Column C from above)
Row 2: 7 (A)
Row 3: 2 (B)
Row 4: 34 (A)
Row 5: 15 (C)\
Row 6: 36 (C) \
Row 7: 21 (C) / 4 in succession
Row 8: 12 (C)/
Row 9: 38 (E)
Row 10: 33 (C)
Row 11: 1 (A)\
Row 12: 7 (A) | 3 in succession
Row 13: 4 (A)/
Row 14: 18 (C)\
Row 15: 15 (C)/ 2 in succession
Row 16: 28 (A)
Row 17: 29 (B)\ 2 in succession
Row 18: 23 (B)/
Row 19: 4 (A)
Row 20: 26 (B)
I'd like values to appear in the following fashion:
1 in succession: 9 (Row 1,2,3,4,9,10,16,19,20)
2 in succession: 2 (Row 14-15, 17-18)
3 in succession: 1 (Row 11-13)
4 in succession: 1 (Row 5-8)
So on...
I can imagine there being some problems arising with values being accounted for more than they actually appear.
For example, from Rows 11-13, being counted as 2 successive numbers from rows 11-12 and 12-13 where it should only be counted as one lot of 3 successive numbers.
I know this is may be very difficult and I might not have explained it very clearly, but am more than happy to add information if needed.
Thank you in advance!