Excel - MATCH function - multiple search results

Question

I am trying to create an excel tool which would search for specific key words located in a separate sheet and display results (full sentences/paragraphs including those key words). As of now I could only manage to display only the first finding. What I would like to do is to display all findings below each other.

The functions I am using are: =IFERROR((INDEX(Tabulka!A:A;POZVYHLEDAT(CONCATENATE("*";FINDER!C4;"*";FINDER!C5;"*";FINDER!C6;"*";FINDER!C7;"*";FINDER!C8;"*");Tabulka!A:A;0)));"N/A")

where Tabulka is a sheet where the table with sentences/paragraphs is located and FINDER is a sheet where I define the key words and desplay search results.

There can be more sentences in Tabulka sheet including the same defined key words in FINDER sheet. I want the function to display them all below each other not only the first one as it does now.

Is it possible to solve this issue somehow with and/or without VBA?

Thanks

J.

Answer 1

Here is an example that uses a Regular Expression. The search pattern is build from the words in column C. The results are shown in columns A and B. Column A has the cell reference.

    Sub SEARCH()

        Const COL_WORDS As String = "C"
        Const COL_RESULTS As String = "B"
        Const SHT_RESULTS As String = "FINDER"
        Const SHT_SEARCH As String = "Tabulka"

        Dim wb As Workbook, wsToSearch As Worksheet, wsResults As Worksheet
        Dim t0 As Single
        t0 = Timer

        Set wb = ThisWorkbook
        Set wsToSearch = wb.Sheets(SHT_SEARCH)
        Set wsResults = wb.Sheets(SHT_RESULTS)

        ' build array of key words without blanks
        Dim i As Integer, irow As Long, iLastRow As Long
        Dim words() As String, cell As Range

        ' last row of keywords
        iLastRow = wsResults.Range(COL_WORDS & Rows.count).End(xlUp).Row

        ReDim words(iLastRow)
        i = 0
        For Each cell In wsResults.Range(COL_WORDS & "1:" & COL_WORDS & iLastRow)
          If Len(cell) > 0 Then
            words(i) = cell.Value
            i = i + 1
          End If
        Next
        ReDim Preserve words(i - 1)

        ' build regex engine
        Dim regex As Object, sPattern As String
        sPattern = Join(words, "|")
        Debug.Print sPattern

        Set regex = CreateObject("vbscript.regexp")
        With regex
          .Global = True
          .MultiLine = True
          .Pattern = sPattern
          .IgnoreCase = True
        End With

        ' scan sheet and record results under keywords
        Dim count As Integer, matches As Integer
        count = 0: matches = 0
        irow = iLastRow + 2
        For Each cell In wsToSearch.UsedRange
           If Len(cell.Value) > 0 Then
             count = count + 1
             If regex.test(cell.Value) Then
               wsResults.Cells(irow, 1) = cell.Address
               cell.Copy wsResults.Range(COL_RESULTS & irow)
               irow = irow + 1
               matches = matches + 1
             End If
           End If
        Next

        ' format results sheet
        wsResults.Columns(COL_RESULTS).ColumnWidth = 100

         ' performance stats
        Dim sMsg As String
        sMsg = count & " cells scanned and " & matches & " matches found " _
        & " in " & Int(Timer - t0) & " seconds."
        MsgBox sMsg, vbInformation, "Result"

    End Sub