Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Entries
I am trying to count unique entries across a set of colums. For example,
A C 3 4 2 4 4 7 2 1 1 0 I want the result to be 5 as the number of unique entries. The entries are not in consecutives column. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Entries
You want the count of unique items for each column?
=SUM(IF(A1:A6<"",1/COUNTIF(A1:A6,A1:A6))) confirmed with Control+Shift+Enter. Copy across to other columns. "SouthCarolina" wrote: I am trying to count unique entries across a set of colums. For example, A C 3 4 2 4 4 7 2 1 1 0 I want the result to be 5 as the number of unique entries. The entries are not in consecutives column. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Entries
Upon rereading, I am thinking you want the number of unique entries in all
columns combined. You could try a UDF. Paste into an excel module and call it like =Unique(A1:A5, C1:C5) Function Unique(ParamArray Rng() As Variant) Dim i As Long Dim t As Long Dim x As Range Dim Temp As Collection On Error Resume Next Set Temp = New Collection For i = 0 To UBound(Rng()) For t = 1 To Rng(i).Areas.Count For Each x In Rng(i).Areas(t).Cells Temp.Add Trim(x.Value), CStr(Trim(x.Value)) Next x Next t Next i Unique = Temp.Count End Function "SouthCarolina" wrote: I am trying to count unique entries across a set of colums. For example, A C 3 4 2 4 4 7 2 1 1 0 I want the result to be 5 as the number of unique entries. The entries are not in consecutives column. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Entries
JMB wrote...
Upon rereading, I am thinking you want the number of unique entries in all columns combined. You could try a UDF. Paste into an excel module and call it like .... "SouthCarolina" wrote: I am trying to count unique entries across a set of colums. For example, A C 3 4 2 4 4 7 2 1 1 0 I want the result to be 5 as the number of unique entries. The entries are not in consecutives column. Why 5? Col A contains 4 distinct numbers: 3, 2, 4 and 1. Col C contains two mo 7 and 0. Should the zero not be included? If it should be included, there are 6 distinct values. This is just conditional counting, so there's no need for udfs. =COUNT(1/FREQUENCY((A1:A5,C1:C5),(A1:A5,C1:C5))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Entries
This is just conditional counting, so there's no need for udfs.
Only if I was a bit smarter <g "Harlan Grove" wrote: JMB wrote... Upon rereading, I am thinking you want the number of unique entries in all columns combined. You could try a UDF. Paste into an excel module and call it like .... "SouthCarolina" wrote: I am trying to count unique entries across a set of colums. For example, A C 3 4 2 4 4 7 2 1 1 0 I want the result to be 5 as the number of unique entries. The entries are not in consecutives column. Why 5? Col A contains 4 distinct numbers: 3, 2, 4 and 1. Col C contains two mo 7 and 0. Should the zero not be included? If it should be included, there are 6 distinct values. This is just conditional counting, so there's no need for udfs. =COUNT(1/FREQUENCY((A1:A5,C1:C5),(A1:A5,C1:C5))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count unique items in a pivottable data field. | Excel Discussion (Misc queries) | |||
count and display unique values | Excel Worksheet Functions | |||
counting unique entries in a list | Excel Discussion (Misc queries) | |||
how to count unique entries with multiple condition | Excel Worksheet Functions | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |