Results 1 to 9 of 9

Thread: excel - count consecutive values

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    excel - count consecutive values

    Hi guys. I have a great problem to solve and I can't find a solution.

    I make an example

    A B C D E F G H I J
    1 2 3 6 7 8 11 12 13 14



    i need to calculate how many consecutive numbers there are

    OCCURRENCES
    3 4 5 6 7 8 9 10 (consecutive numbers)

    2 1 (count of consecutive numbers)



    In this example I need to have as results 2 for 3 numbers (1,2,3 and 6,7,8) and 1 for 4 numbers (11,12,13,14)

    Is there sum array formula that i can use or it occurs vba?
    Thanks in advance.
    Last edited by italian_guy; Jan 30th, 2009 at 11:01 AM.

  2. #2
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: excel - count consecutive values

    Not sure if there is any native formula. But yes it is possible to do it in vba...

    Edit

    I have spent my afternoon on this so I hope you try it...

    Place this in a module.

    Hope this helps...

    Code:
    Option Explicit
    
    '<---------------- Usage/Instruction--------------------->
    '=FindConsecutiveStrings(Range,Number of Places to match)
    '-- Example: In Cell A2 type
    '=FindConsecutiveStrings(A1:R1,4)
    
    '-- Range is the cells where the data is
    '-- Number of Places to match
    '   For Example 3 for 123
    '   Or  4 for 6789
    '   Or  5 for 12345
    '<------------- End of Usage/Instruction------------------>
    
    
    Function FindConsecutiveStrings(rng As Range, Numb)
        Dim cl As Range, StringFromRange As String
        
        '-- Concatenate Values
        For Each cl In rng
            StringFromRange = StringFromRange & cl.Value
        Next cl
        
        FindConsecutiveStrings = ChkConNumb(Trim(StringFromRange), Numb)
        
    End Function
    
    Function ChkConNumb(String1, NoOfPlace)
        Dim MyErrorMsg As String, Checkstring As String
        Dim Count As Integer, Check As Integer
        Dim i As Integer, j As Integer, k As Integer
        
        '-- Error Message
        MyErrorMsg = "Please enter a number which is Greater than 1 or " & _
        "a number which is Lesser than " & (Len(String1) + 1)
        
        '-- Check if the length of consecutive string is
        '   shorter than the main string
        If NoOfPlace > Len(String1) Then
            'MsgBox "Please enter a number which is Lesser than " & Len(String1)
            ChkConNumb = MyErrorMsg
            Exit Function
        End If
        
        '-- Check if the length of consecutive string is
        '   shorter than 2 Characters
        If NoOfPlace < 2 Then
            'MsgBox "Please enter a number which is Greater than 1"
            ChkConNumb = MyErrorMsg
            Exit Function
        End If
        
        Count = 0
        For i = 1 To Len(String1)
            Check = 0
            '-- This ensures that it will stop searching on
            '   reaching the relevant end of the string
            If i > Len(String1) - NoOfPlace + 1 Then Exit For
            
            Checkstring = Mid(String1, i, NoOfPlace)
            
            For j = 1 To Len(Checkstring)
                If i <> 1 Then
                    '-- Checks if the starting number is not
                    '   consecutive to the last batch
                    If Val(Mid(Checkstring, 1, 1)) - _
                    Val(Mid(String1, i - 1, 1)) <> 1 Then
                        '-- Checks if the starting number of next batch is not
                        '   consecutive to current batch
                        If Val(Mid(String1, i + Len(Checkstring), 1)) - _
                        Val(Mid(Checkstring, Len(Checkstring), 1)) <> 1 Then
                            For k = 1 To Len(Checkstring) - 1
                                '-- Final Check if the string is Consecutive
                                If Val(Mid(Checkstring, k + 1, 1)) - _
                                Val(Mid(Checkstring, k, 1)) = 1 Then
                                    Check = Check + 1
                                End If
                            Next k
                            
                            If Check = NoOfPlace - 1 Then
                                Count = Count + 1
                                '-- Skip searching main string
                                i = i + NoOfPlace
                                Exit For
                            End If
                            Check = 0
                        End If
                    End If
                Else
                    '-- Checks if the starting number of next batch is not
                    '   consecutive to current batch
                    If Val(Mid(String1, i + Len(Checkstring), 1)) - _
                    Val(Mid(Checkstring, Len(Checkstring), 1)) <> 1 Then
                        For k = 1 To Len(Checkstring) - 1
                            '-- Final Check if the string is Consecutive
                            If Val(Mid(Checkstring, k + 1, 1)) - _
                            Val(Mid(Checkstring, k, 1)) = 1 Then
                                Check = Check + 1
                            End If
                        Next k
                        
                        If Check = NoOfPlace - 1 Then
                            Count = Count + 1
                            '-- Skip searching main string
                            i = i + NoOfPlace
                            Exit For
                        End If
                        Check = 0
                    End If
                End If
            Next j
        Next i
        '-- Store value
        ChkConNumb = Count
    End Function
    Last edited by Siddharth Rout; Jan 31st, 2009 at 07:16 AM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,467

    Re: excel - count consecutive values

    try this. it accepts a range, (limited to 1 row), counts the consecutive numbers + puts the totals in another row:

    vb Code:
    1. Sub countConsecutiveNumbers()
    2.     Dim inputRange As Range
    3.     Set inputRange = Range("A1:J1")
    4.    
    5.     Dim counter As Integer
    6.     counter = 1
    7.    
    8.     For c = 2 To inputRange.Columns.Count
    9.         If Cells(inputRange.Row, c - 1).Value = Cells(inputRange.Row, c).Value - 1 Then
    10.            counter = counter + 1
    11.         Else
    12.             Cells(5, counter).Value = Cells(5, counter).Value + 1
    13.             counter = 1
    14.         End If
    15.     Next
    16.     Cells(5, counter).Value = Cells(5, counter).Value + 1
    17.    
    18. End Sub
    Attached Images Attached Images  

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: excel - count consecutive values

    Hi Paul, I have already given a code which not only accepts a range but also lets you specify "Number of Places to match" See "Usage Instructions" above...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  5. #5
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: excel - count consecutive values

    My function:
    Code:
    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
    Attached Images Attached Images  
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Dec 2007
    Posts
    94

    Re: excel - count consecutive values

    Hi guys. I came back to post the code I've written unti now and I've found all your answers. You're very kind. Thanks to everybody for all the time you've spent to help me. I really appreciate it. Now I'll try all your solutions and I'll post a feedback. Thanks again.

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    7

    Re: excel - count consecutive values

    I tried all of these codes, but they don't make the calculations exactly right.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: excel - count consecutive values

    as this thread is from 8 years ago, you should have started a new thread

    what is the problem with the calculation?
    is it the same problem for all the different codes?
    what is the result you are getting? where is it wrong?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    New Member
    Join Date
    Aug 2017
    Posts
    7

    Question Re: excel - count consecutive values

    Quote Originally Posted by westconn1 View Post
    as this thread is from 8 years ago, you should have started a new thread

    what is the problem with the calculation?
    is it the same problem for all the different codes?
    what is the result you are getting? where is it wrong?
    Thanks for your answer. I think It would be best to attach an example, but I couldn't attach it here, it gives invalid file type error.
    So I share a link : https://1drv.ms/x/s!AoGkZUHlKui9gRPWEiSx5f68ss82

    The only one that works is the third code,but the problem with it, as i see, I search for 7 consecutives in the range with 8 consecutives, it doesn't count.

    I am sorry about this being an old thread, there are three ways offered here, so I wanted to ask about them.
    Last edited by Matten; Aug 27th, 2017 at 09:54 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width