|
- Sub Macro1()
- Dim arr, brr, crr, d, d2, i&, j%, k%
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 1 To UBound(arr)
- If Not d2.exists(arr(i, 8)) Then
- h = h + 1: d2(arr(i, 8)) = h
- For j = 1 To UBound(arr, 2)
- brr(h, j) = arr(i, j)
- Next
- Else
- n = d2(arr(i, 8))
- For k = 9 To UBound(arr, 2)
- If brr(n, k) = "" Then l = k: Exit For
- Next
- For k2 = 9 To UBound(arr, 2)
- If arr(i, k2) <> "" Then
- brr(n, l) = arr(i, k2)
- l = l + 1
- End If
- Next
- End If
- Next
- For i = 2 To h
- js = 0
- For j = 9 To UBound(brr, 2)
- If brr(i, j) Like brr(i, 8) & "*" Then js = js + 1: z = brr(i, 9): brr(i, 9) = brr(i, j): brr(i, j) = z: Exit For
- Next
- If js = 0 Then
- For j2 = UBound(brr, 2) - 1 To 9 Step -1
- brr(i, j2 + 1) = brr(i, j2)
- Next
- brr(i, 9) = "-"
- End If
- Next
- ReDim crr(1 To UBound(brr) - 1, 1 To UBound(brr, 2) - 8)
- For i = 2 To UBound(brr)
- s = 0
- For j = 9 To UBound(brr, 2)
- brr(i, j) = Replace(brr(i, j), brr(i, 8), "")
- zf = brr(i, j)
- For k = 1 To Len(zf)
- z = Mid(zf, k, 1)
- If Not d.exists(z) Then
- d(z) = ""
- s = s + 1
- crr(i - 1, s) = z
- End If
- Next
- Next
- If s > n Then n = s
- d.RemoveAll
- Next
- Sheet2.Activate
- Range("a10").Resize(h, UBound(brr, 2)) = brr
- Range("i11").Resize(UBound(crr), n) = crr
- End Sub
复制代码 |
|