Example #2 – VBA Match From Another Sheet. Assume the same set of data from the above is there in two different sheets. For an example table array is there in the sheet name called “Data Sheet” and Lookup Value is there in the sheet name called “Result Sheet”. Mar 21, 2015 I'm trying to loop through range a on sheet a and, search for each value in range b on sheet b and then add to a column in sheet b if there's a match.
![Find Find](/uploads/1/2/5/4/125402292/105276731.png)
Problem: Looking for a more efficient way of finding whether there is an exact matching value in a 1d array - essentially a boolean true/false.Am I overlooking something obvious? Or am I simply using the wrong data structure, by using an array when I probably should be using a collection object or a dictionary? In the latter I could check the.Contains or.Exists method, respectivelyIn Excel I can check for a value in a vector array like: If Not IsError(Application.Match(strSearch, varToSearch, False)) Then' Do stuffEnd IfThis returns an exact match index, obviously subject to limitations of Match function which only finds the first matching value in this context. If we're going to talk about performance then there's no substutute for running some tests. In my experience Application.Match is up to ten times slower than calling a function which uses a loop.
Sub TesterDim i As Long, b, tDim arr(1 To 100) As StringFor i = 1 To 100arr(i) = 'Value' & iNext it = TimerFor i = 1 To 100000b = Contains(arr, 'Value50')Next iDebug.Print 'Contains', Timer - tt = TimerFor i = 1 To 100000b = Application.Match(arr, 'Value50', False)Next iDebug.Print 'Match', Timer - tEnd SubFunction Contains(arr, v) As BooleanDim rv As Boolean, lb As Long, ub As Long, i As Longlb = LBound(arr)ub = UBound(arr)For i = lb To ubIf arr(i) = v Thenrv = TrueExit ForEnd IfNext iContains = rvEnd FunctionOutput: Contains 0.8710938Match 4.210938. I used to look for a best replace solution. It should work for simple finding as well.To find first instance of a string you can try using this code: Sub findstrings1Dim ArrayCh As VariantDim rng As RangeDim i As IntegerArrayCh = Array('a', 'b', 'c')With ActiveSheet.CellsFor i = LBound(ArrayCh) To UBound(ArrayCh)Set rng =.Find(What:=ArrayCh(i), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False)Debug.Print rng.AddressNext iEnd WithEnd SubIf you want to find all instances try the below. 'A more efficient way (compared to Application.Match)of finding whether a string value exists in an array':I believe there is no more efficient way than the one you are using, i.e., Application.Match.Arrays allow efficient access in any element if we know the index of that element. If we want to do anything by element value (even checking if an element exists), we have to scan all the elements of the array in the worst case. Therefore, the worst case needs n element comparisons, where n is the size of the array.
So the maximum time we need to find if an element exists is linear in the size of the input, i.e., O(n). This applies to any language that uses conventional arrays.The only case where we can be more efficient, is when the array has special structure. For your example, if the elements of the array are sorted (e.g. Alphabetically), then we do not need to scan all the array: we compare with the middle element, and then compare with the left or right part of the array (. But without assuming any special structure, there is no hope.The Dictionary/Collection as you point, offers constant key access to their elements ( O(1)). What perhaps is not very well documented is that one can also have index access to the dictionary elements (Keys and Items): the order in which elements are entered into the Dictionary is preserved.
Their main disadvantage is that they use more memory as two objects are stored for each element.To wrap up, although If Not IsError(Excel.Application.match(.)) looks silly, it is still the more efficient way (at least in theory). On permission issues my knowledge is very limited. Depending on the host application, there are always some Find-type functions ( C has find and findif for example).I hope that helps!EditI would like to add a couple of thoughts, after reading the amended version of the post and Tim's answer.
The above text is focusing on the theoretical time complexity of the various data structures and ignores implementation issues. I think the spirit of the question was rather, 'given a certain data structure (array)', what is the most efficient way in practice of checking existence.To this end, Tim's answer is an eye-opener.The conventional rule 'if VBA can do it for you then don't write it again yourself' is not always true. Simple operations like looping and comparisons can be faster that 'agreegate' VBA functions. Two interesting links are.