- Sub x()
- Dim a, x%, d, b(), s%, r%
- a = Range("a1").CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- For x = 1 To UBound(a)
- If d(a(x, 4)) Then
- r = d(a(x, 4))
- b(2, r) = b(2, r) & "," & a(x, 5)
- b(3, r) = b(3, r) & "," & a(x, 2)
- If a(x, 3) <> "" Then b(3, r) = b(3, r) & ":" & a(x, 3)
- Else
- s = s + 1
- d(a(x, 4)) = s
- ReDim Preserve b(1 To 3, 1 To s)
- b(1, s) = a(x, 4)
- b(2, s) = a(x, 5)
- b(3, s) = a(x, 2) & ":" & a(x, 3)
- End If
- Next
- [j1].Resize(s, 3) = Application.Transpose(b)
- End Sub
复制代码 |