- Sub demo()
- Dim ar, d As Object, d1 As Object, a, b, re(), br(), xx
- a = Cells(Rows.Count, 8).End(3).Row
- b = Cells(1, Columns.Count).End(1).Column
- ar = Range(Cells(2, 8), Cells(a, b))
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- ReDim re(1 To UBound(ar), 1 To 2)
- For i = 1 To UBound(ar)
- If Not d.Exists(ar(i, 1)) Then
- cnt = cnt + 1
- d(ar(i, 1)) = cnt
- re(cnt, 1) = ar(i, 1)
- End If
- For j = 1 To UBound(ar, 2)
- re(d(ar(i, 1)), 2) = Replace(re(d(ar(i, 1)), 2) & ar(i, j), ar(i, 1), "")
- Next
- If Len(re(d(ar(i, 1)), 2)) > m Then m = Len(re(d(ar(i, 1)), 2))
- Next
- ReDim br(1 To d.Count, 1 To m + 1)
- For i = 1 To d.Count
- br(i, 1) = re(i, 1)
- For j = 1 To Len(re(i, 2))
- d1(Mid(re(i, 2), j, 1)) = ""
- Next
- xx = d1.keys
- For j = 0 To UBound(xx)
- br(i, j + 2) = xx(j)
- Next
- d1.RemoveAll
- Next
- Sheet2.Range("h2").Resize(UBound(br), UBound(br, 2)) = br
- End Sub
复制代码 附件楼下
|