|
发表于 2014-4-28 19:58
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr, crr, drr, d, d2, i&, s&, s2&, j%
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Sheets("表一").Range("b2").CurrentRegion
- brr = Sheets("表二").Range("b2").CurrentRegion
- ReDim crr(1 To UBound(arr), 1 To UBound(arr, 2))
- ReDim drr(1 To 60000, 1 To UBound(arr, 2))
- For i = 2 To UBound(brr)
- zf = Join(Application.Index(brr, i, 0), ",")
- d(zf) = d(zf) + 1
- If Not d2.exists(zf) Then
- d2(zf) = ""
- s2 = s2 + 1
- For j = 1 To UBound(brr, 2)
- drr(s2, j) = brr(i, j)
- Next
- End If
- Next
- For i = 2 To UBound(arr)
- zf = Join(Application.Index(arr, i, 0), ",")
- If d.exists(zf) And d(zf) > 0 Then
- d(zf) = d(zf) - 1
- s = s + 1
- For j = 1 To UBound(arr, 2)
- crr(s, j) = arr(i, j)
- Next
- End If
- If Not d2.exists(zf) Then
- d2(zf) = ""
- s2 = s2 + 1
- For j = 1 To UBound(arr, 2)
- drr(s2, j) = arr(i, j)
- Next
- End If
- Next
- Sheet3.Range("a2").Resize(s, UBound(crr, 2)) = crr
- Sheet4.Range("a2").Resize(s2, UBound(drr, 2)) = drr
- End Sub
复制代码 |
|