## Filter duplicates in a large dataset [UDF]

### Overview

This blog post shows you how to filter duplicate values in a large list. We are going to create and implement an user defined function (udf). This udf also counts duplicates.

Example,

Filtering duplicate values from a big range using worksheet functions is way too slow.

I have in a previous posts described how to:

- Excel udf: Remove duplicates from a large dataset
- Excel udf: Count unique distinct values in a large dataset

### 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

### Download excel sample file for this tutorial.

Duplicate-dates-large-data-set (870 KB)

(Excel 97-2003 Workbook *.xls)

### How to implement user defined function in excel

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
- Select a sheet
- Select a cell range
- Type =DuplicateValues($A$1:$F$3000) into formula bar and press CTRL+SHIFT+ENTER

### How to count duplicate values

- Select a cell
- Type =DuplicateValues($A$1:$F$3000, TRUE) into formula bar and press
*ENTER.*

### Blog posts that may interest you:

- Extract a list of duplicates from a column using array formula in excel
- Extract duplicates from a range using excel array formula
- Highlight duplicate values in a range using conditional formatting in excel
- Extract a list of alphabetically sorted duplicates from a column in excel
- Highlight duplicate values in two ranges combined using conditional formatting in excel
- Count duplicate distinct values in a column in excel
- Highlight duplicates on same date, week or month using conditional formatting in excel
- Filter duplicate words from a cell range in excel (udf)
- Count unique and duplicates text values in a closed workbook in excel (formula)

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