counting names only once in a list
 

Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland

counting names only once in a list
 

As long as there no blanks in the range use

=SUM(1/COUNTIF(A1:A5,A1:A5))

This is an array formula so enter with ctrl+ Shift + enter.

If there are blanks then post again for the answer to that.

"philmail" wrote:

Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland

counting names only once in a list
 

On 19 fév, 13:40, Mike wrote:
As long as there no blanks in the range use

=SUM(1/COUNTIF(A1:A5,A1:A5))

This is an array formula so enter with ctrl+ Shift + enter.

If there are blanks then post again for the answer to that.





"philmail" wrote:
Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland

Actually, there are many blanks !
Let's say there are 8 * 20 positions (displayed in 2 columns). These
160 positions aren't all taken so names can be added.

What do you think ?

Philippe

counting names only once in a list
 

A UDF for 3 columns would be below

call with =uniqueitems(a1:c10)

Function Uniqueitems(ArrayIn, Optional Count As Variant) As Variant
Dim Unique() As Variant
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean
If IsMissing(Count) Then Count = True
NumUnique = 0
For Each Element In ArrayIn
FoundMatch = False
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem
End If
Next i
AddItem:
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If
Next Element
If Count Then Uniqueitems = NumUnique Else Uniqueitems = Unique
End Function

"philmail" wrote:

Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland

counting names only once in a list
 

On 19 Feb 2007 04:25:12 -0800, "philmail" wrote:

Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland


=SUMPRODUCT((rng<"")/COUNTIF(rng,rng&""))

should work. It will NOT count the blanks, but works if there are blanks in
rng.
--ron

counting names only once in a list
 

On 19 fév, 14:49, Ron Rosenfeld wrote:
On 19 Feb 2007 04:25:12 -0800, "philmail" wrote:



Good afternoon,

This is my problem :

Let's say I've got a list of names (actually a list displayed on a few
columns). There are doubles or triples in this list.
What is the function giving me the total of unique names ? So if
Dupond is detected once, it counts one. When Dupond is detected
another time, it doesn't count.

Thanks a lot for the previous help ! It reduced the problems a lot !

Have a good day !

Philippe
Switzerland

=SUMPRODUCT((rng<"")/COUNTIF(rng,rng&""))

should work. It will NOT count the blanks, but works if there are blanks in
rng.
--ron

OK, Thanks a lot, I try right now...

Philippe