counting names only once in a list

P

philmail

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
 
M

Mike

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.
 
P

philmail

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

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
 
M

Mike

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
 
R

Ron Rosenfeld

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


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

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

philmail

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

should work. It will NOT count the blanks, but works if there are blanksin
rng.
--ron
Click to expand...

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

Philippe
 
Top