Hi Howard,

Am Mon, 8 Dec 2014 23:29:39 -0800 (PST) schrieb L. Howard:

Using the formula =MAX(COUNTIF(A$1:A$200,A1:A200)) on the worksheet, it tells me the max number of repeats is 12.

you can get all unique items and the count of them in column a in this
way:

Sub Dupes()
Dim varIn As Variant, varTemp As Variant, varOut() As Variant
Dim LRow As Long, i As Long, n As Long
Dim myDic As Object

Set myDic = CreateObject("Scripting.Dictionary")

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varIn = .Range("A1:A" & LRow)
'Creates unique items
For i = LBound(varIn) To UBound(varIn)
myDic(varIn(i, 1)) = varIn(i, 1)
Next
'array with uniqze items
varTemp = myDic.items

For i = LBound(varTemp) To UBound(varTemp)
ReDim Preserve varOut(myDic.Count, 1)
'Creates an array with the unique items and the count
'of them in column A
varOut(n, 0) = varTemp(i)
varOut(n, 1) = WorksheetFunction.CountIf(.Range("A1:A200"), varTemp(i))
n = n + 1
Next
'Writes the unique items and the count of them to columns C:D
.Range("C1").Resize(myDic.Count, 2) = varOut
End With
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional