Count Distinct Values?

[Guest]

The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A. What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer. But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

 

[JulieD]

Hi Bill

check out
http://www.cpearson.com/excel/duplicat.htm
about half way down the page there's a section entitled counting unique
entries in a range which give a couple of methods.

 

[RagDyeR]

Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000

With this formula, you *cannot* use an entire column as a reference (A:A).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
&""))

The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A. What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer. But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

 

[RagDyeR]

For some reason, the end of the formula was cut off.

Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Try this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000

With this formula, you *cannot* use an entire column as a reference (A:A).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
&""))

The sheet contains 30,000 rows. I need to determine the number of distinct
(unique) values in column A. What is the most efficient way to do this in
Excel?

For example, I can pull the data into Access and use a select query to
"group by" column A to get the answer. But I need to get the answer within
Excel.

Thanks for your help ...

Bill Morgan

 

[Guest]

fantastic

 

[RagDyeR]

Thanks for the feed-back.

 

[Guest]

Thank you, Julie. I'm going there now to check it out....

 

[Guest]

RagDyeR,

This works...! Not sure how, yet, but I am working on that. Thanks so much
for your reply.