Count Unique Entries

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Harlan Grove]

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

Why 5? Col A contains 4 distinct numbers: 3, 2, 4 and 1. Col C contains
two more: 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)))

 

[Guest]

This is just conditional counting, so there's no need for udfs.

Only if I was a bit smarter <g>