Sub linhtinh()
Dim arr, arr1, dic As Object, lr As Long, i As Long, j As Long, a As Long, dk As String, darr, b As Long, c As Integer, T As String
Dim s As String, d As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("sheet2")
arr = .Range("A2:AA2").Value
For i = 2 To 9
dic.Item(arr(1, i) & "CD") = i
Next i
For i = 10 To 17
dic.Item(arr(1, i) & "TT") = i
Next i
For i = 18 To 27
dic.Item(arr(1, i) & "BX") = i
Next i
End With
With Sheets("sheet1")
lr = .Range("D" & Rows.Count).End(xlUp).Row
arr = .Range("C2:G" & lr).Value
End With
ReDim arr1(1 To UBound(arr, 1), 1 To 27)
For i = 1 To UBound(arr, 1)
If Not dic.exists(arr(i, 3)) Then
a = a + 1
dic.Add arr(i, 3), Array(a, "#" & arr(i, 1) & "#")
arr1(a, 1) = arr(i, 3)
Else
s = dic.Item(arr(i, 3))(1)
d = dic.Item(arr(i, 3))(0)
If InStr(1, s, "#" & arr(i, 1) & "#") Then
a = a + 1
arr1(a, 1) = arr(i, 3)
dic.Item(arr(i, 3)) = Array(a, "#" & arr(i, 1) & "#")
Else
s = s & arr(i, 1) & "#"
dic.Item(arr(i, 3)) = Array(d, s)
End If
End If
b = dic.Item(arr(i, 3))(0)
c = dic.Item(arr(i, 1) & "CD")
arr1(b, c) = arr(i, 2)
c = dic.Item(arr(i, 1) & "TT")
arr1(b, c) = arr(i, 5)
c = dic.Item(arr(i, 1) & "BX")
arr1(b, c) = arr(i, 4)
Next i
With Sheets("sheet2")
lr = .Range("A" & Rows.Count).End(xlUp).Row
If lr > 2 Then .Range("A3:AA" & lr).ClearContents
If a Then .Range("A3").Resize(a, 27).Value = arr1
End With
End Sub