Sub zz()
Dim d, ar, br
Set d = CreateObject("Scripting.Dictionary")
ar = Range("A1:H" & [a65536].End(3).Row)
For i = 2 To UBound(ar)
If ar(i, 2) <> "" And Len(ar(i, 3)) = 11 And Len(ar(i, 6)) = 18 And _
(ar(i, 5) = "A" Or ar(i, 5) = "AA" Or ar(i, 5) = "AAA") Then
ar(i, 8) = 1
Else
ar(i, 8) = 0
End If
Next
ReDim br(1 To UBound(ar), 1 To 6)
For i = 2 To UBound(ar)
s = ar(i, 4)
If d(s) = "" Then
m = m + 1: d(s) = m: br(m, 1) = s
If ar(i, 2) <> "" Then br(m, 2) = 1
If Len(ar(i, 3)) = 11 Then br(m, 3) = 1
If ar(i, 5) = "A" Or ar(i, 5) = "AA" Or ar(i, 5) = "AAA" Then br(m, 4) = 1
If Len(ar(i, 6)) = 18 Then br(m, 5) = 1
If ar(i, 8) = 1 Then br(m, 6) = 1
Else
If ar(i, 2) <> "" Then br(d(s), 2) = br(d(s), 2) + 1
If Len(ar(i, 3)) = 11 Then br(d(s), 3) = br(d(s), 3) + 1
If ar(i, 5) = "A" Or ar(i, 5) = "AA" Or ar(i, 5) = "AAA" Then br(d(s), 4) = br(d(s), 4) + 1
If Len(ar(i, 6)) = 18 Then br(d(s), 5) = br(d(s), 5) + 1
If ar(i, 8) = 1 Then br(d(s), 6) = br(d(s), 6) + 1
End If
Next
Sheet2.[a3].Resize(m, 6) = br
[h1].Resize(UBound(ar)) = Application.Index(ar, 0, 8)
End Sub
|