Announcement

Collapse
No announcement yet.

Count Numbers In Sequence

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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)

  • #2
    Re: Counting A Series Of Numbers

    Vlad (any relation to The Count?),

    Here is some code:

    Code:
    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, 09:58.

    Comment


    • #3
      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

      Comment


      • #4
        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, 14:59.

        Comment


        • #5
          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
          Code:
          Public Function CountConsecutiveNumbers() As Boolean
          To:
          Code:
          Public Sub CountConsecutiveNumbers()
          Then do tools/macro to find it and run it.

          G.

          Comment


          • #6
            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, 10:27.

            Comment


            • #7
              Re: Counting A Series Of Numbers

              Hi,

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

              Code:
              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

              Comment


              • #8
                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, 14:16.
                Kris

                ExcelFox

                Comment


                • #9
                  Re: Counting A Series Of Numbers

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

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

                  Comment


                  • #10
                    Re: Counting A Series Of Numbers

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

                    Comment


                    • #11
                      Re: Count Numbers In Sequence

                      Yes only row A had data however once the macro is run then by the time we reach

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

                      Then there is data in column B
                      Last edited by Dave Hawley; December 8th, 2006, 16:13.

                      Comment


                      • #12
                        Re: Count Numbers In Sequence

                        Yes only data in column A

                        Comment


                        • #13
                          Re: Counting A Series Of Numbers

                          Did you see the suggestion from Kris?

                          Comment


                          • #14


                            Re: Count Numbers In Sequence

                            Hi,

                            As I can't replicate an error on my side, could you please attach the dataset that is making the code fall over and I'll have a look.

                            Kind regards,

                            Robert
                            : D

                            Comment

                            Working...
                            X