Need a quick search method in Excel VBA

Question

Consider the scenario, I have 2 columns (column "A" and column "B").

Column A has about 130,000 rows / rows Column B contains about 10,000 rows / rows

I would like to search each row of column "B" from column "A".

As you can see, the amount of data is very high. I have already tried using the Range.Find () method. But it takes a lot of time. I am looking for a method / method that will give me results in a very short time.

* A few more clarifications on my request *

(1) Columns A and B contain string values, NOT NUMBERS. And the string can be very large

(2) For each cell in column "B" there may be many cases in column "A"

(3) I would like to get all occurrence of column “B” in column “A” with row number

(4) For the row present in column "B". It can be found as a substring of any cell in column "A"

---

Download file link - wikisend.com/download/431054/StackOverFlow_Sample.xlsx *

Any suggestions?

Do not hesitate, you need more information to solve the above problem!

Answer 1

Try it.

For 130000 rows in Col Aand 10000 rows in Col Bit took 3seconds. The output is generated at Col C.

NOTE . I accepted a scenario worstwhere all values 10000in Col B are present in Col A

This is what my data looks like.

Sub Sample()
    Debug.Print Now

    Dim col As New Collection
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Application.ScreenUpdating = False

    With ws
        .Range("C1:C10000").Value = "No"

        For i = 1 To 130000
            On Error Resume Next
            col.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
            On Error GoTo 0
        Next i

        On Error Resume Next
        For i = 1 To 10000
            col.Add .Range("B" & i).Value, CStr(.Range("B" & i).Value)
            If Err.Number <> 0 Then .Range("C" & i).Value = "Yes"
            Err.Clear
        Next i
    End With

    Application.ScreenUpdating = True

    Debug.Print Now
End Sub

Answer 2

NEW A 130000 100- , B 10000 30- , 27 .

C B. D B.

Public Sub searchcells()
    Dim arrA(1 To 130000) As String, arrB(1 To 10000) As String, t As Date, nLen As Integer
    t = Now
    Me.Range("c:d") = ""

    For i = 1 To 130000
        arrA(i) = Me.Cells(i, 1)
    Next
    For i = 1 To 10000
        arrB(i) = Me.Cells(i, 2)
    Next

    For i = 1 To 130000
        nLen = Len(arrA(i))
        For j = 1 To 10000
            If InStrRev(arrA(i), arrB(j), nLen - Len(arrB(j)) + 1) > 0 Then Me.Cells(j, 4) = Me.Cells(j, 4) + 1: Me.Cells(j, 3) = Me.Cells(j, 3) & i & "; "
        Next
        Me.Cells(1, 5) = i
    Next

    Debug.Print CDbl(Now - t) * 24 * 3600 & " seconds"
End Sub

, j .

Public Sub fillcells()
    Dim temp As String
    Randomize

    For i = 1 To 13000
        temp = ""
        For j = 1 To 100
            temp = temp & Chr(70 + Int(10 * Rnd()))
        Next
        Me.Cells(i, 1) = temp
    Next
    For i = 1 To 10000
        temp = ""
        For j = 1 To 30
            temp = temp & Chr(70 + Int(10 * Rnd()))
        Next
        Me.Cells(i, 2) = temp
    Next
End Sub

, , .