EXCLUDING DUPES IN STRING ARRAY !!!!
in [Programming]
|
Prev: Using Access form to assign values of variables in an Excel VBA program
Next: Help Designing an Hyperlink process in Excel
From: jay dean on 10 Apr 2010 13:52 Thank -- RB, Rick, Bernd, Dave, and Chip !! Your responses have been very helpful. Jay Dean *** Sent via Developersdex http://www.developersdex.com ***
From: Rick Rothstein on 10 Apr 2010 14:12 Here is a method that should be faster than what I have posted previously... this is the code I mentioned in my other response (to myself)... Dim X As Long, StartPosition As Long, ArrayIndex As Long Dim B As String, CheckString As String, Arr() As String '.... '.... ReDim Arr(1 To SomeMaxIndex) CheckString = String(200000, Chr(1)) StartPosition = 2 ArrayIndex = LBound(Arr) For X = 1 To SomeMaxIndex ' ' Some kind of conditioning code goes here I presume ' If YourCondition Then If InStr(CheckString, Chr(1) & B & Chr(1)) = 0 Then Arr(ArrayIndex) = B ArrayIndex = ArrayIndex + 1 Mid(CheckString, StartPosition) = B StartPosition = StartPosition + Len(B) + 1 End If End If Next ReDim Preserve Arr(1 To (ArrayIndex - 1)) There is one drawback to this method though, you have to estimate the maximum number of characters that could be in the CheckString variable. To do this, you need to be somewhat familiar with your data. Let's say the longest text string you expect to have is 19 characters long and that you expect to have about 10,000 unique text strings when you are done processing your data. Add one to the maximum number of characters and then multiply in order to get the upper limit (I called it SomeMaxIndex in my code above) to Dim your Arr array to... SomeMaxIndex = (19 + 1) * 10000 which is how I got my estimate of 200000 in my CheckString assignment statement above. Also note that the StartPosition will always be 2 (we need a Chr(1) in front of the CheckString text). -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:Opj1K9L2KHA.5212(a)TK2MSFTNGP04.phx.gbl... > You could maintain what I would call a "check string" for this purpose. > Let's say the name of this String variable is CheckString. Then you can do > this in a loop... > > For X = 1 To SomethingLessThanInfinity > ' > ' Some kind of conditioning code goes here I presume > ' > If YourCondition Then > If Instr(CheckString, Chr(1) & B & Chr(1)) = 0 Then > Arr(X) = B > CheckString = CheckString & Chr(1) & B & Chr(1) > End If > Next > > If the text value in variable B is not in CheckString, then this is the > first time you have seen its value, so assign it to the array and then > store its value, with a delimiter on both sides of it, into CheckString. I > have used Chr(1) as my delimiter because under normal circumstances it > will not appear in any of the text being assigned to B during the loop. > You can use any character (or characters) that you **know** for certain > will never appear in your text strings for the delimiter. The reason you > need this delimiter is to stop accidental substring finds crossing over > between your B values. For example, if two consecutive values being > assigned to B during the loop were "moth" and "error" and did not use a > delimiter between them, then they would go into the CheckString as > "...motherror..." and the latter assignment of "mother" to B would > register as already having been added to the array... the delimiters > guarantee this won't happen. > > -- > Rick (MVP - Excel) > > > > "jay dean" <fresh1700(a)yahoo.com> wrote in message > news:#$LzCWL2KHA.4912(a)TK2MSFTNGP06.phx.gbl... >> 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 *** >
From: Chip Pearson on 10 Apr 2010 16:00 >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" <bartsmissaert(a)blueyonder.co.uk> 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" <chip(a)cpearson.com> wrote in message >news:9m61s5tf633bn2bmhtvvv74abum0972svv(a)4ax.com... >>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 <fresh1700(a)yahoo.com> >> 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 ***
From: RB Smissaert on 10 Apr 2010 17:02
Did you run the posted code? RBS "Chip Pearson" <chip(a)cpearson.com> wrote in message news:05m1s59q6qicckouljjehpjfit09sotfkv(a)4ax.com... > >>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" > <bartsmissaert(a)blueyonder.co.uk> 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" <chip(a)cpearson.com> wrote in message >>news:9m61s5tf633bn2bmhtvvv74abum0972svv(a)4ax.com... >>>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 <fresh1700(a)yahoo.com> >>> 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 *** |