Public Sub GPE()
Dim Dic As Object, sArr, dArr, Ws As Worksheet
Dim I As Long, K As Long, N As Long, R As Long, T As Long
Set Dic = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
ReDim dArr(1 To 10000, 1 To 500)
K = 1: N = 3
dArr(1, 1) = "Tên Chi nhánh": dArr(1, 2) = "Khách hàng": dArr(1, 3) = "Thay doi"
For Each Ws In Worksheets
If Ws.Name <> "Table" Then
sArr = Ws.Range("A1").CurrentRegion.Value
For I = 3 To UBound(sArr)
If Not Dic.Exists(Ws.Name) Then
N = N + 1
dArr(1, N) = Ws.Name
Dic.Add Ws.Name, N
End If
If Not Dic.Exists(sArr(I, 1) & "#" & sArr(I, 2)) Then
K = K + 1
Dic.Add sArr(I, 1) & "#" & sArr(I, 2), K
dArr(K, 1) = sArr(I, 1)
dArr(K, 2) = sArr(I, 2)
End If
R = Dic.Item(sArr(I, 1) & "#" & sArr(I, 2))
T = Dic.Item(Ws.Name)
dArr(R, T) = dArr(R, T) + sArr(I, 3)
dArr(R, 3) = dArr(R, 5) - dArr(R, 4)
Next
End If
Next
If K Then
With Sheet1
.Range("H3").Resize(K, 3).Value = dArr
.Range("H4").Resize(K - 1, 3).Sort Range("H3"), xlAscending, Range("I3"), , xlAscending
End With
End If
Application.ScreenUpdating = True
Set Dic = Nothing
End Sub