Calculate recurring instances between cell count - using trigger

[sfarad]

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.

Here is a dl link
https://www77.zippyshare.com/v/KeZPfvd2/file.html

@@@THANK YOU SO MUCH@@@

 

[nemmi69]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

I cannot get the download

 

[sfarad]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

https://files.fm/u/akz73qz2

Try here

 

[Fluff]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

As this is a "homework" question, it would help if you could show what you have tried.
The majority of members here will happily give tips & advice on how to get the desired result. But please do not expect us to do your homework for you.

 

[sfarad]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

As this is a "homework" question, it would help if you could show what you have tried.
The majority of members here will happily give tips & advice on how to get the desired result. But please do not expect us to do your homework for you.

Dear Fluff - thank you for your kind response.
Just to be honest and clear, I do not need to present this excel as backup so it won't count as you guys doing the work for me. I could count the sequences I need manually but it would take me some hours. Inasmuch, I also look forward to LEARN and improve my excel skills, hence the callout for help.

As per your proposal - sounds fantastic.
In 2 weeks, the only thing I got through is a way to count a sequence in a row that includes a group of numbers one after another. In other words, in over 30K numbers, how many times would a two numbers of a same color would appear in a sequence such as:
COLOR2-COLOR2-COLOR3-COLOR3-COLOR1-COLOR1-COLOR2-COLOR2
(see attached link: https://ufile.io/3ay9n)

BUT THIS GETS ME NOWHERE. As this counts a sequence that occurs in a row.
And as I said what I need doesn't necessarily need to occur in a row, as long as it is in a range of a 3 numbers of the same color in a row limits. It could appear like this
1-1-1(three in a row of the same color-upper limit of the range)-2-16-32-35-36(two same color in a row numbers)-5-28-4-14-16(again two same color numbers in a row)-35-35-33(three in a row of the same color-lower limit and than the count begins again).
I explained it best in the first message.

Please find the best way you can help me. PLEASE

 

[Macropod]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

Cross-posted at: www.msofficeforums.com/excel-progra...rring-instances-between-cell-count-using.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[nemmi69]

Re: Calculate recurring instances between cell count - using trigger - URGENT HELP

Before I start, So, do you want just to find the highest reoccurrence, what it is and where it occurs?

 

[nemmi69]

This counts all pairs up to the 1st three group then stops.

Option Explicit
Public WbName As Workbook
Public WsName1 As Worksheet
Dim Cloop As Long
Dim LastRowNo As Long
Dim CurVal As Integer
Dim NextVal As Integer
Dim CurMatchCount As Integer
Dim StartCurMatchCount As Integer
Dim EndCurMatchCount As Integer
Dim CountPair As Integer
Dim PairFnd As Boolean


Sub FindHighestRepeat()
Set WbName = ThisWorkbook
Windows(ThisWorkbook.Name).Activate
Set WsName1 = WbName.Sheets(1) '("DORTMUND")


CurVal = 0
NextVal = 0
CurMatchCount = 0
StartCurMatchCount = 0
EndCurMatchCount = 0
CountPair = 0
PairFnd = False
WsName1.Range("I2").Value = 0
WsName1.Range("K2").Value = ""


LastRowNo = WsName1.Range("C65536").End(xlUp).Row


For Cloop = 2 To LastRowNo
    If CurVal > 0 Then 'just started so no value
        NextVal = WsName1.Range("C" & Cloop).Value
        If CurVal = NextVal And PairFnd = False Then 'if the search value hasn't been matched yet
            CurMatchCount = CurMatchCount + 2
            StartCurMatchCount = Cloop - 1 'set to previous row
            EndCurMatchCount = Cloop 'set to current row
            PairFnd = True
            CurVal = NextVal
        ElseIf CurVal = NextVal And PairFnd = True Then 'if the search value matches and has been matched before
            CurMatchCount = CurMatchCount + 1
            EndCurMatchCount = EndCurMatchCount + 1
            CurVal = NextVal
        End If
        If CurVal <> NextVal And PairFnd = True Then 'current value is no longer matched but has been previously
            If CurMatchCount = 2 Then 'pair found
                CurMatchCount = 0
                StartCurMatchCount = 0
                EndCurMatchCount = 0
                PairFnd = False
                CountPair = CountPair + 1
                CurVal = NextVal
            End If
            If CurMatchCount = 3 Then 'three found
                WsName1.Range("I2").Value = CountPair
                WsName1.Range("K2").Value = "Row " & StartCurMatchCount & " row " & EndCurMatchCount
                Exit For
            End If
        Else
            CurVal = NextVal
        End If
    Else
        CurVal = WsName1.Range("C" & Cloop).Value
    End If
Next Cloop
End Sub

 

[sfarad]

This counts all pairs up to the 1st three group then stops.

Option Explicit
Public WbName As Workbook
Public WsName1 As Worksheet
Dim Cloop As Long
Dim LastRowNo As Long
Dim CurVal As Integer
Dim NextVal As Integer
Dim CurMatchCount As Integer
Dim StartCurMatchCount As Integer
Dim EndCurMatchCount As Integer
Dim CountPair As Integer
Dim PairFnd As Boolean


Sub FindHighestRepeat()
Set WbName = ThisWorkbook
Windows(ThisWorkbook.Name).Activate
Set WsName1 = WbName.Sheets(1) '("DORTMUND")


CurVal = 0
NextVal = 0
CurMatchCount = 0
StartCurMatchCount = 0
EndCurMatchCount = 0
CountPair = 0
PairFnd = False
WsName1.Range("I2").Value = 0
WsName1.Range("K2").Value = ""


LastRowNo = WsName1.Range("C65536").End(xlUp).Row


For Cloop = 2 To LastRowNo
    If CurVal > 0 Then 'just started so no value
        NextVal = WsName1.Range("C" & Cloop).Value
        If CurVal = NextVal And PairFnd = False Then 'if the search value hasn't been matched yet
            CurMatchCount = CurMatchCount + 2
            StartCurMatchCount = Cloop - 1 'set to previous row
            EndCurMatchCount = Cloop 'set to current row
            PairFnd = True
            CurVal = NextVal
        ElseIf CurVal = NextVal And PairFnd = True Then 'if the search value matches and has been matched before
            CurMatchCount = CurMatchCount + 1
            EndCurMatchCount = EndCurMatchCount + 1
            CurVal = NextVal
        End If
        If CurVal <> NextVal And PairFnd = True Then 'current value is no longer matched but has been previously
            If CurMatchCount = 2 Then 'pair found
                CurMatchCount = 0
                StartCurMatchCount = 0
                EndCurMatchCount = 0
                PairFnd = False
                CountPair = CountPair + 1
                CurVal = NextVal
            End If
            If CurMatchCount = 3 Then 'three found
                WsName1.Range("I2").Value = CountPair
                WsName1.Range("K2").Value = "Row " & StartCurMatchCount & " row " & EndCurMatchCount
                Exit For
            End If
        Else
            CurVal = NextVal
        End If
    Else
        CurVal = WsName1.Range("C" & Cloop).Value
    End If
Next Cloop
End Sub

OMG!!!!!!!!!!! This is starting to look like the holy grail wow I am impressed.
To your question I don't need to know the location but the ideal would be to have a table in the side that would look like this:

Number of pairs until reset How many times occured throughout the spreadsheet
1 100
2 93
3 80
.............

These are not the real results obviously but is what I need. How do I get there??

 

[nemmi69]

so you want each pairs row recorded?