Optimizing Loop

[vbNeo]

How can I optimize this loop?:

For I = 0 To UBound(hk) - 1
 
For D = 0 To UBound(sono) - 1
If InStr(1, Trim(sono(D)), Trim(hk(I))) > 0 Then
sAll = sAll & vbCrLf & Trim(hk(I)) & "  ***PASSER***"
GoTo NextLoop
End If
 
Next
sAll = sAll & vbCrLf & Trim(hk(I)) & "  ***PASSER IKKE***"
NextLoop:
Next

What I need to do is see if any of the values in 'hk' are similar to any of the values in 'sono'.

A friend told me of something called binary sorting - if this is a possibility, how would I do it?

Cheers!

[si_the_geek]

the sorting idea could possibly be useful, how many items are in each array?

here's an improved version of what you posted:

Dim max_hk as long
Dim max_sono as long
Dim MatchFound as Boolean
Dim CurrentHK as String
 
max_hk = UBound(hk) - 1
max_sono = UBound(sono) - 1
 
For I = 0 To max_hk
 
  MatchFound = False
  CurrentHK = Trim(hk(I))
 
  For D = 0 To max_sono
    If InStr(1, Trim(sono(D)), CurrentHK) > 0 Then
      sAll = sAll & vbCrLf & CurrentHK & "  ***PASSER***"
      MatchFound = True
      Exit For
    End If
  Next D
 
  If Not MatchFound Then
    sAll = sAll & vbCrLf & CurrentHK & "  ***PASSER IKKE***"
  End If
 
Next I

[vbNeo]

Thanks for your optimization!

Gonna try it out, see if it's enough to be acceptable =).

But - The number of items in the arrays are declared to 4000 for hk, and 8000 for sono - Though this isn't toally correct figures(there are some blank vars), it's close to it, with a margin of 50-70 max.

Cheers!

[si_the_geek]

cool.. let us know the time it takes (for both the old & new versions). Make sure that I and D are both declared as Long.


As you have about 32 million checks it will take a fair amount of time, especially with InStr.

unfortunately binary sorting would be completely useless with InStr... are you sure you need it? (are you just intending to compare the first few characters?)

if a sono() matches one hk(), is it possible that it will match another hk()?

[vbNeo]

InStr isn't completely neccessary, i'll just use Trim() instead.

[si_the_geek]

in that case a binary search would be possible... I'll post a description/example tomorrow if I remember!

[si_the_geek]

I had a little thought on how to speed it up... convert "sono" to a single string, then just do an InStr on that string (once) for each "hk".

This is about 3 times faster (for random data) than my previous post, and about 5 times faster than your original version:

Dim i As Long, d As Long
Dim max_hk As Long
Dim CurrentHK As String
Dim sono_str As String
 
max_hk = UBound(hk) - 1
 
'fill the string with the entire array, with each item surrounded by character 0
sono_str = Chr(0) _
         & Join(sono, Chr(0)) _
         & Chr(0)
 
For i = 0 To max_hk
 
  CurrentHK = Trim(hk(i))
 
  If InStr(1, sono_str, Chr(0) & CurrentHK [i]& Chr(0)[/i]) Then
    sAll = sAll & vbCrLf & CurrentHK & "  ***PASSER***"
  Else
    sAll = sAll & vbCrLf & CurrentHK & "  ***PASSER IKKE***"
  End If
 
Next i

If HK is likely to be the shorter of the two values then the InStr could have the final Chr(0) removed to add more functionality. As it is the two must be the same, with that change they just need to start the same.

To be honest, unless your data is too large for this approach then I dont see a binary search being worthwhile.