Unique Entries Function

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.

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

<samer.kurdi@gmail.com> wrote in message
news:1142034066.158557.235050@e56g2000cwe.googlegroups.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.
>

On 10 Mar 2006 15:41:06 -0800, samer.kurdi@gmail.com wrote:

>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.

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula:

=INDEX(UNIQUEVALUES(A2:A50000,1),1) for the first
=INDEX(UNIQUEVALUES(A2:A50000,1),2) for the second

These are returned in a sorted order. As written, it will sort ascending. To
sort descending:

=INDEX(UNIQUEVALUES(A2:A50000),1) for the first
=INDEX(UNIQUEVALUES(A2:A50000),2) for the second






--ron

Wow....
Thanks Ron. Works Perfect!

On 13 Mar 2006 15:08:46 -0800, samer.kurdi@gmail.com wrote:

>Wow....
>Thanks Ron. Works Perfect!

Glad to help. Thanks for the feedback.
--ron