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:

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

  1. Press Alt-F11 to open 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 =DuplicateValues($A$1:$F$3000) into formula bar and press CTRL+SHIFT+ENTER

How to count duplicate values

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

Blog posts that may interest you: