count the number of consecutive values >=2) (1 Viewer)

[jomuir]

I would like to count the number of consecutive values >=2 in a row.

Eg:-
1 2 3 2 3 2 2 2 2 1 1 1 3 3 2 2 1 1

The returned value would be 8, because the 2nd to the 9th figure are all greater or equal to 2

OR

1 2 3 1 2 1 3 2 1 3 2 3 4 3 1 2 3 1

The returned value would be 5, because the 10th to the 14th figure are all greater or equal to 2

 

[JamesMcS]

Hi! Assuming the numbers are in records in a table, try

Sub Con_Nums()
Dim Dbs As Database
Dim Rst As dao.Recordset
Dim Con_Count, Max_Con_Count As Integer

Set Dbs = CurrentDb
Set Rst = Dbs.OpenRecordset("Consecutive Numbers")

Rst.MoveFirst

Do While Not Rst.EOF

'Debug.Print "ID:" & Rst!ID & ", Num: " & Rst!con_num & ", Con Count: " & Con_Count & ", Max: " & Max_Con_Count

If Rst.Fields("Con_Num") >= 2 Then
    
    Con_Count = Con_Count + 1
    Rst.MoveNext

ElseIf Rst.Fields("Con_Num") < 2 Then

    If Con_Count > Max_Con_Count Then
    
        Max_Con_Count = Con_Count
    
    End If

Con_Count = 0

Rst.MoveNext

End If

Loop

If Con_Count > Max_Con_Count Then
    
    Max_Con_Count = Con_Count
    
End If

'Debug.Print "Max Con Count=" & Max_Con_Count

End Sub

Works a treat - obviously rename fields and tables as you see fit. I commented out the debug lines, they're just to see if the code was working.

 

[JamesMcS]

You could also turn this into a function:

Public Function Con_Nums(InputRS) As Integer
Dim Dbs As Database
Dim Rst As DAO.Recordset
Dim Con_Count, Max_Con_Count As Integer

Set Dbs = CurrentDb
Set Rst = Dbs.OpenRecordset(InputRS)

Rst.MoveFirst

Do While Not Rst.EOF

If Rst!Con_Num >= 2 Then
    
    Con_Count = Con_Count + 1
    Rst.MoveNext

ElseIf Rst!Con_Num < 2 Then

    If Con_Count > Max_Con_Count Then
    
        Max_Con_Count = Con_Count
    
    End If

Con_Count = 0

Rst.MoveNext

End If

Loop

If Con_Count > Max_Con_Count Then
    
    Max_Con_Count = Con_Count
    
End If

Con_Nums = Max_Con_Count

End Function

I think you could then even use this as the controlsource of a text box, as in "con_nums(form.recordsource)"... Genius

 

[Brianwarnock]

James this is Excel

Brian

 

[JamesMcS]

Bugger. I was pleased with that routine too!

 

[Brianwarnock]

Bugger. I was pleased with that routine too!

LOL

Try this

Brian

Sub gtr1()
'Brian 16/6/2011
'find max number of consecutive cells >1 in each row

Dim lastrow As Integer
Dim lastcol As Integer
Dim countr As Integer
Dim countc As Integer
Dim countgtr As Integer
Dim countmax As Integer


lastcol = ActiveSheet.UsedRange.Columns.Count
lastrow = ActiveSheet.UsedRange.Rows.Count

For countr = 1 To lastrow
    countgtr = 0
    countmax = 0
    For countc = 1 To lastcol
        If ActiveSheet.Cells(countr, countc).Value > 1 Then
        countgtr = countgtr + 1
           If countgtr > countmax Then
           countmax = countgtr
           End If
        Else
            If countgtr > countmax Then
            countmax = countgtr
            End If
        countgtr = 0
        End If
    Next countc
    ActiveSheet.Cells(countr, countc + 1).Value = countmax
Next countr


End Sub

 

[NBVC]

Not sure if you guys should bother trying... the OP got his response in a crosspost:

http://www.excelforum.com/excel-general/779967-count-the-number-of-consecutive-values-2-a.html

and obviously hasn't returned here to advise you so that you don't waste your valuable time.

Jomuir, if you come back, please read this: http://www.excelguru.ca/node/7

 

[boblarson]

Not sure if you guys should bother trying... the OP got his response in a crosspost:

http://www.excelforum.com/excel-general/779967-count-the-number-of-consecutive-values-2-a.html

and obviously hasn't returned here to advise you so that you don't waste your valuable time.

Jomuir, if you come back, please read this: http://www.excelguru.ca/node/7

Thanks for posting that. I've bookmarked it so I can use it if I find someone who could use the information.

 

[jomuir]

Hi,

Sorry, I have been on then off then on then off line for a while (new pc - it died after 2 days and then another) , and have just come back to say I have been provided the answer......After I posted this initial post I was still looking for the solution when I came across an Excel forum and thought it would be better suited than this an Access Forum, thus the double post. In future all my Access posts will be on this site and my Excel on the other. Again sorry for the double post....and the solution is below for anyone that has come to this thread:-

=MAX(FREQUENCY(IF(A2:R2>=2,COLUMN(A2:R2)),IF(A2:R2<2,COLUMN(A2:R2))))

confirmed with CTRL+SHIFT+ENTER