Option Explicit
Sub T_T()
Dim dic As Object
Dim sheet As Worksheet
Dim data As Variant, result As Variant
Dim sCode As String, sCust As String
Dim i As Long, k As Long, r As Long, c As Long
Dim dbQty As Double
With ThisWorkbook.Worksheets("DATA")
r = .Cells(.Rows.Count, "M").End(xlUp).Row
If (r < 2) Then Exit Sub
data = .Range("M2:Q" & r).Value
End With
Set sheet = ThisWorkbook.Worksheets("Ket_Qua")
r = sheet.Cells(sheet.Rows.Count, "C").End(xlUp).Row + 1
If (r < 3) Then Exit Sub
result = sheet.Range("C2:D" & r).Value
r = UBound(result, 1)
Set dic = CreateObject("Scripting.Dictionary")
For i = LBound(result, 1) + 1 To UBound(result, 1)
If (i < r) Then
dic.Add result(i, 1), i
Else
result(i, 1) = "Grand Total"
End If
Next i
For i = LBound(data, 1) To UBound(data, 1)
sCode = data(i, 1)
dbQty = data(i, 4)
sCust = data(i, 5)
If dic.Exists(sCode) Then
k = dic.Item(sCode)
If Not dic.Exists(sCust) Then
c = UBound(result, 2) + 1
ReDim Preserve result(1 To r, 1 To c)
dic.Add sCust, c
result(1, c) = sCust
result(k, c) = dbQty
Else
c = dic.Item(sCust)
result(k, c) = result(k, c) + dbQty
End If
result(r, c) = result(r, c) + dbQty
End If
Next i
c = UBound(result, 2) + 1
ReDim Preserve result(1 To r, 1 To c)
result(1, c) = "Grand Total"
For i = 2 To r
For k = 3 To c - 1
result(i, c) = result(i, c) + result(i, k)
Next k
Next i
sheet.Range("G13").Resize(r, c).Value = result
End Sub
