| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to get the number of unique records in a list
hello, Here is a sample of data that I have: CODE NAME LASTNAME TS19 Sylvain Brook TS19 Sylvain Brook TV04 Valérie Musoni TV04 Valérie Musoni VB05 Ben Valiquette VB05 Ben Valiquette VB05 Ben Valiquette VI02 Isabelle Vanasse VR07 Richard Vivo VR07 Richard Vivo VR07 Richard Vivo WD01 Dany Williams WD01 Dany Williams WD01 Dany Williams How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee) In this case, the desired result would be 6. (the count function does NOT give the desired result..) thank you!! louisp -- louisp ------------------------------------------------------------------------ louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
On Mon, 13 Feb 2006 15:08:13 -0600, louisp
wrote: hello, Here is a sample of data that I have: CODE NAME LASTNAME TS19 Sylvain Brook TS19 Sylvain Brook TV04 Valérie Musoni TV04 Valérie Musoni VB05 Ben Valiquette VB05 Ben Valiquette VB05 Ben Valiquette VI02 Isabelle Vanasse VR07 Richard Vivo VR07 Richard Vivo VR07 Richard Vivo WD01 Dany Williams WD01 Dany Williams WD01 Dany Williams How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee) In this case, the desired result would be 6. (the count function does NOT give the desired result..) thank you!! louisp Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/forums Then use the formula: =COUNTDIFF(CODE) where CODE represents the range containing the Employee key --ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Using your posted data, try this: E1: =SUMPRODUCT((A2:A15<"")/COUNTIF(A2:A15,A2:A15&"")) Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Yes!!! very much so!!! thank you!! louis -- louisp ------------------------------------------------------------------------ louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Ron, that is brilliant. I don't know if my opinion means anything to you,
but I am extremely impressed. "Ron Coderre" wrote: Using your posted data, try this: E1: =SUMPRODUCT((A2:A15<"")/COUNTIF(A2:A15,A2:A15&"")) Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
=SUM(IF(FREQUENCY(MATCH(NAME,NAME,0),MATCH(NAME,NA ME,0))0,1))
this could be used on each column or you could use a helper column where the NAME and LASTNAME are added together. "louisp" wrote: hello, Here is a sample of data that I have: CODE NAME LASTNAME TS19 Sylvain Brook TS19 Sylvain Brook TV04 Valérie Musoni TV04 Valérie Musoni VB05 Ben Valiquette VB05 Ben Valiquette VB05 Ben Valiquette VI02 Isabelle Vanasse VR07 Richard Vivo VR07 Richard Vivo VR07 Richard Vivo WD01 Dany Williams WD01 Dany Williams WD01 Dany Williams How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee) In this case, the desired result would be 6. (the count function does NOT give the desired result..) thank you!! louisp -- louisp ------------------------------------------------------------------------ louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
If you download and install the morefunc.xll add-in...
=COUNTDIFF(CodeRange,,"") Otherwise... Either: =SUM(IF(CodeRange<"",1/COUNTIF(CodeRange,CodeRange))) which needs to be confirmed with control+shift+enter. Or: =SUMPRODUCT((CodeRange<"")/(COUNTIF(CodeRange,CodeRange&"")) louisp wrote: hello, Here is a sample of data that I have: CODE NAME LASTNAME TS19 Sylvain Brook TS19 Sylvain Brook TV04 Valérie Musoni TV04 Valérie Musoni VB05 Ben Valiquette VB05 Ben Valiquette VB05 Ben Valiquette VI02 Isabelle Vanasse VR07 Richard Vivo VR07 Richard Vivo VR07 Richard Vivo WD01 Dany Williams WD01 Dany Williams WD01 Dany Williams How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee) In this case, the desired result would be 6. (the count function does NOT give the desired result..) thank you!! louisp |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Thank you everyone for the replies. I have to admit that I do not fully understand what the sumproduct/countif function does.. Does the sum of specified products, then divides by the amount of non-empty products? I like the countdiff function.... will have to try it out. Hooray for simplicity! thanks again... louis -- louisp ------------------------------------------------------------------------ louisp's Profile: http://www.excelforum.com/member.php...o&userid=25880 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
louisp Wrote: Thank you everyone for the replies. I have to admit that I do not fully understand what the sumproduct/countif function does.. Does the sum of specified products, then divides by the amount of non-empty products? I like the countdiff function.... will have to try it out. Hooray for simplicity! thanks again... louis See: http://www.mrexcel.com/board2/viewto...ighlight=token http://www.mrexcel.com/board2/viewto...ighlight=hager http://www.mrexcel.com/board2/viewto...ighlight=hager http://www.mrexcel.com/board2/viewto...ighlight=grove -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Sloth, =SUMPRODUCT((A2:A15<"")/COUNTIF(A2:A15,A2:A15&"")) I agree that the formula is brilliant, but I can't take credit for inventing it. I'm sure I saw it some time ago in the MS Excel newsgroups and copied it into my trove of "must have" solutions. I hope whoever DID invent it sees your compliment and this reply and takes some satisfaction in knowing that they did the Excel-using population some good. Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to get the number of unique records in a list
Hi, Aladin
I just read the links you posted. Thanks for giving us the count-unique formula's pedigree. And kudos to you and Harlan for coming up with that solution. *********** Best Regards, Ron XL2002, WinXP-Pro "Aladin Akyurek" wrote: louisp Wrote: Thank you everyone for the replies. I have to admit that I do not fully understand what the sumproduct/countif function does.. Does the sum of specified products, then divides by the amount of non-empty products? I like the countdiff function.... will have to try it out. Hooray for simplicity! thanks again... louis See: http://www.mrexcel.com/board2/viewto...ighlight=token http://www.mrexcel.com/board2/viewto...ighlight=hager http://www.mrexcel.com/board2/viewto...ighlight=hager http://www.mrexcel.com/board2/viewto...ighlight=grove -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=511929 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select every 10th number in a list | Excel Discussion (Misc queries) | |||
| unique occurences in list | Excel Worksheet Functions | |||
| unique occurences in list | Excel Worksheet Functions | |||
| Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
| Formula to list unique values | Excel Worksheet Functions | |||
Hybrid Mode
