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

1. Press Alt-F11 to open the Visual Basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor

How to extract duplicate values

1. Select a cell range
2. Type =DuplicateValues($A$1:$F$3000, FALSE)
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter once
5. Release all keys

How to count duplicate values

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

Get the Excel file

Duplicate-dates-large-data-set1.xls

User defined function category

Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Break up values in a cell range into separate cells based on a space character as a delimiter
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]

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. […]

Excel categories

Leave a Comment