Count Values in sequence

JonRowland

Active Member
Joined
May 9, 2003
Messages
417
Office Version
  1. 365
Platform
  1. Windows
This is what I would like to do using VBA. Any help to get me started would be great.

In Col T there is a variable range of different values. The values can change throughout the range and I would like to loop through the range and recorded the first and last time the values in T appear in their seqeunce and to count how many times in the sequence the values appear. Date/Time are in Col A & B accordingly. If there is "-" in T then this should be ignored?

Here is an example

Col A Col B Col T
01/01/1999 19:19:32 999123
01/01/1999 19:21:56 999123
01/01/1999 19:22:08 77777
01/01/1999 19:22:10 77777
01/01/1999 19:22:24 77777
01/01/1999 19:27:13 77777
01/01/1999 19:30:38 77777
01/01/1999 19:32:04 77777
01/01/1999 19:32:59 77777
01/01/1999 19:34:06 77777
01/01/1999 19:34:31 77777
01/01/1999 19:35:27 999123
01/01/1999 19:36:05 999123
01/01/1999 19:36:45 999123
01/01/1999 19:37:42 999123
01/01/1999 19:38:47 999123
01/01/1999 19:41:01 999123
01/01/1999 19:43:57 999123

Output result to new worksheet as something like


ColT Vlu Count First Appears Last Appears
999123 2 19:19:32 01/01/1999 19:21:56 01/01/1999
7777 9 19:22:08 01/01/1999 19:34:31 01/01/1999
999123 7 19:35:27 01/01/1999 19:43:57 01/01/1999
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Perhaps something like this to get you started? I've assumed the input data is sorted by date and time.

VBA Code:
Option Explicit
Sub test()
    Dim coll As New Collection
    Dim lastRow As Long, i As Long, count As Long, collRow As Long, outRow As Long
    Dim tVal As Variant, aValmin As Variant, aValmax As Variant
    Dim bValmin As Variant, bValmax As Variant
    '
    ' Get last data row
    '
    lastRow = Worksheets("Sheet1").Cells.Find("*", _
                                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    '
    ' Set initial data values
    '
    outRow = 2
    aValmin = Cells(2, 1).Value
    aValmax = aValmin
    bValmin = Cells(2, 2).Value
    bValmax = bValmin
    tVal = Cells(2, 20).Value
    count = 1
    '
    ' For each row, update maximum value of T value is the same, or
    ' write out last values to collection
    '
    For i = 3 To lastRow
        If Cells(i, 20).Value = tVal Then
            aValmax = Cells(i, 1).Value
            bValmax = Cells(i, 2).Value
            count = count + 1
        Else
            coll.Add tVal
            coll.Add count
            coll.Add aValmin
            coll.Add bValmin
            coll.Add aValmax
            coll.Add bValmax
            '
            ' Reset the data values
            '
            aValmin = Cells(i, 1).Value
            aValmax = aValmin
            bValmin = Cells(i, 2).Value
            bValmax = bValmin
            tVal = Cells(i, 20).Value
            count = 1
        End If
    Next i
            '
            ' Write the last value to the collection
            '
            coll.Add tVal
            coll.Add count
            coll.Add aValmin
            coll.Add bValmin
            coll.Add aValmax
            coll.Add bValmax
    '
    ' Write out the collection
    '
    For collRow = 1 To coll.count Step 6
        ActiveSheet.Cells(outRow, 22) = coll(collRow)
        ActiveSheet.Cells(outRow, 23) = coll(collRow + 1)
        ActiveSheet.Cells(outRow, 24) = coll(collRow + 2)
        ActiveSheet.Cells(outRow, 25) = Format(coll(collRow + 3), "hh:mm:ss")
        ActiveSheet.Cells(outRow, 26) = coll(collRow + 4)
        ActiveSheet.Cells(outRow, 27) = Format(coll(collRow + 5), "hh:mm:ss")
        outRow = outRow + 1
    Next collRow
End Sub

Produces the following output from your test data:
Book1
ABCTUVWXYZAA
1
21/01/199919:19:3299912399912321/01/199919:19:321/01/199919:21:56
31/01/199919:21:569991237777791/01/199919:22:081/01/199919:34:31
41/01/199919:22:087777799912371/01/199919:35:271/01/199919:43:57
51/01/199919:22:1077777
61/01/199919:22:2477777
71/01/199919:27:1377777
81/01/199919:30:3877777
91/01/199919:32:0477777
101/01/199919:32:5977777
111/01/199919:34:0677777
121/01/199919:34:3177777
131/01/199919:35:27999123
141/01/199919:36:05999123
151/01/199919:36:45999123
161/01/199919:37:42999123
171/01/199919:38:47999123
181/01/199919:41:01999123
191/01/199919:43:57999123
Sheet1


Regards

Murray
 
Upvote 0
Solution
Murray, thank you, that's given me the headstart I needed and allowed me to complete the procedure I was attempting and more.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top