Counting how many different names in a long list

I have a list of 332 names many of them are duplicated. I am looking for
a formula or function that can count how many unique names there are in
the list. 

Can anyone help?


-- 
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052
View this thread: http://www.excelforum.com/showthread.php?threadid=501357

0
1/14/2006 5:13:41 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
377 Views

Similar Articles

[PageSpeed] 28

This'll count the number of distinct values in a range:

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

(adjust the range to match, but don't use the whole column)

Quaisne wrote:
> 
> I have a list of 332 names many of them are duplicated. I am looking for
> a formula or function that can count how many unique names there are in
> the list.
> 
> Can anyone help?
> 
> --
> Quaisne
> ------------------------------------------------------------------------
> Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052
> View this thread: http://www.excelforum.com/showthread.php?threadid=501357

-- 

Dave Peterson
0
petersod (12005)
1/14/2006 5:16:23 PM
That works fine. 

When I put it in a cell of its' own it gives the correct answer of 56.
I then wanted to add 2 to it as I know there are 2 lots of duplicate
names that are different people. That worked fine as well with just +2
at the end of the formula.

But when I put it in a concatenate function for some reason it gave the
answer as 58.0000000000001 and it refuses to be formatted, even if I put
it into a seperate hidden cell and format that cell. It appears as 58 on
a cell on its own but as the other value in any text string including
just using &

Still I do not need it in a concatenate function, it just appeared
neater that way. I was just curious though but thanks anyway as that
does solve my problem.


-- 
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052
View this thread: http://www.excelforum.com/showthread.php?threadid=501357

0
1/14/2006 6:15:45 PM
You can use =round() or =text() in your formula that concatenates:

="this is some text: " & text(a1,"#,##0")
or
="this is some text: " & round(a1,0)




Quaisne wrote:
> 
> That works fine.
> 
> When I put it in a cell of its' own it gives the correct answer of 56.
> I then wanted to add 2 to it as I know there are 2 lots of duplicate
> names that are different people. That worked fine as well with just +2
> at the end of the formula.
> 
> But when I put it in a concatenate function for some reason it gave the
> answer as 58.0000000000001 and it refuses to be formatted, even if I put
> it into a seperate hidden cell and format that cell. It appears as 58 on
> a cell on its own but as the other value in any text string including
> just using &
> 
> Still I do not need it in a concatenate function, it just appeared
> neater that way. I was just curious though but thanks anyway as that
> does solve my problem.
> 
> --
> Quaisne
> ------------------------------------------------------------------------
> Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28052
> View this thread: http://www.excelforum.com/showthread.php?threadid=501357

-- 

Dave Peterson
0
petersod (12005)
1/14/2006 6:48:24 PM
Unfortunately I still can not get that to work.:confused: 

my formula is

=(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&""))+2&" differen
scorers")

How do I incorporate 

="this is some text: " & text(a1,"#,##0")
or
="this is some text: " & round(a1,0)

into that to make it work. I either get a value error or the sam
answer. :

--
Quaisn
-----------------------------------------------------------------------
Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2805
View this thread: http://www.excelforum.com/showthread.php?threadid=50135

0
1/15/2006 6:20:58 PM
In case you're anxious for some help, try this:

=ROUND(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&"")),0)+2&
different scorers

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=50135

0
1/15/2006 8:02:20 PM
Thanks that works fine now.:) 

I understood what Dave meant about a1.

What I was doing wrong was putting the ,0 at the very end of th
formula after different scorers.:

--
Quaisn
-----------------------------------------------------------------------
Quaisne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2805
View this thread: http://www.excelforum.com/showthread.php?threadid=50135

0
1/15/2006 8:25:07 PM
Glad you got it working.  I know what it's like to ask for help and the
wait for what seems like forever while you're trying hard to ge
something to work

--
Cutte
-----------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984
View this thread: http://www.excelforum.com/showthread.php?threadid=50135

0
1/15/2006 8:29:07 PM
Reply:

Similar Artilces:

Linking many cells
I wish to link many cells in (e.g. A1:40) in sheet 1 to A1:A40 in sheet 2 without doing it one by one so that whaever I type in A1 sheet 1 appears in A1 sheet 2. Please any idea? dk As long as your 'from' and 'to' blocks are structured identically (eg 1 column of forty rows in your example), it's straightforward. Click in the first 'to' cell, Sheet1!A1. Type the '=' sign, then click in the first 'from' cell, Sheet2!A1, then hit enter. Your formula in Sheet!A1 should read =Sheet2!A1 (look for it in the formula bar). Grab the fill handle (th...

Naming Ranges #4
Hi, When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2, then access the Define Names dialogbox, I can see the named Range listed twice. One is noted as belonging specifically to Sheet2 and the other appears without a sheet reference. When assiging a name to a Range, can I specify that it belongs to a certain sheet? Also, how would I change this code to assign the sheet name aswell? ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1" I am trying to assign a the name 'Column_Header_Range' to each sheet in my work...

suggest names (outlook 2002)
in the autocomplete function, is there a way to access the database? I've got old addresses in there that I used once a year ago causing a drop-down list when I've only got one matching entry in my address book... so what I'd like to do is either clear it out completely or at least delete the obsolete entries, I've looked in the registry and it isn't there... any suggestions would be appreciated, thanks! --John Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key to migrate to...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

Importing five row list from word 2003 to excel
I have a Word five row list I would like to import into Excel in a five column list. Every hundred items or so their is a new heading. What do I do? If it is in a word table you can cut and paste it. But you will have to reformat it. Once you get it into excel you can sort it and delete the duplicate headers. --- Message posted from http://www.ExcelForum.com/ ...

Contacts are not listed in address book
When I open my address book, there are only two contacts. When I open my contact list it lists all my contacts. What do I need to do to get my full contact list into my address book? Depends a bit on what you have done so far and which version you have. You have given us very little information. Try this: right click your Contacts folder | Address Book | set as an Outlook addess book (tick the box). If it doesn't work, tell us version and how you got your Contacts into Outlook (import etc). -- Regards Judy Gleeson MVP Outlook "Docmoney" <Do...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

List of files
All I want to do is get a list of files in a directory so they can be deleted. How do I get this list ? John, > All I want to do is get a list of files in a directory so > they can be deleted. How do I get this list ? You can use FindFirstFile/ FindNextFile/FindClose API functions for enumerating files. If you want to remove directory with its content take a look at SHFileOperation in MSDN. -- Regards, Kobi Ben Tzvi "John" <john.f.brownell@wdc.com> wrote in message news:022b01c3788a$bb119ee0$a301280a@phx.gbl... > All I want to do is get a list of files in...

Validation
I would like to have combo box functionality for the data validation feature in Excel 2000. This doesn't seem to be available in the native validation setup dialog box. Am I missing something? I would like to display the validated items list in the leftmost column and have column(s) of description display to the right of each list item (so I can tell which list item I should select). Suggestions? Many Thanks, Bill Vallance Bill Debra Dalgleish has instructions for creating dependent DV lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Tue, 08 Jun 20...

How do I print a list of my E-mail folders?
I need to print a list of my e-mail folders. "AHD23" <AHD23@discussions.microsoft.com> wrote in message news:5BFD12CA-5D55-4376-AF9C-58703042577D@microsoft.com... >I need to print a list of my e-mail folders. I don't believe there's a way to do that, except as a screen shot. -- Brian Tillman [MVP-Outlook] This might be a workaround for you: http://www.vboffice.net/sample.html?mnu=2&smp=12&cmd=showitem&lang=en In example #1, replace the line LoopItems Folder.Items by Debug.Print Folder.Name After running the code,...

Text to Columns from drop down list update
Hi I need to perform a Text to Column conversion from a drop down list, but I also need the extracted value to be updated if the value in the list is changed. eg: Drop down list has 2 values: 1. 4x16K 2. 8x8K If the user selects 1 I can easily extract out to another cell the value 1 using Text to columns, however if the user then changes the choice to 2 the text to columns extraction is not updated to 2. Is there a way to update changes in the original cell using text to columns? Or is there another way I can assign a value to a drop down menu choice in a different cell while havi...

Delivery Status Notification (DSN) different if sent from outside
When my users mailboxes are full above quota and not accepting any more mail, the sender receives a differently worded DSN depending on whether they sent email to that user from an internal account (one that is part of the exchange org) or an external account (not part of the Exchange organization). The one sent to the a user that is external is much less human friendly and is causing me a problem with some scirpted automation in my app. Is there a way to make the text the same for both? Examples pasted below: Here is the if sender is internal: Your message did not reach some or all o...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

Constant List
Is there an easy way to return the values of the ChartType constants? Excel 97 does not seem to list them in help and I have not been able to find them in google.group searches. Thanks M Select xlChartTypes in the Classes pane of the object browser. The constants are named in the members panel. If you select one, it provides the numeric equivalent at the bottom of the object browser, e.g., Const xlArea = 1 Member of Excel.XlChartType - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ M wrote: > Is there...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

'Recently used file' list.
When I open Excel nine filenames appear on the RHS as 'recently used files' (this number was set via Tools/Options/General, of course). In the middle of this list are 3 no-longer-used files. Though the files themselves have been deleted their names remain in this list. Is there an easy way to remove those entries from that list, please? TIA, DB. There is an entry in the help file "Display or hide recently used workbooks on the File menu" which says uncheck the 'recently used files' checkbox, the click OK. Then, basically, start again. "DB.&...

Favorite Folders reverts to default list when computer shut-down
I have no problem adding, deleting or moving folders in the Favorite Folders pane. The problem is that they all disappear and revert to the default list everytime my computer is shut down. So I have to keep adding the folders back in Is there a way to keep the shortcut folders in the Favorite Folders pane? ...

Long File Path
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>When I try to save my Excel worksheet files somewhere on the HD, Excel comes back with an error message saying &quot;The file path you entered is too long&quot;. I do not want Excel to tell where to save my files! Anybody can help? <br><br>Thanks! <br> Ali Excel is most likely telling you nothing of the sort -- OS X is. Make sure you have Office & OS X fully updated then run Disk Utility - Repair Disk Permissions. If you continue to have a problem you...

Address list filter rules
I am trying to create a new address list in Exchange 2003. What I want is all employees and all employee distribution groups. After trying several combinations I added a custom attribute to each employee and to each employee distruction group and entered "Employee" as the value. I then created a new address list with the filter rule which said if the custom attribute 1 = Employee include in the list. Everything is fine except that Query based distribution groups do not show up. So I create a new address list and found that using any filter rule rejects all query based distributi...

how do I find a list of databases with correpsponding company name
Hello, I need to know how in GP to pull up a list of our companies with their corresponding SQL database names. I've done this a long time ago and remember it being easy but cannot remember where to do this. SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 "Tim Quiggle" <Tim Quiggle@discussions.microsoft.com> wrote in message news:D0ECF5C5-74F9-494E-8C2E-B37EE12C8D0C@microsoft.com... > Hello, > > I need to know how in GP to pull up a list of our companies with their > corresponding SQL database names. I've done this a long time ago and > rem...

CC automatically from distribution list
I am trying to set up a distribution list and trying to automatically cc people from this list (but now all). Is there someway to do this or a macro that can allow this to happen. ...

Long delay in opening New Message windows
My Windows mail has recently developed the problem of taking 10 seconds or more just to open a create new message window. How do I fix this? Did you add any 3rd party software into the Add Ins -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Jembeaux" <Jembeaux@discussions.microsoft.com> wrote in message news:A58C3591-9431-4BDF-8D32-9ACE1E12D41E@microsoft.com... > My Windows mail has recently developed the problem of taking 10 seconds or > more just to open a create ne...

different versions in visio ole-object?
hello i have some problems printing a word document which includes some visio drawingns. In word i can see the visio object like i edited it but when printing the word-document, a completly old version of the visio object is printed out. The printed object is older that 2 weeks. I tryed printing the document from other PC without changes. I tryed copying the ole-object into an other word dokument (copy/paste) without changes. Printing the document from wordpad works great .... I also opened the visio object (doublecklick in word) and edited the object, saved the file and exited back to wo...