Counting unique entries in a field

[Jeff Whitesel]

I have a spreadsheet that is 6 columns by 58000 rows. In this spreadsheet I filter data. However I want to count the number of unique entries in a list (that are not the same). See example:

ORDER RDATE RTIME PHL SQA# FDATE
XHGB 8/18/08 5:00 JW F15031 8/18/08
XWBC 8/18/08 5:05 JW F15031 8/18/08
XHCT 8/19/08 7:05 LW S15033 8/19/08
XALT 8/20/08 11:24 FS W13055 8/20/08

I am counting the number of unique entries by the SQA# field (which should be 3). What is the best way to pull this data for 58000 entries if sorting by the SQA#? Thanks

 

[N Harkawat]

Use Pivot table.

 

[TomPl]

Maybe not the best solution, but you could use <Data><Filter><Advanced
Filter> to copy a list of unique values to a different location. Then you
could count the new list.

Tom

 

[Spiky]

Another way would be to download "morefunc" UDFs and install it. (just
Google it) Use their function COUNTDIFF to give the number of unique
entries.

 

[ShaneDevenshire]

Hi,

The formula to do this is
=SUMPRODUCT(1/COUNTIF(A1:A58000,A1:58000))

Adjust the references for the column containing your data.

If this helps, please click the Yes button.

 

[JMB]

You could use
=Sumproduct((E1:E58000<>"")/Countif(E1:E58000, E1:E58000))

But a user defined function would be faster for a range that size. Here is
a UDF supplied by Charles Williams
http://msdn.microsoft.com/en-us/library/aa730921.aspx


Public Function COUNTU(theRange As Range) As Variant
Dim colUniques As New Collection
Dim vArr As Variant
Dim vCell As Variant
Dim vLcell As Variant
Dim oRng As Range

Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
vArr = oRng
On Error Resume Next
For Each vCell In vArr
If vCell <> vLcell Then
If Len(CStr(vCell)) > 0 Then
colUniques.Add vCell, CStr(vCell)
End If
End If
vLcell = vCell
Next vCell

COUNTU = colUniques.Count
End Function