|
发表于 2014-4-29 13:46
|
显示全部楼层
本楼为最佳答案
- Private Sub CommandButton1_Click()
- Dim arr1(), arr2(), brr1(), brr2()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With Sheets("2")
- .Range("a:i,o:w,ac:ak").ClearContents
- For y = 0 To 2
- For x = 1 To 2
- mc = 1 + y * 14 '原始数所在的列
- mr = IIf(x = 1, 1, 115) '原始数所在的行
- arr1 = Getarr(mr, mc): brr1 = GetBrr(arr1) '表1,去重
- arr2 = Getarr(mr + 57, mc): brr2 = GetBrr(arr2) '表2,去重
- r1 = UBound(brr1): c = UBound(brr1, 2)
- r2 = UBound(brr2)
-
- Dim DelRng As Range
- With Sheet3
- .UsedRange.ClearContents
- Set DelRng = .Cells(10000, 1).Resize(1, 9)
- .[a1].Resize(r1, c) = brr1
- .Cells(r1 + 2, 1).Resize(UBound(brr2), c) = brr2
- For i = 1 To UBound(brr1) '表1+表2去重
- zf = Join(Application.Index(brr1, i), "")
- If Len(zf) > 0 Then d(zf) = i
- Next
-
- k = 0 'k表示表1、表2各自配对数(表1或表2中删掉的行数)
- For i = 1 To UBound(brr2)
- zf = Join(Application.Index(brr2, i), "")
- If d.exists(zf) Then '表1、表2中有数相同
- k = k + 1
- Set DelRng = Union(DelRng, .Cells(d(zf), 1).Resize(1, 9), .Cells(i + r1 + 2, 1).Resize(1, 9))
- End If
- Next
- DelRng.Delete shift:=xlUp
- End With
-
- xr = IIf(x = 1, 1, 105) '显示结果所在的行
- .Cells(xr, mc).Resize(r1 - k, 9) = Sheet3.Cells(1, 1).Resize(r1 - k, 9).Value
- .Cells(xr + 52, mc).Resize(51, 9) = Sheet3.Cells(r1 - k + 2, 1).Resize(51, 9).Value
- Next
- Next
- End With
- End Sub
- Function Getarr(r, c) '取得cells(r,c)为左上角的数组(56*9)
- ReDim arr(1 To 56, 1 To 9)
- xarr = Sheet1.Cells(r, c).Resize(56, 9)
- For i = 1 To 56
- For j = 1 To 9
- arr(i, j) = xarr(i, j)
- Next
- Next
- Getarr = arr
- End Function
- Function GetBrr(arr()) '数组arr中去掉相同行
- Set d = CreateObject("scripting.dictionary")
- ReDim zff(1 To UBound(arr, 2))
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 1 To UBound(arr)
- For k = 1 To UBound(arr, 2)
- zff(k) = arr(i, k)
- Next
- zf = Join(zff, ",")
- d(zf) = d(zf) + 1 '记录重复次数
- Next
- dk = d.keys: dt = d.items
- For i = 0 To UBound(dk)
- If dt(i) = 1 Then
- s = s + 1
- For x = 1 To UBound(arr, 2)
- brr(s, x) = Split(dk(i), ",")(x - 1)
- Next
- End If
- Next
- GetBrr = brr
- End Function
复制代码 |
|