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

Use Pivot table.

"Jeff Whitesel" wrote:

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

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

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.

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.

--
Thanks,
Shane Devenshire


"Jeff Whitesel" wrote:

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

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


"Jeff Whitesel" wrote:

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