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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
Very straightforward, just change this line
For i = 1 To UBound(TempArray) - 1 to For i = LBound(TempArray) To UBound(TempArray) - 1 in the BubbleSort function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bayo" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
On Fri, 23 Dec 2005 17:44:03 -0000, "Bob Phillips"
wrote: Very straightforward, just change this line For i = 1 To UBound(TempArray) - 1 to For i = LBound(TempArray) To UBound(TempArray) - 1 in the BubbleSort function Nice catch. I don't know where that '1' came from. On the version I have in my add-in on my computer, it shows as a '0' which would work, also. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
Thank you very much to both of you. I tried and kept both corrections
and both work perfect, I think it sorts according first digit, like 25 is in front of 3, it returns 1,2,25,3,35,42,5 etc...however it is still OK after having the unique values. Really great help and of course and excellent UDF (still have to work on it to understand...). Regards, Merry X-Mas and Happy Many Years... Bayo |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
On 24 Dec 2005 21:36:41 -0800, "Bayo" wrote:
Thank you very much to both of you. I tried and kept both corrections and both work perfect, I think it sorts according first digit, like 25 is in front of 3, it returns 1,2,25,3,35,42,5 etc...however it is still OK after having the unique values. Really great help and of course and excellent UDF (still have to work on it to understand...). Regards, Merry X-Mas and Happy Many Years... Bayo The purpose of the function is to return unique values. It will work with either numbers or non-numeric values. The purpose of the sorting routine is NOT to be able to return the values in a sorted order, but rather to place identical values "next to each other" so the non-unique values can be more readily identified. Using this technique, it happens that the values are returned in an alpha sorted order. For this purpose the values are all handled as strings. If you want to return the values sorted numerically (as opposed to the alpha sort which is presently the case), you can convert the values to numbers, and then resort. For example: (-- marks the changed lines) ============================ .... For i = 1 To UBound(Temp) If Temp(i) Temp(i - 1) Then j = j + 1 ReDim Preserve Temp2(j) -- Temp2(j) = IIf(IsNumeric(Temp(i)), Val(Temp(i)), Temp(i)) End If Next i -- BubbleSort Temp2 UniqueNos = Join(Temp2, ",") End Function ============================ --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
Ron Rosenfeld wrote: On 24 Dec 2005 21:36:41 -0800, "Bayo" wrote: Thank you very much to both of you. I tried and kept both corrections and both work perfect, I think it sorts according first digit, like 25 is in front of 3, it returns 1,2,25,3,35,42,5 etc...however it is still OK after having the unique values. Really great help and of course and excellent UDF (still have to work on it to understand...). Regards, Merry X-Mas and Happy Many Years... Bayo The purpose of the function is to return unique values. It will work with either numbers or non-numeric values. The purpose of the sorting routine is NOT to be able to return the values in a sorted order, but rather to place identical values "next to each other" so the non-unique values can be more readily identified. Using this technique, it happens that the values are returned in an alpha sorted order. For this purpose the values are all handled as strings. If you want to return the values sorted numerically (as opposed to the alpha sort which is presently the case), you can convert the values to numbers, and then resort. For example: (-- marks the changed lines) ============================ ... For i = 1 To UBound(Temp) If Temp(i) Temp(i - 1) Then j = j + 1 ReDim Preserve Temp2(j) -- Temp2(j) = IIf(IsNumeric(Temp(i)), Val(Temp(i)), Temp(i)) End If Next i -- BubbleSort Temp2 UniqueNos = Join(Temp2, ",") End Function ============================ --ron 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Uniquenos UDF
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|