|
楼主 |
发表于 2011-8-14 14:55
|
显示全部楼层
此方法可以运用所有表- Sub 求单列重复与不重复值ABC()
- Dim d As Object, i&, k&, m&, ar, arr(), arrr()
- Set d = CreateObject("scripting.dictionary")
- With Sheets("sheet1")
- .[c:e].ClearContents
- .[c1:e1] = Array("不重复值", "重复值", "重复次数")
- ar = .Range("a2", .[a65536].End(3))
- ReDim arr(1 To UBound(ar), 1 To 2)
- For i = 1 To UBound(ar)
- If Not d.Exists(ar(i, 1)) Then
- k = k + 1
- d(ar(i, 1)) = k
- arr(k, 1) = ar(i, 1)
- End If
- arr(d(ar(i, 1)), 2) = arr(d(ar(i, 1)), 2) + 1
- Next
- ReDim arrr(1 To k, 1 To 3)
- For i = 1 To k
- If arr(i, 2) = 1 Then
- j = j + 1
- arrr(j, 1) = arr(i, 1)
- ElseIf arr(i, 2) > 1 Then
- m = m + 1
- arrr(m, 2) = arr(i, 1)
- arrr(m, 3) = arr(i, 2)
- End If
- Next
- .[c2].Resize(k, 3) = arrr
- End With
- End Sub
复制代码 |
|