Sub Click()
Dim d, A, B
Dim i, j, k
'1)创建
Set d = CreateObject("scripting.dictionary")
For k = 1 To 2
A = Sheets(k).Range("a1").CurrentRegion
For i = 2 To UBound(A)
If d.exists(A(i, 2)) Then
For j = 1 To UBound(A, 2)
If A(i, j) = "" Then A(i, j) = "的"
If Asc(d(A(i, 2))(A(1, j))) < Asc(A(i, j)) Then d(A(i, 2))(A(1, j)) = A(i, j) '比较
Next j
Else
Set d(A(i, 2)) = CreateObject("scripting.dictionary") '字典嵌套
For j = 1 To UBound(A, 2)
If A(i, j) = "" Then A(i, j) = "的"
d(A(i, 2))(A(1, j)) = A(i, j)
Next j
End If
Next i
Next k
'2)查询
ReDim A(1 To d.Count + 1, 1 To UBound(A, 2))
For j = 1 To UBound(A, 2)
A(1, j) = Sheets(1).Cells(1, j)
Next j
B = d.keys
For i = 2 To UBound(A)
For j = 1 To UBound(A, 2)
A(i, j) = d(B(i - 2))(A(1, j))
If A(i, j) = "的" Then A(i, j) = ""
Next j
Next i
'3)输出
Sheets(3).Range("a1").CurrentRegion = ""
Sheets(3).Range("a1").Resize(UBound(A), UBound(A, 2)) = A
End Sub
样表3.rar
(21.19 KB, 下载次数: 13)