|
Sub paim()
Dim i, j, k, m, irow
Dim t
t = Timer
Dim ar, br, cr
Dim d, d1 As Object
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
irow = Sheet1.[L65536].End(xlUp).Row
ar = Sheet1.Range("j1:p" & irow)
For i = 5 To irow
d1(ar(i, 4) & ar(i, 5) & ar(i, 6)) = ar(i, 3)
If Not d.exists(ar(i, 3)) Then
d(ar(i, 3)) = ar(i, 7)
Else
If ar(i, 7) > d(ar(i, 3)) Then
d(ar(i, 3)) = ar(i, 7)
End If
End If
Next
Sheet1.[a5].Resize(100, 7).ClearContents
Sheet1.[c5].Resize(d.Count, 1) = WorksheetFunction.Transpose(d.keys)
Sheet1.[g5].Resize(d.Count, 1) = WorksheetFunction.Transpose(d.items)
Sheet1.Range("a4:g" & d.Count + 1).Sort key1:=Columns("g"), order1:=xlDescending, Header:=xlYes
br = Sheet1.[a5].Resize(d.Count, 7)
For j = 1 To d.Count
For i = 5 To irow
If br(j, 3) = ar(i, 3) And br(j, 7) = ar(i, 7) Then
br(j, 2) = ar(i, 2)
For k = 4 To 6
br(j, k) = ar(i, k)
Next
ar(i, 3) = ""
End If
Next
Next
Sheet1.[a5].Resize(d.Count, 7) = br
ReDim cr(1 To 10, 1 To 7)
For i = 5 To irow
If ar(i, 3) <> "" Then
m = m + 1
cr(m, 2) = ar(i, 2)
For k = 4 To 7
cr(m, k) = ar(i, k)
Next
cr(m, 3) = d1(cr(m, 4) & cr(m, 5) & cr(m, 6))
End If
Next
Sheet1.Cells(5 + d.Count, 1).Resize(m, 7) = cr
Sheet1.Cells(5 + d.Count, 1).Resize(m, 7).Sort key1:=Columns("g"), order1:=xlDescending, Header:=xlNo
For i = 5 To irow
Sheet1.Cells(i, 1) = i - 4
Next
MsgBox Timer - t
MsgBox "ok"
End Sub |
|