Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
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...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
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...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
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...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
Unfortunately I still can not get that to work. my formula is =(SUMPRODUCT((E2:E336<"")/COUNTIF(E2:E336,E2:E336&""))+2&" different 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 same answer. :) -- Quaisne ------------------------------------------------------------------------ Quaisne's Profile: http://www.excelforum.com/member.php...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
In case you're anxious for some help, try this: =ROUND(SUMPRODUCT((E2:E336<"")/COUNTIF(E2:E336,E2:E336&"")),0)+2&" different scorers" -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
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 the formula after different scorers.:( -- Quaisne ------------------------------------------------------------------------ Quaisne's Profile: http://www.excelforum.com/member.php...o&userid=28052 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting how many different names in a long list
Glad you got it working. I know what it's like to ask for help and then wait for what seems like forever while you're trying hard to get something to work. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=501357 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning one text value in a long list? | Excel Worksheet Functions | |||
Excel Spreadsheet from Access. List of names changes as names are | Excel Discussion (Misc queries) | |||
need to copy a long list of file names | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |