Searching a row for total consecutive numbers. Detailed inside.

[WS_Scott]

Hi,

Thanks for taking the time to read and help resolve this issue. I've attached an image of the data I am working with.

I'm looking for a formula to determine how the max consecutive numbers a row has. Consecutive meaning {5, 2, 3, 1, 4, 12, 65} because the array has consecutive numbers from 1 to 5 even though it's out of order. The formula would return '5'. {54, 21, 78, 1, 5, 12, 65} returns 0. {54, 55, 74, 1, 5, 12, 65} returns 2. Etc.. Note: A number only appears once in a row. No two columns in same row will have the same number.

I've been completely stumped on how to proceed. Nothing I have managed to search for has given me clues. Mostly because every formula I come across requires knowing the values you want to look for. In my case, each row has 21 columns and there are 80 different numbers that can appear in each row. So I'm not concerned with what numbers are consecutive as I am how many total.

Please let me know if you have any questions. I will be checking here regularly to answer.

Thanks!

 

[pgc01]

Hi
Welcome to the board

Some questions:

1 - you say there are 80 different numbers. Can we consider them the numbers 1-80 or they are just some integers? In that case what's the range of the values that they can take?

2 - can you have duplicate numbers in 1 row? In that case how would you count, for ex., {1,1,2,2,3}?

3 - you say that you have 21 columns in a row but you post the example with 20 columns. Is the example wrong and it should be C:W (or B:V), or is it 20 columns per row?

Please clarify.
I'll check in tomorrow to see your answers.

 

[Usercode]

Someone gave me this code to find consecutive numbers before. It only shows the number of consecutives in a range.

Public Function CountConsec(ListRange As Range, ByVal Seq As Long) As Variant    '-- Accept ListRange of single row or single column only
    Dim n As Long, m As Long
        
    If Seq < 1 Then CountConsec = CVErr(2015): Exit Function
    If ListRange.Areas.Count > 1 Then CountConsec = CVErr(2042): Exit Function
    n = ListRange.Rows.Count
    m = ListRange.Columns.Count
    If n > 1 And m > 1 Then CountConsec = CVErr(2042): Exit Function
    If m > 1 Then n = m
    CountConsec = 0
    If Seq <= n Then
        ReDim ar(1 To n + 1) As Long
        For m = 1 To n + 1: ar(m) = 1: Next
        For m = 2 To n
            If ListRange(m) = ListRange(m - 1) + 1 Then
                ar(m) = ar(m - 1) + 1
            End If
        Next
        For m = 1 To n
            If ar(m) = Seq And ar(m + 1) = 1 Then
                CountConsec = CountConsec + 1
            End If
        Next
    End If
End Function