unique values in an array
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.
|