Counting Unique Values

[LaDdIe]

Hiya,

Cells D114 contain values Numerical & Text, and may appear more than once
in this range, I would like a way to count cells with values (ignoring blank
cells) and ignoring duplicate values, the result to be displayed in D15, VBA
or Formula.

I've attemped to search around the discussion pages for somthing to modify
w/o luck.

Thanks

Laddie.

 

[Chip Pearson]

Try the following in D15:

=SUM(IF(FREQUENCY(IF(LEN(D114)>0,MATCH(D114,D114,0),""),
IF(LEN(D114)>0,MATCH(D114,D114,0),""))>0,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dave D-C]

I like to see a formula solution.
in VBA, a feature of a collection is that it will
not allow duplicate keys:

Option Explicit

Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 1 To 14
sCell = Cells(iRowV, 4).Value
If sCell <> "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(15, 4) = iCount
End Sub

 

[rleavitt]

Hiya,

Cells D114 contain values Numerical & Text, and may appear more than once
in this range, I would like a way to count cells with values (ignoring blank
cells) and ignoring duplicate values, the result to be displayed in D15, VBA
or Formula.

The following user defined function is a simple approach. You would
enter this function in a VBA module and then enter

=CountValues(D114) in cell D15.

I note that this is not elegant and will be a little slow if you have
a big range with many values. I have another approach for large ranges
of values using an ordered array and a binary search, but I thought
this would be less confusing. Also, this will treat text that can be
resolved into a value as the value itself. For example if you enter
'1' into a cell, this will count as 1.

Function CountValues(TheRange As Range) As Integer
Dim SaveValues(99) As Variant
Dim Index As Integer
Dim MaxIndex As Integer
Dim FoundValue As Boolean
Index = 0
MaxIndex = 0
Dim cell As Range
For Each cell In TheRange
If IsNumeric(cell) Then
TheValue = Val(cell.Value)
If MaxIndex = 0 Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
Else
FoundValue = False
Index = 0
While Not FoundValue And Index <= MaxIndex
FoundValue = (SaveValues(Index) = TheValue)
If Not FoundValue Then Index = Index + 1
Wend
If Not FoundValue Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
End If
End If
End If
Next
CountValues = MaxIndex
End Function

 

[LaDdIe]

Thanks Chip,

The results is an error (a value used in the formula is of the wrong data
type).
Would you be able to help resolve this?

Thanks.

Laddie

 

[LaDdIe]

Thanks, It works.

The following user defined function is a simple approach. You would
enter this function in a VBA module and then enter

=CountValues(D114) in cell D15.

I note that this is not elegant and will be a little slow if you have
a big range with many values. I have another approach for large ranges
of values using an ordered array and a binary search, but I thought
this would be less confusing. Also, this will treat text that can be
resolved into a value as the value itself. For example if you enter
'1' into a cell, this will count as 1.

Function CountValues(TheRange As Range) As Integer
Dim SaveValues(99) As Variant
Dim Index As Integer
Dim MaxIndex As Integer
Dim FoundValue As Boolean
Index = 0
MaxIndex = 0
Dim cell As Range
For Each cell In TheRange
If IsNumeric(cell) Then
TheValue = Val(cell.Value)
If MaxIndex = 0 Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
Else
FoundValue = False
Index = 0
While Not FoundValue And Index <= MaxIndex
FoundValue = (SaveValues(Index) = TheValue)
If Not FoundValue Then Index = Index + 1
Wend
If Not FoundValue Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
End If
End If
End If
Next
CountValues = MaxIndex
End Function

 

[Chip Pearson]

I forgot to mention that this is an Array Formula. Therefore, you must press
CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will display the
formula in the formula bar enclosed in curly braces { }.

For more about array formulas, see
http://www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dave D-C]

Chip,
Very pretty.
Please grade:
=SUM(IF(IF(D114>="a",MATCH(D114,D114,0),0)=ROW(D114)-0,1,0))
(The "-0" near the end has to be -(the 1st row - 1);
for D1 it is -0)

Try the following in D15:
=SUM(IF(FREQUENCY(IF(LEN(D114)>0,MATCH(D114,D114,0),""),
IF(LEN(D114)>0,MATCH(D114,D114,0),""))>0,1))

Dave said:
I [would] like to see a formula solution.

 

[Dave D-C]

Dave should've said [LEN()>0 instead of ..>="a"]

Chip,
Very pretty.
Please grade:
=SUM(IF(IF(LEN(D114)>0,MATCH(D114,D114,0),0)=ROW(D114)-0,1,0))
(The "-0" near the end has to be -(the 1st row - 1);
for D1 it is -0)

Try the following in D15:
=SUM(IF(FREQUENCY(IF(LEN(D114)>0,MATCH(D114,D114,0),""),
IF(LEN(D114)>0,MATCH(D114,D114,0),""))>0,1))

Dave said:
I [would] like to see a formula solution.

 

[LaDdIe]

Thanks very much for your help, got it to work!

I forgot to mention that this is an Array Formula. Therefore, you must press
CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will display the
formula in the formula bar enclosed in curly braces { }.

For more about array formulas, see
http://www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)