Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
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 |