Sub XYZ()
Dim i&, j&, r&, n&, k&, ik&, ir&, iDA&, rowDA&, iHD&, rowHD&, stt&, key$
Dim aDA(), aHD(), aHM(), arr(), aTong#(3 To 5), S, ST, res(), Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("DM CHUNG")
aDA = .Range("A3", .Range("C1000000").End(xlUp)).Value
End With
With Sheets("DATA")
arr = .Range("D4:M" & .Range("D1000000").End(xlUp).Row).Value
End With
ReDim res(1 To UBound(arr) * 3, 1 To 6)
For i = 1 To UBound(arr)
key = arr(i, 5) & "|" & arr(i, 3)
If Dic.exists(key) = False Then Dic(arr(i, 5)) = Dic(arr(i, 5)) & "|" & arr(i, 3)
Dic(key) = Dic(key) & "," & i
Next i
For i = 1 To UBound(aDA)
If Dic.exists(aDA(i, 2)) Then
k = k + 1: iDA = iDA + 1
rowDA = k: iHD = 0
res(k, 1) = Cells(1, iDA).Address(1, 0)
res(k, 1) = Mid(res(k, 1), 1, InStr(1, res(k, 1), "$") - 1)
res(k, 2) = aDA(i, 3)
S = Split(Dic(aDA(i, 2)), "|")
For r = 1 To UBound(S)
k = k + 1: iHD = iHD + 1
rowHD = k: stt = 0
res(k, 1) = Application.Roman(iHD)
ST = Split(Dic(aDA(i, 2) & "|" & S(r)), ",")
res(k, 2) = arr(CLng(ST(1)), 4)
For n = 1 To UBound(ST)
ir = CLng(ST(n))
key = aDA(i, 2) & "#" & arr(ir, 1)
If Dic.exists(key) = False Then
k = k + 1
Dic.Add key, k
stt = stt + 1
res(k, 1) = stt
res(k, 2) = arr(ir, 2)
res(k, 6) = arr(ir, 10)
End If
ik = Dic(key)
For j = 7 To 9
res(ik, j - 4) = res(ik, j - 4) + arr(ir, j)
res(rowDA, j - 4) = res(rowDA, j - 4) + arr(ir, j)
res(rowHD, j - 4) = res(rowHD, j - 4) + arr(ir, j)
aTong(j - 4) = aTong(j - 4) + arr(ir, j)
Next j
Next n
Next r
End If
Next i
res(k + 1, 2) = "T" & ChrW(7893) & "ng c" & ChrW(7897) & "ng:"
res(k + 1, 3) = aTong(3): res(k + 1, 4) = aTong(4): res(k + 1, 5) = aTong(5)
Sheets("BAO CAO CONG NO").Range("A4:F10000").ClearContents
Sheets("BAO CAO CONG NO").Range("A4").Resize(k + 1, 6) = res
End Sub