DUPLICATEVALUES

Returns the list of items that appear more than once.

Remarks

* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* The equivalent JavaScript function is DUPLICATEVALUES

Public Function DUPLICATEVALUES(ByVal rgeValues As Range, _ 
                       Optional ByVal bAsArray As Boolean = False) As Variant 

Dim lcellcount As Long 
Dim rgeCell As Range 
Dim lmatchindex As Long 
Dim sduplicatevalues As String 
Dim aTheDuplicates As Variant 

    For lcellcount = 1 To rgeValues.Cells.Count 
       Set rgeCell = rgeValues.Cells(lcellcount) 
    
       If (rgeCell.Value <> "") Then 
      
          lmatchindex = Application.WorksheetFunction.Match(rgeCell, rgeValues, 0) 
          
          If (lmatchindex <> lcellcount) Then 
             sduplicatevalues = sduplicatevalues & "," & rgeCell.Value 
          End If 
       End If 
    Next 

   If (Len(sduplicatevalues) = 0) Then 
      DUPLICATEVALUES = "no duplicates" 
   Else 
      sduplicatevalues = Right(sduplicatevalues, Len(sduplicatevalues) - 1) 
   End If 

   If (bAsArray = False) Then 
      DUPLICATEVALUES = sduplicatevalues 
   Else 
      aTheDuplicates = VBA.Split(sduplicatevalues, ",") 
      aTheDuplicates = Application.WorksheetFunction.Transpose(aTheDuplicates) 
      DUPLICATEVALUES = aTheDuplicates 
   End If 
       
End Function