Roulette analysis

[makinmomb]

These are 53 numbers played in a roulette game that has 36 numbers including a zero

I want to pull what numbers came after a particular number e.g 21
Those were expected numbers manually plugged after using CTRL F

Formula below 21 drag down , formula to read 2500 rows

[makinmomb]

If the formula reads at 6666 rows it is okay too

[MrShorty]

Is it necessary to use a formula (or a sequence of formulas)? My first thought was to use a pivot table. I:
1) Added a column to the source data to show the "followed by" number. A simple formula like =A3 in B2.
2) Created a pivot table where:
2a) Column A (the draw number) was used as the row labels.
2b) Column B (the followed by number) was used as the column labels.
2c) Count of column B (the followed by number) was used as the value field.
3) The pivot table shows all numbers and how often each number followed another number. In order to see, like your example, how many times each number followed 21, I select the row labels drop down and filter the pivot table by the number 21.

Would that kind of approach be allowed?

[makinmomb]

Lets see how it looks , can you paste the file here please

[makinmomb]

Meanwhile anyone who has a formula to drag down below where 21 is types in yellow

[samba_ravi]

C2=IFERROR(INDEX($A$1:$A$53,SMALL(INDEX(($A$1:$A$53<>$C$1)*10^10+ROW($A$1:$A$53)+1,0),ROWS(C$2:C2))),"")

Please Login or Register  to view this content.

Try this and drag down

[makinmomb]

Samba lovely , thank you