• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

Find text in both MSHflexgrid

Hello all

I'm having an issue on this code where i would like to add 1 small criteria to it.

So the code work just like this but i also want to say that if
MSHFlexGrid2.TextMatrix(r1, Form56.MSHFlexGrid1.TextMatrix(r2, 4) = Form55.MSHFlexGrid2.TextMatrix(r1, 39)

Open in new window


The problem is that in Form56.MSHFlexGrid1.TextMatrix(r2, 4) , i could see a text that look like for example 00008987989M
and in my Form55.MSHFlexGrid2.TextMatrix(r1, 39), i can see a phrase but in that phrase i could see the same text ex: 00008987989M. Actually, it could be any where in the cell.

So if for ex:
Form56.MSHFlexGrid1.TextMatrix(r2, 4)  = 00008987989M
Form55.MSHFlexGrid2.TextMatrix(r1, 39) =   BOL: 00008987989M  / 00008985559M
 So since we have a match with the text: 00008987989M i would continu with the rest of the code:
 Next r2
   If bFound Then

     
   Else
      Form55.MSHFlexGrid2.Row = r1
      Form55.MSHFlexGrid2.Col = 39
      Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
      Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"
           
   End If
Next r1

Open in new window




How can i add this criteria?

Thanks again



Full code
Dim r1 As Long, r2 As Long, i As Long
Dim Text1 As String
Dim Text2 As String
Dim bFound As Boolean
For r1 = 1 To Form55.MSHFlexGrid2.Rows - 1
   bFound = False
   For r2 = 1 To Form56.MSHFlexGrid1.Rows - 1
      Text1 = UCase(Trim(Form55.MSHFlexGrid2.TextMatrix(r1, 33)))
     Text2 = UCase(Trim(MSHFlexGrid1.TextMatrix(r2, 24)))
      If Text2 = Text1 Then
         bFound = False
         Exit For
      Else
         bFound = True
      End If
      
      
      
      
   Next r2
   If bFound Then

     
   Else
      Form55.MSHFlexGrid2.Row = r1
      Form55.MSHFlexGrid2.Col = 39
      Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
      Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"
           
   End If
Next r1

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 20
  • 14
1 Solution
 
aikimarkCommented:
Use the Instr() function to do your matching
Example:
If Instr(1, "BOL: 00008987989M  / 00008985559M", "00008987989M", vbCompareText) <> 0 Then
   Debug.Print "Matched"
Else
   Debug.Print "Not Matched"
End If

Open in new window


Of course, you would use your flexgrid references in place of the string literals in my example.
Form55.MSHFlexGrid2.TextMatrix(r1, 39)
0
 
Wilder1626Author Commented:
HI

Ok, i have tried like this. No error but it does not select all the one that should have a match.

Let me look at this.

Full code:
Dim r1 As Long, r2 As Long, i As Long
Dim Text1 As String
Dim Text2 As String
Dim bFound As Boolean
For r1 = 1 To Form55.MSHFlexGrid2.Rows - 1
   bFound = False
   For r2 = 1 To Form56.MSHFlexGrid1.Rows - 1
      Text1 = UCase(Trim(Form55.MSHFlexGrid2.TextMatrix(r1, 33)))
     Text2 = UCase(Trim(MSHFlexGrid1.TextMatrix(r2, 24)))
      If Text2 = Text1 And InStr(1, Form55.MSHFlexGrid2.TextMatrix(r1, 39)) <> 0 Then
            bFound = False
         Exit For
      Else
         bFound = True
      End If
      
      
      
      
   Next r2
   If bFound Then

      
   Else
           Form55.MSHFlexGrid2.Row = r1
           Form55.MSHFlexGrid2.Col = 39
           Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
           Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"
      
           
   End If
Next r1

Open in new window

0
 
aikimarkCommented:
you are missing a parameter in the Instr() function.  Notice that in my example, there are two strings.  I assumed that you would have two different variables in their place.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wilder1626Author Commented:
Oh, you are right.

I have changed the code to make it easier but now, like this it's  matching with everything, even what is not matching.

Am i missing something?

Dim i As Integer
Dim j As Integer


For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1
        If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4)) <> 0 Then

      Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
      Form55.MSHFlexGrid2.Row = i
      Form55.MSHFlexGrid2.Col = 38
      Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
    Else
    
    
    End If
     

Next
Next

Open in new window

0
 
Wilder1626Author Commented:
I've done some more google search and now, i did this, but still have the same issue where all match, even if there is no match

  If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then

Open in new window



Full code:
Dim i As Integer
Dim j As Integer


For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1
        If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then

      Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
      Form55.MSHFlexGrid2.Row = i
      Form55.MSHFlexGrid2.Col = 38
      Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
    Else
     Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
    
    End If
     

Next
Next

MsgBox "Done"

Open in new window

0
 
aikimarkCommented:
Please test this version of the code and post the ten lines it produces in the Immediate window.
Dim i As Integer
Dim j As Integer

Dim lngCount As Long

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
    For j = 1 To Form56.MSHFlexGrid1.Rows - 1
        
        If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then
            If lngCount < 10 Then
                Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & Form56.MSHFlexGrid1.TextMatrix(j, 4) & "'"
                lngCount = lngCount + 1
            End If
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
        Else
             Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
        End If

    Next
Next

MsgBox "Done"

Open in new window

0
 
Wilder1626Author Commented:
all have the MATCH result,  but actually, there is no match accept 1

i=1           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=4           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=5           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=6           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=2           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=4           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=5           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=6           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=3           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST1'   Form56.MSHFlexGrid1.TextMatrix(j, 4)=''

Open in new window

0
 
Wilder1626Author Commented:
This is the result in the grid

Form55.MSHFlexgrid2
0
 
aikimarkCommented:
Instr() has a nasty habit of returning a non-zero value when you are looking for an empty string.  I've added a length check in the following snippet.
Dim i As Integer
Dim j As Integer

Dim lngCount As Long

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
    For j = 1 To Form56.MSHFlexGrid1.Rows - 1
        
        If (Len(Form56.MSHFlexGrid1.TextMatrix(j, 4)) <> 0) And (InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0) Then
            If lngCount < 10 Then
                Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & Form56.MSHFlexGrid1.TextMatrix(j, 4) & "'"
                lngCount = lngCount + 1
            End If
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
        Else
             Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
        End If

    Next
Next

MsgBox "Done"

Open in new window

0
 
Wilder1626Author Commented:
Oh, i think it's working
i=2           j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'

Open in new window


Let me try with more data.
0
 
aikimarkCommented:
It is also possible to use the Like operator as well as the Instr() function.
0
 
Wilder1626Author Commented:
humm

I have now tried with about 1000 rows of data, and no match when i should have.

Let me test again

what is the like operator?
0
 
Wilder1626Author Commented:
I think that after there is 1 match, it put all others at no match.
0
 
aikimarkCommented:
the Like operator is a pattern matching operator in the VB language
0
 
Wilder1626Author Commented:
Ok. Thanks.

Let me test some more a huge file. For now, it should find something but it don't.

I'm just trying to see why.
0
 
aikimarkCommented:
From a performance perspective, this version of the search code will be much faster.  It doesn't even start the inner loop if the search criteria is an empty string.
Dim i As Integer
Dim j As Integer

Dim strSearchFor As String
Dim lngCount As Long

For j = 1 To Form55.MSHFlexGrid2.Rows - 1
  strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
  If (Len(strSearchFor) <> 0) Then
    For i = 1 To Form56.MSHFlexGrid1.Rows - 1
        If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), strSearchFor, vbTextCompare) <> 0 Then
            If lngCount < 10 Then
                Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & strSearchFor & "'"
                lngCount = lngCount + 1
            End If
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
        Else
             Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
        End If

    Next i

  End If

Next j

MsgBox "Done"

Open in new window

0
 
Wilder1626Author Commented:
Hi

I tried it but i have a run time error 381 subsript out of range on:

strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)

Open in new window

0
 
Wilder1626Author Commented:
I did 2 modification .
Dim i As Integer
Dim j As Integer


Dim strSearchFor As String
Dim lngCount As Long

For j = 1 To Form56.MSHFlexGrid1.Rows - 1
  strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
  If (Len(strSearchFor) <> 0) Then
    For i = 1 To Form55.MSHFlexGrid2.Rows - 1
        If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), strSearchFor, vbTextCompare) <> 0 Then
            If lngCount < 10 Then
                Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & strSearchFor & "'"
                lngCount = lngCount + 1
            End If
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
        
        Else
             'Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
        End If

    Next i

  End If

Next j

MsgBox "Done"

Open in new window



The result i have now is:

i=1           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=3           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST1'   Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=40          j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=2           j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=30          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: 00008987989M/456789'   Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=31          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='  BOL: 00008987989M'           Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=32          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='BOL: 00008987989M/ooooo'       Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=33          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'

Open in new window

0
 
Wilder1626Author Commented:
Thank you so much for all the help.

It's very fast and all work great.

Thanks again
0
 
aikimarkCommented:
glad it works.  thanks for the points.
Sorry about the flexgrid name error.

What percentage of your Form56.MSHFlexGrid1 (searchfor) cells were zero-length strings?

Note: You do not need the Else clause.
0
 
Wilder1626Author Commented:
Hi aikimark

Thanks again, i'm doing many many test right now and all looks good so far.

Actually, i don't need the else clause. Just if it MATCH.

For your question about:
What percentage of your Form56.MSHFlexGrid1 (searchfor) cells were zero-length strings?

None. I have value on every row until the last record of the sheet.
0
 
aikimarkCommented:
how many rows are in your flexgrids?

If a lot, I would suggest copying the Form56.MSHFlexGrid1 column 39 strings (search what) to a string vector (single dimension array) with the same row dimensions (lbound & ubound) as your TexMatrix rows.  Making form and control references can be a performance killer.

My 'performance' tweak doesn't help much if there are aren't any zero-length searchfor strings.

Be sure to add an Exit For just below your MATCH code, unless there can be duplicate matches.

>>Actually, i don't need the else clause. Just if it MATCH.
That's what I wrote.
0
 
Wilder1626Author Commented:
Oh,

The rows will increase with the time.
Well, everyday, it will increase in Form56. Now, i'm at 15000 rows
In form 55, it will vary everyday. Could be less or could be more. an average would be around 58 to  1000.

Now  for the match, i can have more than 1 match. Not to many time but once in a while.
So i guess i should not use the EXIT FOR.
Right?

Do you think i will be good like that?
0
 
aikimarkCommented:
with that data description, I recommend the following.
* Go back to the original iteration of the Form55 iteration in the outer loop
* Copy the Form56 column into a string array as I described earlier
* Do use an Exit For statement.
   It doesn't matter if there are multiple matches in the Form56 column, you are only indicating a match in the Form55 grid, which a first/only instance of a match

Example:
Dim i As Long
Dim j As Long

Dim SearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid2.Rows - 1
Redim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
    SearchFor(j) = Form56.MSHFlexGrid2.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
  strSearchWhat = Form55.MSHFlexGrid1.TextMatrix(i, 39)
  If (Len(strSearchWhat) <> 0) Then
    For j = 1 To lngF56Limit
        If (Len(strSearchFor(j) <>0) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
            Exit For
        End If
       
    Next j
  End If

Next i

MsgBox "Done"

Open in new window


Note: I've changed the data type of your iteration variables to Long.
0
 
Wilder1626Author Commented:
HI,  Ok i have tried this code, but with some modification but i have a compile error:



       If (Len(SearchFor(j) <> 0)) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then 

Open in new window


Full code

Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid1.Rows - 1
ReDim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
    SearchFor(j) = Form56.MSHFlexGrid1.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
  strSearchWhat = Form55.MSHFlexGrid2.TextMatrix(i, 39)
  If (Len(strSearchWhat) <> 0) Then
    For j = 1 To lngF56Limit
        If (Len(SearchFor(j) <> 0)) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
            Exit For
        End If
       
    Next j
  End If

Next i

MsgBox "Done"

Open in new window

0
 
Wilder1626Author Commented:
It says variable required with <>.

Strange.
0
 
aikimarkCommented:
I don't see some of the variables defined in the "Full code"
Always run with explicit variable declaration compile options.

I mixed the variable names for my new string variables; corrected below.
Option Explicit


Dim i As Long
Dim j As Long

Dim strSearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid2.Rows - 1
Redim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
    strSearchFor(j) = Form56.MSHFlexGrid2.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
  strSearchWhat = Form55.MSHFlexGrid1.TextMatrix(i, 39)
  If (Len(strSearchWhat) <> 0) Then
    For j = 1 To lngF56Limit
        If (Len(strSearchFor(j) <>0) And (InStr(1, strSearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
            Exit For
        End If
       
    Next j
  End If

Next i

MsgBox "Done"

Open in new window

0
 
Wilder1626Author Commented:
Like this it work, and much more faster also

Dim i As Long
Dim j As Long

Dim SearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid1.Rows - 1
ReDim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
    SearchFor(j) = Form56.MSHFlexGrid1.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
  strSearchWhat = Form55.MSHFlexGrid2.TextMatrix(i, 39)
  If (Len(strSearchWhat) <> 0) Then
    For j = 1 To lngF56Limit
        If (Len(SearchFor(j)) <> 0) And (InStr(1, strSearchWhat, SearchFor(j), vbTextCompare) <> 0) Then
            Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
            Form55.MSHFlexGrid2.Row = i
            Form55.MSHFlexGrid2.Col = 38
            Form55.MSHFlexGrid2.CellBackColor = &H80FF&
            Exit For
        End If
       
    Next j
  End If

Next i


MsgBox "Done"

Open in new window

0
 
aikimarkCommented:
did we just cross-post?
0
 
Wilder1626Author Commented:
lol, i think so.

I really like your last option.

So fast compare to the previous code.

i don't know how to thanks you for all your help.

I did learned a lot with you on this post.
0
 
aikimarkCommented:
Have you measured the performance difference between the iteration of Form56 in the original code in the inner For loop and the current iteration of the strSearchFor array?

If your Form56 or Form55 column grows significantly, you can consider a different approach to this matching process.  In such a scenario, you would parse the Form55 string to extract the substrings that relate to the BOL numbers.  For each BOL number, you would do a hashed lookup in a dictionary object that contains the unique values from the Form55 list.
0
 
Wilder1626Author Commented:
From what i know, the form 55 would always have between 58 to 1000 max records. 85 % of the time, it would not go higher than 500.

But for the form56, at the beginning of the year, it would start at 1 but at the end of the year, i could have more than 20000 records.

Does it mean that i should search each BOL from Form55 to match the same BOL in form56 than the opposite?
0
 
aikimarkCommented:
But for the form56, at the beginning of the year, it would start at 1 but at the end of the year, i could have more than 20000 records.

Does it mean that i should search each BOL from Form55 to match the same BOL in form56 than the opposite?

Even though the Form55 list will have more items at the start of the year, you will be better off making Form55 the outer search, since the Form56 list will be an order of magnitude larger by the end of the year.

I think that is how our currently written code snippets process these two lists. The reason why I posted my prior comment was that our use of Instr() for not-exact matching isn't very efficient.

If I applied the "(\d+\w)" regexp pattern to the Form55 items, I could then try to do an exact match on the Form56 items.  If the Form56 items were in a dictionary object, we can do a hashed look-up by invoking the dictionary object's .Exists() method.
Note: the regexp pattern is based on your example
              BOL: 00008987989M  / 00008985559M string
"(^|\s)(.{12})(\s|$)" would also parse your sample string.
I do not have enough samples to recommend a parsing strategy or regexp pattern.

An alternative search technique would be to sort the Form56 list and perform a binary search or build a b-tree.
0
 
Wilder1626Author Commented:
I think we are good now. No need to try to find another solution.

we are in September and i am only at 13000 rows in form56, and it is pretty fast when i match both grid.

Thanks again for all your time and help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 20
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now