LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,231
Default 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.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unique values in an array. Jerry Excel Discussion (Misc queries) 2 October 15th 09 06:44 PM
Returning an array of unique values? Blue Max Excel Worksheet Functions 10 January 16th 08 02:51 AM
Array formula for unique values Dan Hatola Excel Worksheet Functions 1 January 20th 07 03:06 AM
Array formula for unique values Dan Hatola Excel Worksheet Functions 0 January 20th 07 02:11 AM
unique values to array Gary Keramidas Excel Programming 4 November 6th 06 02:31 PM


All times are GMT +1. The time now is 06:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"