|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub test()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With ActiveSheet
r = .Cells(Rows.Count, 18).End(xlUp).Row
ar = .Range("r1:s" & r)
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" And Trim(ar(i, 2)) <> "" Then
If d(Trim(ar(i, 2))) = "" Then
d(Trim(ar(i, 2))) = ar(i, 1)
Else
d(Trim(ar(i, 2))) = d(Trim(ar(i, 2))) & "|" & ar(i, 1)
End If
End If
Next i
For Each k In d.keys
rr = Split(d(k), "|")
m = UBound(rr)
If m <= 9 Then
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(rs, 1) = k
For j = 0 To m
.Cells(rs, j + 2) = rr(j)
Next j
ElseIf m > 9 Then
For j = 0 To m Step 10
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(rs, 1) = k
y = 1
For s = j To j + 9
y = y + 1
If s > m Then GoTo 10
.Cells(rs, y) = rr(s)
Next s
10:
Next j
End If
Next k
End With
End Sub
|
|