Unique Function UDF Issue

[Juggler_IN]

An Excel 2003 udf for unique items works fine for 2 or greater than two unique items in a list of repeated items. BUT if there is 1 unique item, it repeats the single unique value in the output across rows. How to fix it?

Public Function UDFlist( _
       ByRef x As Range) As Variant

    Dim c As New Collection

    Dim k&

    Dim y As Range

    Dim u, v

    For Each y In x
        If Application.WorksheetFunction.CountIf(x, y.Value) = 1& Then
            c.Add y.Value
        End If
    Next y

    If c.Count = 0& Then
        UDFlist = CVErr(xlErrNA)
        Exit Function
    Else
        ReDim u(1& To c.Count, 1& To 1&)
        For Each v In c
            k = (k + 1&)
            u(k, 1&) = v
        Next v
        UDFlist = u
        Exit Function
    End If

End Function

For Example, for two uniques it outputs:

1​	1​
2​	3​
3​	#N/A​
2​	#N/A​

But, for a single unique it outputs:

1​	1​
2​	1​
2​	1​
2​	1​

 

[Fluff]

How about

Public Function UDFlist( _
       ByRef x As Range) As Variant


    Dim y As Range


    Dim d As Object
    Set d = CreateObject("scripting.dictionary")
    For Each y In x
      If Not d.exists(y.Value) Then
         d.Add y.Value, y.Value
      Else
         d(y.Value) = CVErr(xlErrNA)
      End If
    Next y

    UDFlist = Application.Transpose(d.Items)
End Function

 

[Norie]

Aren't there 2 unique items in your second example?

 

[Fluff]

Nope, there are 2 distinct values, but only one is unique.

 

[Juggler_IN]

@Fluff; It outputs results in the middle of #N/As. Can the values come at the start?

Currently,

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

2​

6​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

#N/A​

 

[Fluff]

How about

Public Function UDFlist( _
       ByRef x As Range) As Variant


    Dim y As Range
    Dim i As Long, j As Long
    Dim a As Variant
    
    Dim d As Object
    Set d = CreateObject("scripting.dictionary")
   
    For Each y In x
      If Not d.exists(y.Value) Then
         d.Add y.Value, y.Value
      Else
         d(y.Value) = ""
      End If
    Next y
    ReDim a(1 To x.count, 1 To 1)
    For i = 0 To d.count - 1
      If d.Items()(i) <> "" Then
         j = j + 1
         a(j, 1) = d.Items()(i)
      End If
   Next i
   For i = j + 1 To UBound(a)
      a(i, 1) = CVErr(2042)
   Next i
    UDFlist = a
End Function

 

[Juggler_IN]

@Fluff; thanks.

 

[Fluff]

My pleasure