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
- Press with left mouse button on 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.
User defined function category
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
The SUBSTITUTE and REPLACE functions can only handle one string, this article demonstrates two ways to handle more than one […]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Excel categories
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.