Function for counting different values in a range of columns
I am trying to create a function in VBA that when given a range of values will return the Count Distinct of those values. For example:
| Column A |
|----------|
| 1 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |
Number of Rows = 11 Significant Values = 6
Here is the structure of the VBA code I am trying to use to build a function that I can call in Excel:
Function CountDistinct(dataRange As Range)
Dim x As Double
x = 0
For i = 1 To dataRange.Rows.Count
x = x + (1 / (CountIf(dataRange, dataRange(i))))
Next i
End Function
I am completely new to VBA programming, so I apologize for all the obvious, egregious mistakes made in the code above, if it could even be called that.
I know there are other ways to find the correct answer, but I'm curious about how to create custom Excel functions.
Also, the pseudo logic of my approach looks like this:
- Give the CountDistinct function a dataRange cell range
- Loop through the range
- For each cell in the range, COUNTIF that value in the range (so in the example above, rows 3-6 would return 4, since 3 appears 4 times in the range).
- For each cell in the range, add 1 / (the result of step 3) to the result variable x
| Values | CountIF(Value) | 1/CountIF(Value) |
|--------|----------------|-----------------------------|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 4 | 2 | 0.5 |
| 4 | 2 | 0.5 |
| 5 | 2 | 0.5 |
| 5 | 2 | 0.5 |
| 6 | 1 | 1 |
| | | SUM of 1/CountIF(Value) = 6 |
This will return the Count of Distinct in column A == 6.
source to share
First steps:
Add Option Explicit
to the header of all your modules. It will record the difference between OneVariable
and OneVarlable
.
Make the variables meaningful - did you know that x and I were next time you look at this code?
Your parameters for counting
- user is listing
- save the values and read only those that do not correspond to the previous values.
Using the worksheet function,
Option Explicit
Function CountUnique(dataRange As Range) As Long
Dim CheckCell
Dim Counter As Double
Counter = 0
For Each CheckCell In dataRange.Cells
Counter = Counter + (1 / (WorksheetFunction.CountIf(dataRange, CheckCell.Value)))
Next
' Finally, set your function name equal to the Counter,
' so it knows what to return to Excel
CountUnique = Counter
End Function
Using tracking
...
' check out scripting dictionaries
' much more advanced - Keep it simple for now
...
source to share
Late, but I thought I'd add another VBA variant that doesn't require adding a link.
Also, this is about a neat excel VBA function that I would have liked to know much earlier.
My solution for this is using a Collection object to find different values.
Option Explicit
'^ As SeanC said, adding Option Explicit is a great way to prevent writing errors when starting out.
Public Function CountDistinct(r As Range) As Long
'' DIM = declare in memory
Dim col As Collection
Dim arr As Variant
Dim x As Long
Dim y As Long
Set col = New Collection
'' setting a Variant = Range will fill the Variant with a 2 dimensional array of the values of the range!
arr = r
'' skip the errors that are raised
On Error Resume Next
'' loop over all of the elements.
'' UBound is a built in VBA Function that gives you the largest value of an array.
For x = 1 To UBound(arr, 1)
For y = 1 To UBound(arr, 2)
'' try to add the value in arr to the collection
col.Add 0, CStr(arr(x, y))
'' every time the collection runs into a value it has already added,
'' it will raise an error.
'uncomment the below to see why we are turning off errors
'Debug.Print Err.Number, Err.Description
Next
Next
'' turn errors back on.
On Error GoTo 0
''set the function name to the value you want the formula to return
CountDistinct = col.Count
'' The next parts should be handled by VBA automatically but it is good practise to explicitly clean up.
Set col = Nothing
Set arr = Nothing
Set r = Nothing
End Function
I hope this helps someone in turn.
source to share
Sub CountDistinct()
Dim RunSub As Long
Dim LastRow As Long
Dim CurRow As Long
Dim Unique As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Unique = 1
For CurRow = 2 To LastRow
If Range("A2:A" & CurRow - 1).Find(Range("A" & CurRow, LookIn:=xlValues)) Is Nothing Then
Unique = Unique + 1
Else
End If
Next CurRow
MsgBox Unique & " Unique Values"
End Sub
source to share
There are (of course) other ways that this can be done using VBA.
Public Function CountDistinct(rng As Range) As Long
Dim i As Long
Dim Cnt As Double
Cnt = 0
For i = 1 To rng.Rows.Count
Cnt = Cnt + 1 / WorksheetFunction.CountIf(rng, rng(i, 1))
Next i
CountDistinct = CLng(Cnt)
End Function
source to share
I'll call you back here too ...
Public Function Count_Distinct_In_Column(Rng As Range)
Count_Distinct_In_Column = _
Evaluate("Sum(N(countif(offset(" & Rng.Cells(1).Address _
& ",,,row(" & Rng.Address & "))," & Rng.Address & ")=1))")
End Function
Called as:
? Count_Distinct_In_Column(Range("A2:A12"))
6
source to share
This method applies the following logic.
- Put the elements of the range into an array
- Place an array in a dictionary for unique items only
- Count the elements (keys) in the dictionary for unique elements
Under Tools → References, Link "Microsoft Scripting Runtime"
Option Explicit
Dim lngCounter As Long
Dim dataRange As Range
Dim dictTemp As Dictionary
Dim varTemp As Variant
Sub Test()
Set dataRange = Range(Cells(2, 1), Cells(12, 1))
MsgBox CountDistinct(dataRange), vbInformation + vbSystemModal, "Count Distinct"
End Sub
Public Function CountDistinct(dataRange As Range) As Long
'Populate range into array
If dataRange.Rows.Count < 2 Then
ReDim varTemp(1 To 1, 1 To 1)
varTemp(1, 1) = dataRange
Else
varTemp = dataRange
End If
'Dictionaries can be used to store unique keys into memory
Set dictTemp = New Dictionary
'Add array items into dictionary if they do not exist
For lngCounter = LBound(varTemp) To UBound(varTemp)
If dictTemp.Exists(varTemp(lngCounter, 1)) = False Then
dictTemp.Add Key:=varTemp(lngCounter, 1), Item:=1
End If
Next lngCounter
'Count of unique items in dictionary
CountDistinct = dictTemp.Count
End Function
source to share