Counting numbers that appear in succession over different columns

  • 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!

  • Re: Counting numbers that appear in succession over different columns


    This may not be quite what you are looking for, but I do have a sheet that should work for you. Please note the use of the {} around the formulas in the J row. This formula uses the array formula which can be done by pressing ctrl+shift+enter after typing in the formula. the {} does fill with the formula. You can hide the H column if you wish.ozgrid.com/forum/core/index.php?attachment/69289/

  • Re: Counting numbers that appear in succession over different columns


    Another way with data in A1:E13.


    With this array-entered formula in C16 as in the below and filled down

    Code
    1. =INDEX($A$1:$E$1,,MAX(IF(B16=$A$2:$E$13,COLUMN($A:$E)-MIN(COLUMN($A:$E))+1)))


    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)

    And then this array-entered formula in D16 and filled across.

    Code
    1. =MAX(FREQUENCY(IF(D$15=$C$16:$C$35,ROW($16:$35)),IF(D$15<>$C$16:$C$35,ROW($16:$35))))



    [TABLE="class: grid"]

    [tr]


    [td]

    Row\Col

    [/td]


    [td]


    A


    [/td]


    [td]


    B


    [/td]


    [td]


    C


    [/td]


    [td]


    D


    [/td]


    [td]


    E


    [/td]


    [td]


    F


    [/td]


    [td]


    G


    [/td]


    [td]


    H


    [/td]


    [/tr]


    [tr]


    [td]


    1


    [/td]


    [td]


    A


    [/td]


    [td]


    B


    [/td]


    [td]


    C


    [/td]


    [td]


    D


    [/td]


    [td]


    E


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    2


    [/td]


    [td]


    1


    [/td]


    [td]


    2


    [/td]


    [td]


    3


    [/td]


    [td]


    37


    [/td]


    [td]


    38


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    3


    [/td]


    [td]


    4


    [/td]


    [td]


    5


    [/td]


    [td]


    6


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    4


    [/td]


    [td]


    7


    [/td]


    [td]


    8


    [/td]


    [td]


    9


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    5


    [/td]


    [td]


    10


    [/td]


    [td]


    11


    [/td]


    [td]


    12


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    6


    [/td]


    [td]


    13


    [/td]


    [td]


    14


    [/td]


    [td]


    15


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    7


    [/td]


    [td]


    16


    [/td]


    [td]


    17


    [/td]


    [td]


    18


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    8


    [/td]


    [td]


    19


    [/td]


    [td]


    20


    [/td]


    [td]


    21


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    9


    [/td]


    [td]


    22


    [/td]


    [td]


    23


    [/td]


    [td]


    24


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    10


    [/td]


    [td]


    25


    [/td]


    [td]


    26


    [/td]


    [td]


    27


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    11


    [/td]


    [td]


    28


    [/td]


    [td]


    29


    [/td]


    [td]


    30


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    12


    [/td]


    [td]


    31


    [/td]


    [td]


    32


    [/td]


    [td]


    33


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    13


    [/td]


    [td]


    34


    [/td]


    [td]


    35


    [/td]


    [td]


    36


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    14


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    15


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]


    A


    [/td]


    [td]


    B


    [/td]


    [td]


    C


    [/td]


    [td]


    D


    [/td]


    [td]


    E


    [/td]


    [/tr]


    [tr]


    [td]


    16


    [/td]


    [td]

    Row 1:

    [/td]


    [td]


    6


    [/td]


    [td]


    C


    [/td]


    [td]


    3


    [/td]


    [td]


    2


    [/td]


    [td]


    4


    [/td]


    [td]


    0


    [/td]


    [td]


    1


    [/td]


    [/tr]


    [tr]


    [td]


    17


    [/td]


    [td]

    Row 2:

    [/td]


    [td]


    7


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    18


    [/td]


    [td]

    Row 3:

    [/td]


    [td]


    2


    [/td]


    [td]


    B


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    19


    [/td]


    [td]

    Row 4:

    [/td]


    [td]


    34


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    20


    [/td]


    [td]

    Row 5:

    [/td]


    [td]


    15


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    21


    [/td]


    [td]

    Row 6:

    [/td]


    [td]


    36


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    22


    [/td]


    [td]

    Row 7:

    [/td]


    [td]


    21


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    23


    [/td]


    [td]

    Row 8:

    [/td]


    [td]


    12


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    24


    [/td]


    [td]

    Row 9:

    [/td]


    [td]


    38


    [/td]


    [td]


    E


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    25


    [/td]


    [td]

    Row 10:

    [/td]


    [td]


    33


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    26


    [/td]


    [td]

    Row 11:

    [/td]


    [td]


    1


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    27


    [/td]


    [td]

    Row 12:

    [/td]


    [td]


    7


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    28


    [/td]


    [td]

    Row 13:

    [/td]


    [td]


    4


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    29


    [/td]


    [td]

    Row 14:

    [/td]


    [td]


    18


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    30


    [/td]


    [td]

    Row 15:

    [/td]


    [td]


    15


    [/td]


    [td]


    C


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    31


    [/td]


    [td]

    Row 16:

    [/td]


    [td]


    28


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    32


    [/td]


    [td]

    Row 17:

    [/td]


    [td]


    29


    [/td]


    [td]


    B


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    33


    [/td]


    [td]

    Row 18:

    [/td]


    [td]


    23


    [/td]


    [td]


    B


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    34


    [/td]


    [td]

    Row 19:

    [/td]


    [td]


    4


    [/td]


    [td]


    A


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [tr]


    [td]


    35


    [/td]


    [td]

    Row 20:

    [/td]


    [td]


    26


    [/td]


    [td]


    B


    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [td]

    [/td]


    [/tr]


    [/TABLE]