Filter duplicates in a large dataset [UDF]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates.
Example, the image below shows a list containing duplicate values.
User defined function
Function DuplicateValues(rng As Variant, Optional CountDuplicates As Variant) As Variant Dim Test As New Collection Dim Dupes As New Collection Dim Value As Variant Dim Item As Variant Dim temp() As Variant ReDim temp(0) rng = rng.Value If IsMissing(CountDuplicates) Then CountDuplicates = False On Error Resume Next For Each Value In rng If Len(Value) > 0 Then Test.Add Value, CStr(Value) If Err Then If Len(Value) > 0 Then Dupes.Add Value, CStr(Value) Err = False End If Next Value On Error GoTo 0 If CountDuplicates = False Then For Each Item In Dupes temp(UBound(temp)) = Item ReDim Preserve temp(UBound(temp) + 1) Next Item DuplicateValues = Application.Transpose(temp) Else DuplicateValues = Dupes.Count End If End Function
How to add the user defined function to your workbook
- Press Alt-F11 to open the Visual Basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
How to extract duplicate values
- Select a cell range
- Type =DuplicateValues($A$1:$F$3000, FALSE)
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys
How to count duplicate values
- Select a cell
- Type =DuplicateValues($A$1:$F$3000, TRUE) 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]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]
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 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 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 […]
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 […]
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.