Uniquenos UDF
Ron Rosenfeld wrote:
On 26 Dec 2005 08:10:37 -0800, "Bayo" wrote:
Thanks Ron,
That works fine when I place a comma in front of initial entry,
otherwhise, first number appears in the result and then the remaining
ones appear in sorted order.
Regards,
Baybars
Sorry about that. The conversion of text to numbers, the way I wrote it,
leaves out the 0th element of the array. So that was always remaining as a
text string and getting sorted to the end.
Here's another version that should take care of that issue, and give a
numerically sorted output, with text at the end:
====================================
Option Explicit
Function UniqueNos(str As String) As String
Dim Temp, Temp2
Dim i As Integer, j As Integer
Temp = Split(str, ",")
ReDim Temp2(0)
Bubblesrt Temp
Temp2(0) = Temp(0)
j = 0
For i = 1 To UBound(Temp)
If Temp(i) Temp(i - 1) Then
j = j + 1
ReDim Preserve Temp2(j)
Temp2(j) = Temp(i)
End If
Next i
For j = 0 To UBound(Temp2)
Temp2(j) = IIf(IsNumeric(Temp2(j)), Val(Temp2(j)), Temp2(j))
Next j
Bubblesrt Temp2
UniqueNos = Join(Temp2, ",")
End Function
Function Bubblesrt(TempArray As Variant)
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer
' Loop until no more "exchanges" are made.
Do
NoExchanges = True
' Loop through each element in the array.
For i = 0 To UBound(TempArray) - 1
' If the element is greater than the element
' following it, exchange the two elements.
If TempArray(i) TempArray(i + 1) Then
NoExchanges = False
Temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = Temp
End If
Next i
Loop While Not (NoExchanges)
End Function
===========================
--ron
Ron,
This is absolutely perfect...
Thanks a lot...
Happy New Year...
Baybars
|