Count unique distinct values in a large dataset [UDF]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one distinct value and you have created a unique distinct list.
Formulas are sometimes too slow if you work with a lot of data, this article demonstrates a user defined function you can use, however, I highly recommend using a pivot table for this task:
Count unique distinct values using a pivot table
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 add the user defined function to your workbook
1. Press Alt-F11 to open the 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
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
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 unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
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
Paste image link to your comment.