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 09:55 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 10:12 Considering all else (the array will be used somewhere, so the check for duplicates is not the only deciding factor) I doubt there are better ways than just looping through the array, but a few things to consider: 1. Is the string array sorted? If it is then you could check with a binary search. That will be a lot faster than a full loop. 2. Could you use a collection or dictionary instead of the array? With that the check for duplicates might be faster. 3. You could have the array in a string variable, eg: element1 & | element2 & | etc. With that you could than check with Instr. Concatenating the strings though will be a big overhead, so I doubt it will help. 4. You could use Olaf Schmidt's dhRichClient3.dll. That has a very fast collection object and dictionary object. 5. You could invest in Jim Mach's Stamina dll. That has some fast array routines that could speed this up. Can't think of much else. RBS "jay dean" <fresh1700(a)yahoo.com> wrote in message news:%23$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: Bernd P on 10 Apr 2010 10:34 Hello Jay Dean, I would use an approach with a scripting dictionary or with a collection. See http://sulprobil.com/html/lfreq.html for example. Regards, Bernd
From: Dave Peterson on 10 Apr 2010 10:42 John Walkenbach shows a way to use a collection in this code: http://www.j-walk.com/ss/excel/tips/tip47.htm He's actually filling a listbox with that unique list, but it should work ok. And he also sorts the list -- you may find that useful, too. 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 *** -- Dave Peterson
From: Rick Rothstein on 10 Apr 2010 11:05
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 *** |