|
- Sub tt()
- Dim sh As Worksheet
- Set d = CreateObject("scripting.dictionary") '记录重名
- Set d1 = CreateObject("scripting.dictionary") '记录姓名出现的次数
- r = Sheet1.[a65536].End(3).Row
- arr = Sheet1.Range("a1:o" & r)
- Set sh = Sheets("结果")
- For i = 1 To UBound(arr)
- x = arr(i, 10) '姓名
- d1(x) = d1(x) + 1 '姓名出现的次数
- If Not d.exists(x) Then
- n = n + 1 '姓名在新表中的行位置
- d(x) = n
- sh.Cells(n, 1).Resize(1, 15) = Sheet1.Cells(i, 1).Resize(1, 15).Value '第一次出现,复制1--15列
- Else '第二次以上出现
- p = d(x)
- c = 6 + 5 * d1(x)
- sh.Cells(p, c).Resize(1, 5) = Sheet1.Cells(i, 11).Resize(1, 5).Value '复制11-16列,到新表指定列
- sh.Cells(1, c).Resize(1, 5) = Sheet1.Cells(1, 11).Resize(1, 5).Value '新表头
- For j1 = 11 To c - 5 Step 5 '判断毕业日期并排序
- arr1 = sh.Cells(p, j1).Resize(1, 5)
- For j2 = 16 To c Step 5
- arr2 = sh.Cells(p, j2).Resize(1, 5)
- If CDate(arr1(1, 3)) > CDate(arr2(1, 3)) Then '前一日期大于后一日期,交换
- sh.Cells(p, j1).Resize(1, 5) = arr2
- sh.Cells(p, j2).Resize(1, 5) = arr1
- End If
- Next
- Next
- End If
- Next
- sh.Activate
- End Sub
复制代码 |
|