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

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.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"bill_morgan" wrote in message
...
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

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

"JulieD" wrote:

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.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"bill_morgan" wrote in message
...
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

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!
==============================================
&""))

"bill_morgan" wrote in message
...
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

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 !
--------------------------------------------------------------------

"RagDyeR" wrote in message
...
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!
==============================================
&""))

"bill_morgan" wrote in message
...
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

fantastic

"RagDyeR" wrote:

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 !
--------------------------------------------------------------------

"RagDyeR" wrote in message
...
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!
==============================================
&""))

"bill_morgan" wrote in message
...
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

Thanks for the feed-back.
--

Regards,

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

"bj" wrote in message
...
fantastic

"RagDyeR" wrote:

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 !
--------------------------------------------------------------------

"RagDyeR" wrote in message
...
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!
==============================================
&""))

"bill_morgan" wrote in message
...
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,

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

"RagDyeR" wrote:

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 !
--------------------------------------------------------------------

"RagDyeR" wrote in message
...
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!
==============================================
&""))

"bill_morgan" wrote in message
...
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