Merge duplicate rows, sum values of columns and choose latest date for duplicates with VBA

Hello,

My mind is about to explode trying to figure out how to do this. I have a sheet of data with columns A through AD, I want to find duplicates based off of column A. If a duplicate is found columns G through AD need to be summed in each respective column, I also need to keep all columns in tact. If a duplicate is found I need to look at column F and compare the dates associated with each duplicate to choose the date that is in the future the furthest and use all the column information with that date. I need to have this pasted into a separate sheet.

Also the amount of rows the data set has will changed so I need to VBA code to be dynamic to accommodate changes automatically.

I tried to attached a before and after excel sample but not sure if it worked so I took a snip of that file to see if that will help illustrate what I mean. Hopefully I'm making sense.

Capture.GIF

Thank you Jindon, That worked wonderfully. I had change one small thing for the date comparison section, but worked amazing afterwards.

Sub test()
Dim a, i As Long, ii As Long
a = Sheets("sheet1").Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
.Item(a(i, 1)) = .Count + 2
For ii = 1 To UBound(a, 2)
a(.Count + 1, ii) = a(i, ii)
Next
Else
If a(i, 6) >= a(.Item(a(i, 1)), 6) Then
For ii = 1 To 6
a(.Item(a(i, 1)), ii) = a(i, ii)
Next
End If
For ii = 7 To UBound(a, 2)
a(.Item(a(i, 1)), ii) = a(.Item(a(i, 1)), ii) + a(i, ii)
Next
End If
Next
i = .Count + 1
End With
Sheets("sheet2").Cells(1).Resize(i, UBound(a, 2)) = a
End Sub