how to get the number of unique records in a list

L

louisp

hello,

Here is a sample of data that I have:

CODE NAME LASTNAM
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 lik
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 doe
NOT give the desired result..)

thank you!!

louis
 
R

Ron Rosenfeld

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
Click to expand...


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
 
R

Ron Coderre

Using your posted data, try this:

E1: =SUMPRODUCT((A2:A15<>"")/COUNTIF(A2:A15,A2:A15&""))

Does that help?

Regards,
Ron
 
G

GerryK

=SUM(IF(FREQUENCY(MATCH(NAME,NAME,0),MATCH(NAME,NAME,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.
 
A

Aladin Akyurek

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&""))
 
S

Sloth

Ron, that is brilliant. I don't know if my opinion means anything to you,
but I am extremely impressed.
 
L

louisp

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
 
A

Aladin Akyurek

louisp said:
Thank you everyone for the replies.

I have to admit that I do not fully understand what th
sumproduct/countif function does.. Does the sum of specifie
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
Click to expand...

See:

http://www.mrexcel.com/board2/viewtopic.php?t=37550&highlight=token

http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager

http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager

http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grov
 
R

Ron Coderre

Sloth,

RE: =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
 
R

Ron Coderre

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
 
Top