| Home |
| Search |
| Today's Posts |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
EXCLUDING DUPES IN STRING ARRAY !!!!
Did you run the posted code?
RBS "Chip Pearson" wrote in message ... Unless I am overlooking something, using Application.Match looks very slow to me. I don't know. I ran it with an array of about 1000 elements and it was essentially instantaneous. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 10 Apr 2010 17:03:40 +0100, "RB Smissaert" wrote: Unless I am overlooking something, using Application.Match looks very slow to me. Using the helper code as posted previously. Sub ArrayTest3() Dim i As Long Dim n As Long Dim x As Long Dim arrString(1 To 10000) As String Dim strAdd As String Dim V As Variant StartSW For i = 1 To 10000 strAdd = RandomWord(2) V = Application.Match(strAdd, arrString, 0) If IsError(V) Then x = x + 1 arrString(x) = strAdd End If Next i StopSW For i = 1 To x Cells(i, 1) = arrString(i) Next i End Sub RBS "Chip Pearson" wrote in message . .. I think the fastest method is to test for a value's existence in an array is to use the Match function. For example, examine the following code: ' <START CODE Dim Arr(1 To 5) As String Dim Ndx As Long Dim B As String Dim V As Variant '''''''''''''' ' load up some test values For Ndx = 1 To 3 Arr(Ndx) = Chr(Asc("a") + Ndx - 1) Next Ndx '''''''''''''' B = "f" ' doesn't exist in Arr V = Application.Match(B, Arr, 0) If IsError(V) Then ' does not exist Arr(Ndx) = B Else ' exists, do nothing End If '''''''''''''' B = "b" ' exists in Arr V = Application.Match(B, Arr, 0) If IsError(V) Then ' does not exist Arr(Ndx) = B Else ' exists, do nothing End If '''''''''''''' ' list content For Ndx = LBound(Arr) To UBound(Arr) Debug.Print Ndx, Arr(Ndx) Next Ndx ' <END CODE First, part of the array Arr is given some test values, "a", "b", and "c". Then, B is assigned "f". The value "f" is searched for in Arr by the Match function. The variant V holds the result of Match. If it is an error (IsError = True), then "f" does not exist in the array and is added to the array. It is assumed that at this point in the code, the variable Ndx points to the first unused element of Arr. Next, the value "b" is assigned to the variable B and again Match is used to see if "b" exists in Arr. Since it does already exist, Match assigns its position to V, and when V is tested for an error, IsError returns False so we know "b" already exists. Finally, the code just lists the content of Arr. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 10 Apr 2010 06:55:41 -0700, jay dean wrote: Hi - B is a string var. In my code, if a certain condition is met, then store B in th next available index of Arr(). However, before I store B, I need to check that the current value of B does not already exist in Arr(). Is there a "faster" way to accomplish this, or I need to loop from lbound(Arr) to Ubound(Arr) every time to check if the new value to be stored already exists? Thanks Jay Dean *** Sent via Developersdex http://www.developersdex.com *** |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula percent complete excluding given text string | Excel Discussion (Misc queries) | |||
| Excluding a string | Excel Programming | |||
| Summing part of a string excluding cells | Excel Worksheet Functions | |||
| summing part of a string, excluding cells | Excel Worksheet Functions | |||
| COUNTIF - everything excluding a string | Excel Worksheet Functions | |||
Threaded Mode
