## Filter duplicates in a large dataset [UDF]

*Article updated on January 20, 2018*

### 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 […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article