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
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]
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 [โฆ]
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 [โฆ]
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 [โฆ]
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, [โฆ]
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, [โฆ]
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.
English
Spanish
