Problem with function that creates an array of unqiue values

gers1978 · Jun 28th 2016

Ok, firstly, here's the function:

Code

Private Function UniqueValues(InputRange As Range)
    Dim cell As Range
    Dim tempList As Variant
    
    tempList = vbNullString
    
    For Each cell In InputRange
        If cell.Value <> vbNullString Then
            If InStr(1, tempList, cell.Value) = 0 Then
                If tempList = vbNullString Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
    Next cell
    
    UniqueValues = Split(tempList, "|")
    
End Function

Display More

The problem comes when one of the unique values is contained *within* another unique value.

For example, in my column on the worksheet the unique values might be:

FGHCAD
DES PMO
Admin Support
PMO
Tech Assist

The problem with the function above is it effectively builds a string that (should) looks like:

FGHCAD|DES PMO|Admin Support|PMO|Tech Assist

Then Splits the items into an array based on the | character. Whilst this is a very good and clever solution, it has one flaw that I'm not sure how to overcome, namely that because the string "PMO" exists within the string "DES PMO", then item "PMO" never gets added. In other words the temp list ends up as:

FGHCAD|DES PMO|Admin Support|Tech Assist

so that when the Split happens, "PMO" doesn't get added.

Any ideas?

Thanks

Also posted here: http://www.mrexcel.com/forum/e…e-values.html#post4562746

**KjBox** · Jun 28th 2016

Re: Problem with function that creates an array of unqiue values

Try this

Code

Private Function UniqueValues(InputRange As Range)
    Dim x, i As Long
     
    x = InputRange.Value
    With CreateObject("system.collections.arraylist")
        For i = 1 To ubpond(x, 1)
            If Not .contains(x(i, 1)) Then .Add x(i, 1)
        Next
        UniqueValues = .toarray
    End With
         
End Function

Display More

Problem with function that creates an array of unqiue values

Participate now!

Share