Hi guys,

Probably my best shot here to finish successfully my graduation with a study I am doing that I am now TOTALLY STUCK. Please read slowly as I will do my best to explain my needs:

In the Excel attached there are 27008 rows of random numbers from 0-36 (column B).
Each range of numbers is categorized as following:
0 = Color 2 1-6 = Color 3 7-12 = Color 4 13-18 = Color 1 19-24 = Color 5 25-30 = Color 6 31-36 = Color 7

What I need, in the first place, is to count is the number of sequences that only 2 random numbers of the same color appear one after another. Per example: Rows 2-3 4-5 20-24 23-24 etc.....
This is the easy part.
The "trigger" to stop counting would be a sequence where 3 or more numbers of the same color appear one after another. Per example: Rows 134-136.

So in the case that we take a sequence from row 2 to row 136 - the formula would count exactly 17 times that a group of 2 numbers of the same color appeared one after another until a group of 3 numbers of the same color one after another showed up and than the count stops and restarts.

The ultimate question of the study would be: out of 27008 rows, the longest sequence that 2 numbers of the same color appeared one after another until a 3 numbers one after another showed up is............. X

How do I achieve this? Really I have consulted my professors, computer engineers and many others, with 0 SUCCESS.
Please help me. Since I am no Excel programmer feel free to edit my excel and send back.

@@@THANK YOU SO MUCH@@@

Attached Files

Cell count.xlsx (454.1 KB, 11 views)