VBA - filtering duplicates adding up multiple columns

[Gestel]

Hello Everyone!

During the last few weeks I have started to gain experience in VBA (with a lot of help from this forum and great joy so far),
yesterday, however I encountered a challenge I could not yet solve.

For my internship I need to analyse two databases which are constantly updated (therefore I would prefer VBA over a function),
but I'm struggling with duplicates which need to be added up and deleted afterwards.

The first original database looks as follows:

[TABLE="width: 757"]
<tbody>[TR]
[TD]Datum[/TD]
[TD]shift leader[/TD]
[TD]Operator[/TD]
[TD]Activiteit[/TD]
[TD]Truck[/TD]
[TD]Numbers[/TD]
[TD]Lines[/TD]
[TD]Cartons[/TD]
[TD]Start[/TD]
[TD]Einde[/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]222[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]397[/TD]
[TD="align: right"]472[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]553[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]418[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]566[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]222[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]88[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132229[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11:20[/TD]
[TD="align: right"]12:20[/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]87[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]71[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132232[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]78[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]12:10[/TD]
[TD="align: right"]12:20[/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]311[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]655[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]655[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132233[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132118[/TD]
[TD="align: right"]411[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]298[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132118[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]288[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-May-16[/TD]
[TD][/TD]
[TD]Jack[/TD]
[TD]Picking crates[/TD]
[TD="align: right"]14132118[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD]

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

For me it is key to add up the Numbers, Lines and Cartons if the truck number is the same in this database
(as you can see a single time is added for either one truck or for one day for a single operator (like Jack in this example))
and delete the rows which were added up.

Additional information:
The file is a binary file.
has 9 sheets (the database is in the 8th sheet and named "Required for Macro").

I will post about the second database once we found a solution for this one.
I'm sorry for any grammer mistakes, I'm not a native speaker.

If I forgot anything please let me know!

Thank you!



PS:
This thread might be useful, it included the code shown below, unfortunately I'm not yet skilled enough to convert the code to my needs...

http://www.mrexcel.com/forum/excel-...licate-values-then-delete-duplicate-rows.html

Sub Test()    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim Rng As Range
    Set Sh = Worksheets(1)
    Sh.Columns(5).Insert
    LastRow = Sh.Range("A65536").End(xlUp).Row
    With Sh.Range("A1:A" & LastRow).Offset(0, 4)
        .FormulaR1C1 = "=IF(COUNTIF(R1C[-4]:RC[-4],RC[-4])>1,"""",SUMIF(R1C[-4]:R[" & LastRow & "]C[-4],RC[-4],R1C[-1]:R[" & LastRow & "]C[-1]))"
        .Value = .Value
    End With
    Sh.Columns(4).Delete
    Sh.Rows(1).Insert
    Set Rng = Sh.Range("D1:D" & LastRow + 1)
    With Rng
        .AutoFilter Field:=1, Criteria1:="="
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub