This blog post describes how to count unique distinct values in list.
We have created excel formulas before to accomplish this task:
- Count unique distinct values in a column in excel
- Count unique distinct values in two columns in excel
- Count unique distinct values in three columns combined in excel
- Count unique distinct text values in a range in excel
The difference from previous posts is today we are working with a large dataset. Formulas in previous posts are too slow.
What is an unique distinct list?
Remove all duplicates from a list and you have created an unique distinct list.
Example - Count unique distinct values in a large dataset
Formula in cell H2:
Excel user defined function
Public Function CountUniqueValues(rng As Variant) As Variant
Dim Test As New Collection
Dim Value As Variant
rng = rng.Value
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then Test.Add Value, CStr(Value)
On Error GoTo 0
CountUniqueValues = Test.Count
How to implement user defined function in excel
1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select a sheet
6. Select a cell range
7. Type =CountUniqueValues(A1:F3000) into formula bar and press ENTER
Download excel example file
(Excel 97-2003 Workbook *.xls)