Returns the list of items that appear more than once.


* 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

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

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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext