Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Names in a List | Excel Discussion (Misc queries) | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
Counting how many different names in a long list | Excel Discussion (Misc queries) | |||
need to find a max count without knowing what I'm counting (list of names) | Excel Worksheet Functions | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |