|
本帖最后由 zjdh 于 2011-8-13 18:05 编辑
Sub test()
Dim arr(), D, I%, J%, N$, K
Sheets("sheet3").Range("A:D").ClearContents
Set D = CreateObject("scripting.dictionary")
For I = 3 To 11
For J = 20 To Cells(65536, 1).End(3).Row
If Cells(J, 1).Value <> "" Then
N = Cells(J, 1) & "|" & Cells(19, I) & "|" & Cells(J, I)
If Not D.exists(N) Then
D.Add N, Cells(J, 2)
Else
D(N) = D(N) & "," & Cells(J, 2)
End If: End If: Next: Next
K = D.keys
ReDim arr(1 To D.Count, 1 To 4)
For I = 1 To D.Count
arr(I, 1) = Split(K(I - 1), "|")(0)
arr(I, 2) = D(K(I - 1))
arr(I, 3) = Split(K(I - 1), "|")(1)
arr(I, 4) = Split(K(I - 1), "|")(2)
Next
Sheets("sheet3").Cells(4, 1).Resize(1, 4) = Array("年级", "班别", "科目", "代课老师")
Sheets("sheet3").Cells(5, 1).Resize(D.Count, 4) = arr
End Sub
|
|