## Count unique distinct values in a large dataset [UDF]

### Overview

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)

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)

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Count comma separated values [UDF]

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Substitute multiple text strings [UDF]

The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom […]

List permutations without repetition [UDF]

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form