[已解决]选出重复行最多的行

ymq123

1、把表1中重复行数最多的行选出来生成表2。生成结果如表3
2、表1中重复最多的行数字是 1 2 4 5 6 和 2 3 4 6 7。其它行重复少或不重复，全部要删除。
谢谢！

最佳答案

月排行榜 / 总排行榜

妞叫七七

2013-6-8 23:30

写了一个，可以提出重复最多的一行，Private Sub CommandButton1_Click()
Dim d As Object, sr As String
Set d = CreateObject("scripting.dictionary")
For x = 2 To 18
sr = ""
  For y = 1 To 7
    sr = sr & "-" & Replace(Cells(x, y), " ", "")
  Next y
  d(sr) = d(sr) + 1
Next x
arr = d.keys
arr1 = d.items
m = Application.Max(arr1)
For x = 0 To UBound(arr1)
If arr1(x) = m Then
   k = k + 1
   arr2 = Split(arr(x), "-")
   For y = 1 To 7
    Cells(k + 1, y + 9) = arr2(y)
   Next y
End If
Next x
End Sub

跳转到最佳答案楼层

妞叫七七

没看明白，最多的行怎么会有两个呢，最多是怎么算出来的

妞叫七七

写了一个，可以提出重复最多的一行，Private Sub CommandButton1_Click()
Dim d As Object, sr As String
Set d = CreateObject("scripting.dictionary")
For x = 2 To 18
sr = ""
  For y = 1 To 7
    sr = sr & "-" & Replace(Cells(x, y), " ", "")
  Next y
  d(sr) = d(sr) + 1
Next x
arr = d.keys
arr1 = d.items
m = Application.Max(arr1)
For x = 0 To UBound(arr1)
If arr1(x) = m Then
   k = k + 1
   arr2 = Split(arr(x), "-")
   For y = 1 To 7
    Cells(k + 1, y + 9) = arr2(y)
   Next y
End If
Next x
End Sub