Overview

This blog post describes how to count unique distinct values in list.

We have created excel formulas before to accomplish this task:

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:

=CountUniqueValues(A1:F3000) + ENTER

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)

Next Value
On Error GoTo 0

CountUniqueValues = Test.Count

End Function

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

count unique distinct dates large data set.xls (675 KB)

(Excel 97-2003 Workbook *.xls)