Calculating time from 2 columns

[odrisso]

Hi,

I have two columns like this way.

[TABLE="class: cms_table_outer_border, width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]102[/TD]
[TD]103[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]135[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]157[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]189[/TD]
[TD]191[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]201[/TD]
[TD]202[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]222[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]245[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]290[/TD]
[TD]292[/TD]
[/TR]
</tbody>[/TABLE]


Here the values are in seconds. I want to write a formula, which show the total duration of seconds of a and b.

For example: here total duration of a is = (160-102) + (250-222) = 86 seconds.

Please suggest me how to do it.

Thanks

 

[RickXL]

Hi,

I started with some formulas then tried PivotTables but ended up with a macro.
So here it is if a macro solution is acceptable:

' Remember to add Reference = Microsoft Scripting Runtime
Sub Total_Times_for_Groups()

    Dim Groups As Range, Group As Range
    Dim Totals As Dictionary
    Dim PrevGroup As String, NextGroup As String
    Dim MinValue As Long, MaxValue As Long, Diff As Long
    
    ' Dictionary to store Group name and running total
    Set Totals = New Dictionary

    ' Set Range for List of Groups
    With Sheet1
        Set Groups = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With

    For Each Group In Groups
        ' Note next and previous group names
        PrevGroup = Group.Offset(-1, 0)
        NextGroup = Group.Offset(1, 0)
        
        ' If first entry in Group
        If Group <> PrevGroup Then
            MinValue = Group.Offset(0, 1)
            MaxValue = Group.Offset(0, 2)
        End If
        
        ' If in same group as last tim round the loop
        If Group = PrevGroup Then
            MaxValue = Group.Offset(0, 2)
        End If
        
        ' If last entry in Group
        If Group <> NextGroup Then
            ' Calculate difference f max and min
            Diff = MaxValue - MinValue
            ' Store running total for Group
            With Totals
                If .Exists(Group.Value) Then .Item(Group.Value) = .Item(Group.Value) + Diff Else .Add Group.Value, Diff
            End With
        End If

    Next

    ' Output the answers to the worksheet
    Range("E:F").ClearContents
    With Totals
        For i = 0 To .Count - 1
            Range("E" & i + 1) = .Keys(i)
            Range("F" & i + 1) = .Items(i)
        Next
    End With

End Sub

Basically, it uses a Dictionary to keep a list of groups and totals. A Dictionary in VBA has a Key and an Item. I use the Key to store the Group name and the Item to keep a running total of the time differences.

Then it starts at row 2 and keeps track of the first and last times to get the minimum and maximum values. When it moves on to the next Group it writes the data to the Dictionary.

At the end, the contents of the dictionary are written into columns E and F.
Please Note: The macro clears the contents of these columns so you could lose some data if you have any in there.

The macro needs to be pasted into a macro Module and you also need to go into the Tools-->References menu and tick "Microsoft Scripting Runtime" to make the Dictionary object available.