Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically adjust numbers in a column when data is deleted | Excel Worksheet Functions | |||
Add selected numbers in a column that are a different color | Excel Discussion (Misc queries) | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) |