Finding unique numbers in a column
 

Is there any way to find the number of unique values among a set of
values in a column in an excel sheet. I would also like to know the
number of times each value appears in the column.

Thanks


--
coolkid397
------------------------------------------------------------------------
coolkid397's Profile: http://www.excelforum.com/member.php...o&userid=24319
View this thread: http://www.excelforum.com/showthread...hreadid=379248

There was also a similar thread under Excel Worksheet functions titled
"Delete duplicates".

Solution provided was to use:
DataFilterAdvanced filterCopy to another location and unique records
only.

Once u get ur unique numbers in a separate table u can then use
COUNTIF(range,criteria) to count the occurence of each unique number in
the original list.

Cheers.


--
terabar
------------------------------------------------------------------------
terabar's Profile: http://www.excelforum.com/member.php...o&userid=24272
View this thread: http://www.excelforum.com/showthread...hreadid=379248

To identify the number of dupes, say in A1:A1000 then use this formula in
another column (Say E2 and copy down)

=COUNTIF($A$1:$A$1000,A1)

Other ways of handling dupes can be found here

http://www.cpearson.com/excel/duplicat.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"coolkid397" wrote
in message ...

Is there any way to find the number of unique values among a set of
values in a column in an excel sheet. I would also like to know the
number of times each value appears in the column.

Thanks


--
coolkid397
------------------------------------------------------------------------
coolkid397's Profile:
http://www.excelforum.com/member.php...o&userid=24319
View this thread: http://www.excelforum.com/showthread...hreadid=379248

Hi!

Assume your list is in the range A1:A100

Count of uniques:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I would also like to know the number of times each value appears in the
column.

One way to do this:

Use DataAdvanced Filter to copy unique values to another location. Assume
that new location is B1:B10. In C1 enter this formula and copy down to C10:

=COUNTIF(A$1:A$100,B1)

Biff

"coolkid397" wrote
in message ...

Is there any way to find the number of unique values among a set of
values in a column in an excel sheet. I would also like to know the
number of times each value appears in the column.

Thanks


--
coolkid397
------------------------------------------------------------------------
coolkid397's Profile:
http://www.excelforum.com/member.php...o&userid=24319
View this thread: http://www.excelforum.com/showthread...hreadid=379248