|
发表于 2011-10-28 11:51
|
显示全部楼层
本楼为最佳答案
本帖最后由 mxg825 于 2011-10-28 12:01 编辑
- Sub 生成数据()
- Dim D As New Dictionary, LD As New Dictionary, ID As New Dictionary '字典
- Dim Arr1, Arr2, Arr3 '数组
- Dim 列&, 行&, L&, R&, X& '行 列 号变量
- Arr1 = Sheets("專長表").Range("a2:B" & Sheets("專長表").Range("a65536").End(xlUp).Row)
- Arr2 = Sheets("所屬公司").Range("a2:B" & Sheets("所屬公司").Range("a65536").End(xlUp).Row)
- L = 1: R = 1
- LD("公司\專長") = L
- For X = 1 To UBound(Arr1)
- If Not LD.Exists(Arr1(X, 2)) Then
- L = L + 1
- LD(Arr1(X, 2)) = L
- End If
- D(Arr1(X, 1)) = LD(Arr1(X, 2))
- Next
- If L = 1 Then Exit Sub
- Arr3 = Application.Transpose(LD.keyS)
- For X = 1 To UBound(Arr2)
- 行 = D(Arr2(X, 2))
- If ID.Exists(Arr2(X, 1)) Then
- 列 = ID(Arr2(X, 1))
- Arr3(行, 列) = Arr3(行, 列) + 1
- Else
- R = R + 1
- ID(Arr2(X, 1)) = R
- ReDim Preserve Arr3(1 To L, 1 To R)
- Arr3(1, R) = Arr2(X, 1)
- Arr3(行, R) = 1
- End If
- Next
- Sheets("生成").Cells.Clear
- Sheets("生成").Range("A1").Resize(R, L) = Application.Transpose(Arr3)
- MsgBox "完成", , "mxg825提示"
- End Sub
复制代码
|
|