Count Unique Records
Posted by Andonny on November 15, 2001 9:18 PM
Hi,
I would like to count only records which are unique in a column. If it appears more then once in a column it should leave it out from the count.
Your help is very much appreciated
Andonny
Posted by Ben O. on November 15, 2001 10:06 PM
Andonny,
This code should work:
Sub CountUnique()
' Data must be sorted in ascending order
' Change 2 to the first row of your data and 100 to your last
Dim ucount As Integer
ucount = 0
Dim rCol As Integer
' Change rCol to the column of data you want to analyze
rCol = 1
For x = 2 To 100
If Cells(x + 1, rCol).Value <> Cells(x, rCol).Value Then
ucount = ucount + 1
End If
Next x
r = MsgBox(ucount & " unique records in column " & rCol, , "Unique Count")
End Sub
-Ben
Posted by Richard S on November 15, 2001 10:07 PM
You could use Data|Filter|Advanced to filter the data to another location, and check the unique records box. The number of rows it takes is the number of unique records you have.
HTH
Richard
Posted by Aladin Akyurek on November 15, 2001 10:57 PM
Since you don't want to count an item with a freq of occurrence > 1, use either:
=SUMPRODUCT((COUNTIF(range,range)=1)+0)
or:
=SUM((FREQUENCY(range,range)=1)+0)
If my interpretation above is not correct, that is, if you want to count "types", have a look at:
Aladin
