On 23 Dec 2005 08:53:44 -0800, "Bayo" wrote:

Below is from a post dated 2004 by Ron Rosenfeld. It is supposed to
check the comma seperated numbers in a cell and filter unique ones and
show in sorted. Example given was 1,2,3,4,2,3,4,1,5,2,5,3 should be
shown 1,2,3,4,5. In fact, it does so with these numbers. But when the
numbers are 2,3,4,2,3,4,1,5,2, it comes up as 2,2,3,4,5 (1 disappears
and duplicate 2s). Or 9,31,5,4,11,12 becomes 9,12,31,4,5 (11
dissappears).
I usually try and modify the codes, however this is too complicated for
me. I am more into listing unique values, sorting would be an
additional good function, however not really a must.
Any help will be very much appreciated.

**********************************
Function UniqueNos(str As String) As String
Dim Temp, Temp2
Dim i As Integer, j As Integer

Temp = Split(str, ",")
ReDim Temp2(0)
BubbleSort 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

UniqueNos = Join(Temp2, ",")

End Function

Function BubbleSort(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 = 1 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
****************************

Regards,

Bayo

There's an error in the Bubblesort routine.

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
-- For i = 0 To UBound(TempArray) - 1
^^^

That line needs to be changed.

Or, as Bob suggested, you could also change the '1' to LBound(TempArray)


--ron