Home |
Search |
Today's Posts |
#12
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Alan Beban wrote...
.... Seems that by choosing case insensitivity, the user will have indicated that it doesn't make any difference which is kept as the distinct value; that he/she is indicating that for his/her purposes they are all equivalent. Fair enough, so first match. An alternative way to handle this would be to use a different function to change all text to upper, lower or Proper case before calling the function that would remove duplicates. . . . The one instance of "" (which will often result from unwanted blanks in the range/array from which duplicates are to be eliminated) is eliminated with a single simple line of code -- If OmitBlanks Then x.Remove ("") Now that you mention it, this lies in an inefficient block of code. On Error Resume Next For Each Elem In arr x.Add Item:=Elem, Key:=CStr(Elem) Next If OmitBlanks Then x.Remove ("") On Error GoTo 0 There's no need for the enclosing error trapping, which slows things down. Just test whether each new array value is already loaded into the dictionary, and only add those that aren't. Similarly, only remove "" if it exists. For Each Elem In arr If not x.Exists(Elem) Then x.Add Item:=0, Key:=Elem Next If OmitBlanks And x.Exists("") Then x.Remove "" There is no need for the ArrayUniques function to contain the separate filtering function for any additional filtering that a user might design. The filtering can readily be accomplished, if desired, with something like FilteringFunction(ArrayUniques([ArrayUniques parameters]), [FilteringFunction parameters]) Indeed, but note that your parameter to omit blanks only eliminates zero-length strings. If the input array were a range, and that range contained blank cells (in the ISBLANK sense), those cells would be recorded as Empty rather than "", so using your optional parameter wouldn't eliminate them. My point is that IF a user would want to eliminate both "" and Empty, it's more efficient to reduce the array to the distinct values POSSIBLY INCLUDING "" and Empty, then eliminate the at most single remaining "" and Empty items. If all a user would ever want to do would be eliminating "" values, your approach would be fine. However, if a user would want to eliminate "" AND Empty values (and/or possibly other values, e.g., whatever Excel would receive representing missing values from database records, which are not always "" or Empty), then the likely need to call another function would negate the possible usefullness of eliminating "" in ArrayUniques. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Unique values in an array. | Excel Discussion (Misc queries) | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
unique values to array | Excel Programming |