Unique Entries Function
This will do it:
Function UniqueEntry(rngValues As Range, lOrder As Long) As String
Dim i As Long
Dim arr
Dim coll As Collection
Set coll = New Collection
arr = rngValues
For i = 1 To UBound(arr)
On Error Resume Next
coll.Add arr(i, 1), CStr(arr(i, 1))
Next
If lOrder coll.Count Then
UniqueEntry = ""
Else
UniqueEntry = coll(lOrder)
End If
End Function
Sub test()
MsgBox UniqueEntry(Range(Cells(1), Cells(25, 1)), 2)
End Sub
RBS
wrote in message
oups.com...
It seems like it is every couple of weeks that I am searching these
forums for excel tricks and ways to manipulate duplicates in a list.
However, I am wondering if it is possible to put together a simple UDF
that would look like this:
=Uniqueentry(A2:A50000,1) ; where the result would be the FIRST unique
entry in the range.
=Uniqueentry(A2:A50000,2) ; where the result would be the SECOND unique
entry in the range.
and, assuming that the range contains only 100 unique entries:
=Uniqueentry(A2:A50000,101) -- would result in a blank string ("")
Also, I am purposefully using the a2:a50000 example because I need this
UDF to look at huge ranges pretty quickly without calculating for a
long long time.
Any takers out there? I would be forever grateful.
|