|
本帖最后由 sliang28 于 2013-12-21 16:02 编辑
采用校长下棋原理,避免了楼上多次使用字典,使用多个字典,多次写入单元格.- Sub sliang28()
- Dim arr, brr(1 To 10000, 1 To 500) As String
- Dim d1 As Object, d2 As Object, d3 As Object
- Dim iRow As Long, mycol As Integer
- Dim i&, j&, k&, x&
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- With Sheets("Sheet1")
- iRow = .Range("G65536").End(3).Row
- arr = .Range("G2:H" & iRow)
- For i = 1 To UBound(arr)
- If Not d1.Exists(arr(i, 2)) Then '如果不存在二级分类
- d1(arr(i, 2)) = d1.Count + 1 '记录结果数组行号
- brr(d1(arr(i, 2)), 1) = arr(i, 2) '将二级分类写入结果数组第一列
- End If
- If Not d2.Exists(arr(i, 1) & arr(i, 2)) Then '如果不存在二级分类与分类合并
- d2(arr(i, 1) & arr(i, 2)) = "" '将合并值写入字典
- d3(d1(arr(i, 2))) = d3(d1(arr(i, 2))) + 1 '将结果数组的行号写入字典,并记录当前行的最大列号
- brr(d1(arr(i, 2)), d3(d1(arr(i, 2))) + 1) = arr(i, 1) '按照字典记录的当前行的最大列号把分类写入结果数组
- End If
- If mycol < d3(d1(arr(i, 2))) Then mycol = d3(d1(arr(i, 2))) '记录结果数组的最大列号,此判断可以不要,写入时用最大列号写入,即500
- Next
- .Range("J2").Resize(d1.Count, mycol + 1) = brr
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|