Sum and Delete Duplicate Rows

[RossH94]

Hi All,

I need a table that shows the DriverName and Duration columns, every other column can be deleted. All duplicates must be delete and the duration for each driver should be summed.

I've been struggling with this for a while, the code i've been trying to use is below. It needs to be done in Vba.

If anyone can help I would greatly appreciate it.

Sub Test
Dim Rng As Range, Dn As Range, n As Long, nRng As Range
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Dn
Else
If nRng Is Nothing Then Set nRng = _
Dn Else Set nRng = Union(nRng, Dn)
.Item(Dn.Value).Offset(, 3) = .Item(Dn.Value).Offset(, 3) + Dn.Offset(, 3)
End If
Next
If Not nRng Is Nothing Then nRng.EntireRow.Delete
End With
End Sub

 

[footoo]

Try this :

Sub Test()
Dim rng As Range
[D1:D2] = [A1:A2].Value
[A:C,E:L].Delete
[C4] = "Duration"
Set rng = Range([C5], Cells(Rows.Count, "A").End(xlUp)(1, 3))
With rng
    .NumberFormat = "hh:mm:ss"
    .Formula = "=SUMIF(" & .Offset(0, -2).Address & ",A5," & .Offset(0, -1).Address & ")"
    .Value = .Value
    .Offset(0, -2).Resize(, 3).RemoveDuplicates Columns:=1, Header:=xlNo
End With
[B:B].Delete
End Sub

 

[RossH94]

Try this :

Sub Test()
Dim rng As Range
[D1:D2] = [A1:A2].Value
[A:C,E:L].Delete
[C4] = "Duration"
Set rng = Range([C5], Cells(Rows.Count, "A").End(xlUp)(1, 3))
With rng
    .NumberFormat = "hh:mm:ss"
    .Formula = "=SUMIF(" & .Offset(0, -2).Address & ",A5," & .Offset(0, -1).Address & ")"
    .Value = .Value
    .Offset(0, -2).Resize(, 3).RemoveDuplicates Columns:=1, Header:=xlNo
End With
[B:B].Delete
End Sub

Thanks for that, it works up to a point. However, it doesn't add up the duration correctly.

 

[footoo]

Thanks for that, it works up to a point. However, it doesn't add up the duration correctly.

Please provide some sample data that is not being summed properly.
What the format of your Duration column?

 

[RossH94]

Please provide some sample data that is not being summed properly.
What the format of your Duration column?

These are some examples, the format is hh:mm:ss.

[TABLE="width: 483"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DriverName
[/TD]
[TD] Duration
[/TD]
[/TR]
[TR]
[TD]
Richard Taylor 1335
[/TD]
[TD="align: right"]
00:17:17
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 483"]
<colgroup><col width="301"><col width="182"></colgroup><tbody>[TR]
[TD="width: 301"]Jason Ashton 1326
[/TD]
[TD="class: xl65, width: 182, align: right"]
18:21:40

[/TD]
[/TR]
</tbody>[/TABLE]

<colgroup><col width="301"><col width="182"></colgroup><tbody>
[TD="width: 301"]Peter Reed 1287[/TD]
[TD="class: xl65, width: 182, align: right"]14:15:12[/TD]

</tbody>

 

[footoo]

I get the correct totals (e.g. 00:56:46 for Jason Ashton) when I run the macro.

Try stepping though the code via F8 to see what's happening, particularly what formula the macro is putting in column C.

Does your original Duration column contain any formulas ?

 

[RossH94]

I get the correct totals (e.g. 00:56:46 for Jason Ashton) when I run the macro.

Try stepping though the code via F8 to see what's happening, particularly what formula the macro is putting in column C.

Does your original Duration column contain any formulas ?

There are no formulas in the original duration column. This is the formula the macro is using: =SUMIF($A$5:$A$18437,A5,$B$5:$B$18437)

 

[footoo]

The formula is correct.

In an empty cell on your original sheet try the sum function for one of the drivers. Does it produce the correct total?

Does Jason Ashton only appear on rows 10/11/12 or also on other rows? How is your original data sorted?

 

[RossH94]

The formula is correct.

In an empty cell on your original sheet try the sum function for one of the drivers. Does it produce the correct total?

Does Jason Ashton only appear on rows 10/11/12 or also on other rows? How is your original data sorted?

Yes it does add up correctly, the spreadsheet is 18,500 columns in total. I've hidden some of it for data protection reasons.

 

[RossH94]

Yes it does add up correctly, the spreadsheet is 18,500 columns in total. I've hidden some of it for data protection reasons.

18,500 rows I meant to say.