I need a method of quickly searching a large 2 dimensional array. I extract the array from Excel, so 1 dimension represents the rows and the second the columns. I wish to obtain a list of the rows where the columns match certain criteria. I need to know the row number (or index of the array).
For example, if I extract a range from excel. I may need to find all rows where column A =”dog” and column B = 7 and column J “a”. I only know which columns and which value to find at run time, so I can’t hard code the column index.
I could use a simple loop, but is this efficient ? I need to run it several thousand times, searching for different criteria each time.
For r As Integer = 0 To UBound(myArray, 0) - 1
match = True
For c = 0 To UBound(myArray, 1) - 1
If not doesValueMeetCriteria(myarray(r,c) then
match = False
Exit For
End If
Next
If match Then addRowToMatchedRows(r)
Next
The doesValueMeetCriteria function is a simple function that checks the value of the array element against the query requirement. e.g. Column A = dog etc.
Is it more effiecent to create a datatable from the array and use the .select method ?
Can I use Linq in some way ?
Perhaps some form of dictionary or hashtable ?
Or is the simple loop the most effiecent ?
Your suggestions are most welcome.
