Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Count Numbers In Sequence

  1. #1
    Join Date
    18th April 2006
    Posts
    39

    Count Numbers In Sequence

    Hi

    I have a list of random numbers in column A the number range is from 0 to 20,000. What I am trying to do is to count the sequential numbers after each random number....sorry this is not really explained well so I willl use a numeric example.

    A
    23
    24
    25
    33
    34
    60
    77
    80

    Above is what I have in column A you can see that my number range starts a 23 so I would like to count the sequential numbers that occur from 23 in this case it would be count(23,24,25) giving the answer 3 I would like this answer to be inserted into column B next to 23 and the numbers that were counted in this formula then be deleted as rows (i.e. where the number 24 and 25 was counted then the rows containing these numbers are deleted), The macro moves then to count the next number.

    In the case of just a single number then a value of 1 is entered into the column next to the number indicating that there was only one number in that sequence.


    Can this be done? If anyone can work out a VBA for this that would be great but even a formula will do I will manually delete the rows if need be (but there 11000 of them and I'de rather not)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th October 2006
    Location
    Australia
    Posts
    287

    Re: Counting A Series Of Numbers

    Vlad (any relation to The Count?),

    Here is some code:

    VB:
    Public Function CountConsecutiveNumbers() As Boolean 
        Dim c As Excel.Range 
        Dim x As Integer 
        Dim count As Integer 
        Dim prev As Single 
         
        Set c = ActiveWindow.ActiveCell 
         
        Do Until c.Offset(x, 0).Value = "" 
             
            If prev + 1 = c.Offset(x, 0).Value Then 
                count = count + 1 
            Else 
                c.Offset(x - 1, 1).Value = count 
                count = 0 
            End If 
             
            prev = c.Offset(x, 0).Value 
            x = x + 1 
        Loop 
         
        c.Offset(x - 1, 1).Value = count 
         
    End Function 
    
    
    Put your cursor on the first of the numbers and then run the macro above.

    This doesn't do the deletion. I suggest that for the deletion, you insert a column to the left of your numbers that is a list of the Order of the numbers (call It "Order").
    Then sort the numbers by the list that this code above inserts (the frequency). This will bunch all of the numbers that you want at the top. Delete all of the numbers that you don't want and then sort by your "Order" column.

    G. (mwahahahaha)
    Last edited by GeorgS; December 8th, 2006 at 09:58.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th April 2006
    Posts
    39

    Re: Counting A Series Of Numbers

    Thanks for your help you and your loved ones will be among the few mortals spared...or you can join me in the seat of power wehn the dart times begin

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th April 2006
    Posts
    39

    Re: Counting A Series Of Numbers

    Sorry I dont get how this works its a user defined function so the formula goes into column b?
    Last edited by Dave Hawley; December 8th, 2006 at 14:59.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th October 2006
    Location
    Australia
    Posts
    287

    Re: Counting A Series Of Numbers

    Vlad,
    I beg your mercy,
    You need to past the code into a module in your workbook, and then change
    VB:
    Public Function CountConsecutiveNumbers() As Boolean 
    
    
    To:
    VB:
    Public Sub CountConsecutiveNumbers() 
    
    
    Then do tools/macro to find it and run it.

    G.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th April 2006
    Posts
    39

    Re: Counting A Series Of Numbers

    Cheers, ozgrid rips www.excelforum.com those guys are always SO SLOW...why do I even bother posting there
    Last edited by Vlad999; December 8th, 2006 at 10:27.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st December 2006
    Posts
    1,366

    Re: Counting A Series Of Numbers

    Hi,

    Assuming the data in Column A is already sorted in ascending sequence, try the following*:

    VB:
    Sub FilterRecords() 
         
        Dim intCounter As Integer 
        Dim strActiveCell As String 
        Dim lngLastRowMarker As Long 
         
        intCounter = 1 
         
         'Clear any existing counts in column B
        Columns("B").Delete 
         
        Range("A1").Select 
         
        Do Until ActiveCell = "" 
            If ActiveCell.Value + 1 = ActiveCell.Offset(1, 0).Value Then 
                ActiveCell.Offset(1, 0).Select 
                intCounter = intCounter + 1 
            Else 
                ActiveCell.Offset(1, 0).Select 
                strActiveCell = ActiveCell.Address 
                ActiveCell.Offset(0 - intCounter, 1).Select 
                ActiveCell.Value = intCounter 
                Range(strActiveCell).Select 
                intCounter = 1 
            End If 
        Loop 
         
        lngLastRowMarker = Range("A65536").End(xlUp).Row 
         
         'Sort the detail data set by Column B.
        Range("A1:B" & lngLastRowMarker).Select 
        Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
        Range("B1").Select 
        Selection.End(xlDown).Offset(1, -1).Select 
        Range(ActiveCell, ActiveCell.End(xlDown)).Select 
         
        Selection.EntireRow.Delete 
         
         'Sort the detail data set by Column A.
        Range("A1:B" & lngLastRowMarker).Select 
        Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
         
        Range("A1").Select 
         
        MsgBox "The data has been sorted and filtered.", vbInformation, "Data Sort and Filter Editor" 
         
    End Sub 
    
    
    * At 10,000 records may this may take a while - Access might be a better solution...

    Kind regards,

    Robert
    : D

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Counting A Series Of Numbers

    Hi,

    If your data is in A2:A9 then

    Try,

    =MAX(FREQUENCY(IF(IF(ABS(A2:A8-A3:A9)=1,1,0)=1,ROW(A2:A8)),IF(IF(ABS(A2:A8-A3:A9)=1,1,0)<>1,ROW(A2:A8))))+1

    To Enter the array formula hold down Ctrl and Shift while pushing Enter.
    Last edited by Krishnakumar; December 8th, 2006 at 14:16.

  9. #9
    Join Date
    18th April 2006
    Posts
    39

    Re: Counting A Series Of Numbers

    Tried your code Trebor it works fine untill it reaches this point in the macro

    VB:
     
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st December 2006
    Posts
    1,366

    Re: Counting A Series Of Numbers

    Seems odd - was column B clear before you ran it?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count Numbers Of Zeros Before Positive Numbers
    By chrisbrooks72 in forum Excel General
    Replies: 6
    Last Post: June 22nd, 2008, 15:41
  2. Automatically Generate Numbers In Sequence
    By The_Beardy_One in forum Excel General
    Replies: 2
    Last Post: December 10th, 2007, 21:35
  3. Count Numbers Of Rows Housing 2 Specified Numbers
    By Runeshak in forum Excel General
    Replies: 4
    Last Post: October 18th, 2006, 15:50
  4. Auto Assign Numbers in Sequence
    By cswa860 in forum Excel General
    Replies: 3
    Last Post: September 4th, 2005, 10:53
  5. Formulas : Sequence of numbers extraction
    By skelum in forum Excel General
    Replies: 3
    Last Post: February 6th, 2004, 00:38

Bookmarks

Posting Permissions

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