Summarizing Data

[fa2020]

Hi,
I have a sample excel data which has 5 columns. I want to summarize them based on IDs and Work Time. So, for each ID, I need to sum its corresponding Work Time values and at the same time, keep other corresponding data too. For better understandings, I provided my desired final solution in sheet 2 (the answer should be written in a new sheet).
I think that I need to use VBA Dictionary.
Please help me out (Please note that I just need VBA code, not Power Pivot).

I wrote a code but I couldn't continue. I failed to complete the code. I'm new to VBA Dictionaries and don't have enough understandings of dictionary usage in different situation yet.

Sub test()
    Dim a, i As Long, txt As String, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("Sheet1").Range("A1:E11")
    For i = 1 To UBound(a, 1)
        txt = Join(Array(a(i, 1), a(i, 2), a(i, 3), a(i, 4)), "~")
        dic(txt) = dic(txt) + a(i, 4)
    Next
    With Sheets.Add.Cells(1).Resize(, UBound(a, 2))
        .Value = Array("ID", "Farm", "Department", "Work Time", "Date")