|
- Sub yy()
- Dim Arr, i&, xd%, aa, bb
- Dim d, m&, n&, j&, Arr2, x$, y$, z$
- Set d = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- Sheet3.Activate
- [a2:i2000].ClearContents
- Arr = Sheet1.[a1].CurrentRegion
- Arr2 = Sheet2.[a1].CurrentRegion
- For i = 2 To UBound(Arr)
- x = Arr(i, 5)
- d(x) = d(x) & i & ","
- Next
- m = 1
- For i = 2 To UBound(Arr2)
- x = Arr2(i, 5): xd = 0
- If d.exists(x) Then
- y = Arr2(i, 1) & "|" & Arr2(i, 2) & "|" & Arr2(i, 3)
- If d(x) = "" Then GoTo 50
- bb = Left(d(x), Len(d(x)) - 1)
- If InStr(bb, ",") Then
- aa = Split(bb, ",")
- For j = 0 To UBound(aa)
- z = Arr(aa(j), 1) & "|" & Arr(aa(j), 2) & "|" & Arr(aa(j), 3)
- If z = y Then xd = 1
- Next
- If xd = 0 Then
- m = m + 1
- Cells(m, 5).Resize(1, 3) = Split(y, "|")
- Cells(m, 8) = x
- End If
- Else
- z = Arr(bb, 1) & "|" & Arr(bb, 2) & "|" & Arr(bb, 3)
- If z <> y Then
- m = m + 1
- Cells(m, 5).Resize(1, 3) = Split(y, "|")
- Cells(m, 8) = x
- End If
- End If
- End If
- 50:
- Next
- d.RemoveAll
- For i = 2 To UBound(Arr2)
- x = Arr2(i, 5)
- d(x) = d(x) & i & ","
- Next
- n = 1
- For i = 2 To UBound(Arr)
- x = Arr(i, 5): xd = 0
- If d.exists(x) Then
- y = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
- If d(x) = "" Then GoTo 100
- bb = Left(d(x), Len(d(x)) - 1)
- If InStr(bb, ",") Then
- aa = Split(bb, ",")
- For j = 0 To UBound(aa)
- z = Arr2(aa(j), 1) & "|" & Arr2(aa(j), 2) & "|" & Arr2(aa(j), 3)
- If z <> y Then xd = 1
- Next
- If xd = 0 Then
- n = n + 1
- Cells(n, 1).Resize(1, 3) = Split(z, "|")
- Cells(n, 4) = x
- End If
- Else
- z = Arr2(bb, 1) & "|" & Arr2(bb, 2) & "|" & Arr2(bb, 3)
- If z <> y Then
- n = n + 1
- Cells(n, 1).Resize(1, 3) = Split(y, "|")
- Cells(n, 4) = x
- End If
- End If
- End If
- 100:
- Next
- Application.ScreenUpdating = True
- Set d = Nothing
- End Sub
复制代码 |
|