Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Entries Function
Wow....
Thanks Ron. Works Perfect! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Entries Function
On 13 Mar 2006 15:08:46 -0800, wrote:
Wow.... Thanks Ron. Works Perfect! Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum unique entries only | Excel Discussion (Misc queries) | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Unique Entries | Excel Discussion (Misc queries) | |||
Unique Entries | Excel Worksheet Functions | |||
Unique Entries | Excel Programming |