Count Distinct Values?

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>
>

I have a similar situation. However, I have 3 sheets inside my spreadsheet and I need to count the unique values in the same column across all three sheets.

For example, I can use the following formula (that I found in this thread) to count the values in one sheet:

=SUMPRODUCT((G12:G247<>"")/COUNTIF(G12:G247,G12:G247&""))

If my sheets are named SheetA, SheetB, SheetC, how can I modify this forumula to look at all three sheets? The column and row values are the same in each sheet. Since it is very likely that the same value will appear in one or more of the sheets, I can't just add the individual sheet sums together.

Thanks for the feed-back.
--

Regards,

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

"bj" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>
>
>