Finding unique numbers in a column

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

Thank

--
coolkid39
-----------------------------------------------------------------------
coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431
View this thread: http://www.excelforum.com/showthread.php?threadid=37924

0
6/15/2005 6:01:57 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
395 Views

Similar Articles

[PageSpeed] 13

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

Solution provided was to use:
Data>Filter>Advanced filter>Copy 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?action=getinfo&userid=24272
View this thread: http://www.excelforum.com/showthread.php?threadid=379248

0
6/15/2005 6:10:50 AM
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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"coolkid397" <coolkid397.1qnlya_1118815516.4784@excelforum-nospam.com> wrote 
in message news:coolkid397.1qnlya_1118815516.4784@excelforum-nospam.com...
>
> 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?action=getinfo&userid=24319
> View this thread: http://www.excelforum.com/showthread.php?threadid=379248
> 


0
6/15/2005 6:53:45 AM
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 Data>Advanced 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" <coolkid397.1qnlya_1118815516.4784@excelforum-nospam.com> wrote 
in message news:coolkid397.1qnlya_1118815516.4784@excelforum-nospam.com...
>
> 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?action=getinfo&userid=24319
> View this thread: http://www.excelforum.com/showthread.php?threadid=379248
> 


0
biffinpitt (3172)
6/15/2005 6:53:52 AM
Reply:

Similar Artilces:

Changing font case in a selected column
Probably an easy one for you experts but I am very frustrated! I have copied data from one worksheet to another and to tidy it up I want to change the font from sentence case e.g. Hume to upper case e.g. HUME. How do I do it? Many Thanks Paul With Hume in A1, put this in B1 =UPPER(A1) it will display as HUME....... Note that =PROPER(B1) in cell C1 will turn it back to Hume....... Vaya con Dios, Chuck, CABGx3 "PAUL HUME" <paul.c.hume@jobcentreplus.gov.uk> wrote in message news:0a4101c34608$450edaf0$a501280a@phx.gbl... > Probably an easy one for you experts ...

Inserting ID Number from Another Table
Hello, I have two tables. One stores "employee information" and another stores "time sheet information" for all the employees. The "Employees" table has a unique identifier for each employee (EmployeeID). The "Time Sheet" table also has a unique identifier for each time sheet entry (TimeSheetID). When the data entry person inputs time sheet information onto the "TimeSheet" table, she selects the employee from a combo box which combines the first and last name of the employee from the employee table. What I want to do is automatically inse...

Label Columns
I hope I can explain this easily. I am using an excel document with two columns (A and B). How can I print the labels (which are two columned) so that Column A from excel is to the left of column B when printed onto labels. I am trying to make it easy on myself when I go to peel the labels that I can peel the left label and then place the right label with it. It is different information and they cannot be on the same label. Is there a way to do this? Well to start off with printing labels is never easy from Excel, though you would thing nothing could be simpler. But Microsoft d...

in printing a word( 2007) document, the number 2 is missing
Using Microsoft 2007 and printer is HP Laserjet 2200D. On all pages, the number 2 does not print. Does the problem go away if you choose a different font? -- Stefan Blom Microsoft Word MVP "gsw" <gsw@discussions.microsoft.com> wrote in message news:15C05E0B-CB3C-4667-90A6-306B1D9FA711@microsoft.com... > Using Microsoft 2007 and printer is HP Laserjet 2200D. On all pages, the > number 2 does not print. ...

join columns, keep both values
I have a multicolumn database with text entries. I want to join the cells in Col. B with the cells in Col.C and keep all the text. For instance, B3 may be "1N", C3 may be "4W". I want the combined column to read "1N 4W". How do I do this?!? TNX. -- FourPenny ------------------------------------------------------------------------ FourPenny's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23864 View this thread: http://www.excelforum.com/showthread.php?threadid=375062 Try this: =B3&" "&C3 -->"1N 4W&q...

Move a column up or down
Sometimes i need to move a specfic column one line up or down, m quetios is how ro do it? of course i know how to insert o delete column but the column itself one line up o down tks mloo5 -- MLOO5 ----------------------------------------------------------------------- MLOO55's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38564 I assume you mean you want to move a ROW up or down? You can copy the row and insert it back where you want it. "MLOO55" <MLOO55.1rv1uc_1120842307.6...

Finding a string/using adjacent data question
It's a convuluted question that I hope has an easy answer. I'm rather new to Excel formulas, so I've been trying to find stuff about this on the internet but with not much luck. If anyone can help me at all I would greatly appreciate it. I have text strings in column A, A2-A335. I have numerical values that correspond to each text string in columns B-O. What I'm hoping to do is run a search for a given text string in column A and then use the numerical values in D, H, and O for further calculations. I want to be able to type in a name in column Q (for example) and have the f...

getting more columns and data onto one page in Crystal
Hello: Since you can only place so much data onto a "piece of paper", likely the answer is "no". But, even after choosing "Landscape" in Crytal (10.0), there's not a way of getting as many columns of data onto a report as I want--is there? Ideally, I'd like to get 14 columns on there but am running out of room on the right. Also, I have placed a cross-tab on my report that prints on multiple pages instead of just one. Of course, since the cross-tab has 83 rows of data, my guess is that there is nothing that I can do about this either. Is there? ...

Idetnifying repeats in a column
Can anyone help? If I have a column of 10 digit numbers, is there a function which can identify when a number is repeated e.g. A B 1 Number Repeat? 2 1234567890 Yes 3 2345678901 No 4 3456789012 No 5 1234567890 Yes 6 4567890123 No So I need a function entered in column B which searches down the entire column A to look for an identical number and if it finds one, it puts ...

In Excel, COLUMN HEADERS ARE NUMERICAL
I don't know what I've done, but my columns are numerical 1, 2, 3, etc not the typical Column A, B, C. Also, my functions all read something like this: R[-1]C+R[24]C[2] How can I get my spreadsheets back to normal? tools>options>general>r1c1 -- Don Guillett SalesAid Software donaldb@281.com "dstach11" <dstach11@discussions.microsoft.com> wrote in message news:52764F92-BC69-4193-BA78-65850E8EB8E2@microsoft.com... > I don't know what I've done, but my columns are numerical 1, 2, 3, etc not > the typical Column A, B, C. Also, my functions all...

dropdownlist two columns
Hi, I want a dropdown list that refers to two (hidden) columns. Column being an ID-number, column B being the description. Eventually I need the ID-number in the cell with the dropdownlist, bu the user needs the description to decide. Via Data-Validation I can only refer to one column. or not? thanks, spoef -- Message posted from http://www.ExcelForum.com Hi have a look at http://www.contextures.com/excelfiles.html look for Data Validation "Columns" -- Regards Frank Kabel Frankfurt, Germany > Hi, > > I want a dropdown list that refers to two (hidden) columns....

Cannot find mfcs42d.pdb for C++ 6.0 on Windows XP at runtime
I'm getting Find Symbols pop-up window while running C++ 6.0 saying "Please enter the path for mfcs42d.pdb". After I specify the file location at VC98\MFC\Lib (1,092 KB from 7/15/2000), I'm getting a note that the file is found but is not the correct one. I didn't have this problem working on Windows NT. Now I'm trying to run the recompiled version of the same code on Windows XP. Service pack 5 is installed. Please advise what to do. Thanks. ...

Can you sort on the number of messages in thread?
Hi all, When you have your mail grouped on a field, is it then possible to sort on the number of messages in a thread. So threads with only 1 message will be on top en threads with more messages will appear lower. Hope this is possible..... All the best (this might be a solution to my previous question in another yet unanswered thread about filtering messages that are replied to) extra info: the version xp (but 2003 options are ok too) Best, Bert As far as I know, that's not an option "plurker" <nomail@nomail.com> wrote in message news:11ffe8fgqvmbc88@corp.su...

How to Add page numbers to a excel spreadsheet?
-- redhead Page numbers are printed in the header or footer of a worksheet. On the File menu, choose Page Setup. On the Header/Footer tab, use a predefined header or footer from the dropdowns, or design a custom one. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ redhead101863 wrote: ...

App: excel.exe (pid=1612) Exception number: c0000005 (access violation)
Can anyone tell me why or how to correct the error I am consistentl getting (see below) Application exception occurred: App: excel.exe (pid=1612) When: 12/18/2003 @ 08:44:24.656 Exception number: c0000005 (access violation) I have rebooted, removed/reinstalled MSOFFICE 2000 and I am stil getting this error. You can see the error that Dr.Watson reported b viewing the attachment. Please email me at JJOHNSON@SALLIEMAE.COM i you can assist. THANKS Attachment filename: application exception occurred.txt Download attachment: http://www.excelforum.com/attachment.php?postid=38578 -- Messa...

'Define Views...' option not available in Advanced Find screen in Outlook 2000
The 'Define Views...' option in the View menu not available in Advanced Find screen in Outlook 2000. We are looking for a way to set a default view in the advanced find screen or a way to edit the messages view to make it include the To field. We have users that want to see the TO field without having to click View and then the 'Send To' view. I have tried customizing the view but each time I exit it resets the view. I have also modified my 'Messages' view but it was done at the main screen and doesn't seem to phase the one in the advanced find. I also tri...

HR version number not updating after installing SP
Hello, We upgraded Great Plains & Human resources to 8.0 & also installed SP2. After the SP2, The Great Plains version number has properly updated to 8.00g34 but the HR version still says 8.00.7 instead of 8.00.10 on the server & other wrkstations. This was not a problem. The HR modified reports all upgraded fine & all workstations are able to run the modified HR reports except one wrkstn. This one wrkstn. has properly updated the HR version number to 8.00.10. When we try to run an HR report from this wrkstn, it says that the dictionary is not found. The reports need to ...

Find/Found
Can not get Found to respond. Object: Get PersonName in Sheet2, Go to Sheet1, Find PersonName. If Found put PersonName & address into Sheet2 Else put PersonName in Sheet3. Sheet2 has no address. Sheet1 has most, but not all names that are in Sheet2 Tried: Set Found = Nothing Set Found = Cells.Find(PersonName) .. Cells.Find(What:=PersonName..... If Found Nothing Then .. Thanks again for all your help, Gordon Cells.Find("PersonName") -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message ...

Insuficient Key Column Information
Hi, When I try create a new button on my POS touch screen SO Manager (version 2.0) returns an error message "Insuficient Key Column Information" and refuses to allow a new button to be created. Can anyone interpret this message for me and let me know how to fix it so I can create a new button. The new button is a standard button, not a custom one. I just want to put the reprint button on my custom buttons menu. Thanks Dan Levin ...

How do I get the weeks numbered?
I need to create worksheets for the managers at my job and I need to list the work weeks by numbers. Example April 4 - April 10 was the 15th week of the year, I need to know how to get the numbered week on my calender. Tools-> Options-> Calendar Options -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Properly back-up and restore your Outlook data -Creating a Permanent New Mail Desktop Alert in Outlook 2003 ----- "smwhiten" <smwhiten@discussions.microsoft.com> wrote in message news:92478EC4-CB77-4329-B6A8-F2BA1D4034B9@microsoft.com... &g...

How do I set up number eg 2000 to word two thousand in excel?
I need to convert numbers in excel to words Eg 2,200 Two thousand and two hundred dollars Here is the code to create the function: http://support.microsoft.com/?kbid=213360 "Lawrence" <Lawrence@discussions.microsoft.com> wrote in message news:82B59144-57C1-4C42-87B9-42D23D0858E5@microsoft.com... >I need to convert numbers in excel to words > > Eg 2,200 > > Two thousand and two hundred dollars Hi see: http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Regards Frank Kabel Frankfurt, Germany "Lawrence" <Lawrence@discussions.microsoft.com...

How can I find automatic links in an Excel-sheet?
I have an Excel-sheet with "automatic links" to other workbooks. At least according to Excel, so every time when I open the document I have to click 'No' to the questions whether I want to update these. The problem is I can not find this/these automatic link/s. Is there a way to find all automatic links so that I can delete them? Tobias wrote: > I have an Excel-sheet with "automatic links" to other workbooks. At > least according to Excel, so every time when I open the document I > have to click 'No' to the questions whether I want to update th...

Count Consecutive string of same number
Hi, I would like to find a formula that will give me the largest consecutive sting of numbers out of a list. Here is an example of the list (A1:A10000): this answer is 3. A 1 1 blank 1 blank 1 1 1 Thanks! Are the numbers to count *really* 1s? So, you want to find the longest streak of contiguous consecutive 1s? -- Biff Microsoft Excel MVP "andy" <andy@discussions.microsoft.com> wrote in message news:949CCD02-18BB-408F-829B-F812C951D5E2@microsoft.com... > Hi, > I would like to find a formula that will give me the largest consecutiv...

'Find' doesn't find anything
I'm using Outlook with my office's exchange server. I've been a happy user of the 'search folder' feature (mainly the 'for follow-up' search folder), but this suddenly seized to work. After some experimenting I discovered that the underlying problem is the failure of 'Find' to find anything. Any search I perform simply doesn't find anything. I'm using Outlook on a remote desktop infrastructure, just like all my colleagues. I asked a few of them if they had any problems with this, and they apparently hadn't. Since I'm sharing this rem...

Find #3
Hi I think this is a mid find function but can't quite work it out. I have a list of names eg John Smith in a cell I want to list the surnames only. Hope you can help Thanks Shona Hi It is a mid function - you're right! Try: =MID(A1,FIND(" ",A1)+1,99) -- Andy. "Shona" <Student.13@uk.bosch.com> wrote in message news:c5o5ko$c8l$1@ns1.fe.internet.bosch.com... > Hi > > I think this is a mid find function but can't quite work it out. > > I have a list of names eg John Smith in a cell I want to list the surnames > only. > > Ho...