点击文件名下载附件
代码如下:
Sub 生成体检单位及人数明细()
Dim d As Object, arr, brr(), i&, j&, m&, s&
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.Cells(3, 3).Resize(Sheet1.Cells(Rows.Count, 3).End(3).Row - 2, 2).Value
ReDim brr(1 To UBound(arr), 1 To 3)
For i = 1 To UBound(arr)
s = d(arr(i, 1) & arr(i, 2))
If s = Empty Then
m = m + 1
d(arr(i, 1) & arr(i, 2)) = m
s = m
brr(s, 1) = arr(i, 2)
brr(s, 2) = arr(i, 1)
End If
brr(s, 3) = brr(s, 3) + 1
Next
d.RemoveAll
For i = 1 To m
If Not d.Exists(brr(i, 1)) Then
d(brr(i, 1)) = "安排" & brr(i, 2) & brr(i, 3) & "人"
Else
d(brr(i, 1)) = d(brr(i, 1)) & "、" & brr(i, 2) & brr(i, 3) & "人"
End If
Next
With Sheet3
.[a1].CurrentRegion.Offset(1).ClearContents
.[a2].Resize(d.Count).Value = Application.Transpose(d.Keys)
.[b2].Resize(d.Count).Value = Application.Transpose(d.Items)
End With
Set d = Nothing
End Sub