'项目号与项目名称规则不唯一,有一个需要手工确认,,,
Option Explicit
Sub test()
Dim arr, i, j, p, t, m
arr = Sheets("运动项目").[a1].CurrentRegion.Offset(1)
Call bsort(arr, 1, UBound(arr, 1) - 1, 1, UBound(arr, 2), 1)
For i = 1 To UBound(arr, 1) - 1
t = t & "、" & arr(i, 3)
If arr(i, 1) <> arr(i + 1, 1) Or i = UBound(arr, 1) - 1 Then
m = m + 1
For j = 1 To 6
arr(m, j) = arr(i, j)
Next
arr(m, 7) = i - p: arr(m, 8) = "?": arr(m, 9) = Mid(t, 2)
t = vbNullString: p = i
End If
Next
With Sheets("需要的结果").[a2]
.Resize(UBound(arr, 1), UBound(arr, 2)).ClearContents
.Resize(m, 9) = arr
End With
End Sub
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next
Next
End Function |