Pfthb

I'm trying to make a Document Search Engine that uses keywords and the document name when searching for a file.. in an excel spreadsheet.

It works by breaking down the search term and the name of the file via spaces, as well as the keywords via commas.
Then it matches each search term for every keyword, and adds a counter for each match. More keyword matches = higher counter, then I sort the table via this counter, so I could get the best-matching result at the top.

The search functionality is already working fine, however when it comes to singular and plural terms, it's not finding it.

Let's say that the keyword for doc1 is Templates. When searching for the word Template in the search field, it fails to match. I'm using a LIKE statement, and I tried adding a wildcard at the front and the end, but it still does not work.

Strangely enough, the opposite works. If I search for a plural term, and the keyword is singular, it gets a match. I have no idea why, it just works which is a good thing.

Sub Searchresult()
Dim x As Long, y As Long, count As Long
Dim search() As String, keyword() As String, names() As String
Dim result As String
Dim tbl As ListObject, sortcol As Range, lrow As Long

 With Worksheets("Sheet3") 'Prep for placing results in table.
 Set tbl = .ListObjects("tblSearch")
 Set sortcol = .Range("tblSearch[sort]")
 tbl.DataBodyRange.ClearContents
 End With

 With Worksheets("Sheet2")
 search = Split(.Range("F1").Value, " ") 'split search terms via spaces
 For x = 2 To 1000 Step 1
 count = 0
 lrow = Worksheets("Sheet3").Cells(Rows.count, 1).End(xlUp).Row + 1
 keyword() = Split(.Range("d" & x), ",") ' split keywords via comma
 names() = Split(Replace(Replace(Replace(Replace(.Range("c" & x), "-", ""), "(", ""), ")", ""), "'", ""), " ") 'splits names via spaces, deleting any unwanted characters

 For i = LBound(keyword) To UBound(keyword)
 For j = LBound(search) To UBound(search)

 If "*" & UCase(search(j)) & "*" Like "*" & UCase(keyword(i)) & "*" Then 'compare search term and keyword
 Worksheets("Sheet3").Range("A" & lrow, "B" & lrow).Value = .Range("A" & x, "B" & x).Value
 count = count + 1
 Worksheets("Sheet3").Range("C" & lrow).Value = count
 End If

 Next
 Next

 For k = LBound(names) To UBound(names)
 For l = LBound(search) To UBound(search)
 If "*" & UCase(search(l)) & "*" Like "*" & UCase(names(k)) & "*" And Len(names(k)) > 2 Then 'compare search term and document name
 Worksheets("Sheet3").Range("A" & lrow, "B" & lrow).Value = .Range("A" & x, "B" & x).Value
 count = count + 1
 Worksheets("Sheet3").Range("C" & lrow).Value = count
 End If
 Next
 Next
 Next


 End With

 With tbl.Sort 'sort everything based on count to get best result
 .SortFields.Clear
 .SortFields.Add Key:=sortcol, SortOn:=xlSortOnValues, Order:=xlDescending
 .Header = xlYes
 .Apply
 End With


End Sub

How do I make sure that the LIKE statement can match both singular/plural terms of a word? It doesn't have to be too complex that it can find words that change when it's singular/plural (Ex:"copy/copies"), however having it match something that just adds an 's' would do.

EDIT: Thanks to Brian's explanation, the code is fixed by adding an OR statement that flips the Like statement so that it compares the terms again. It's now able to match even though it's singular/plural.

 If UCase(search(j)) Like "*" & UCase(keyword(i)) & "*" Or UCase(keyword(i)) Like UCase(search(j)) & "*" Then 'compare search term and keyword

 If (UCase(search(l)) & "*" Like UCase(names(k)) & "*" Or UCase(names(k)) Like UCase(search(l)) & "*") And Len(names(k)) > 2 Then 

In your example code, the 'keyword' needs to be singular and then it will match both singular and plural (as you have seen). Also, you don't need wildcards on the 'search' term.

"TEMPLATE" Like "*" & "TEMPLATE" & "*" 'returns True
"TEMPLATES" Like "*" & "TEMPLATE" & "*" 'returns True

Think of it this way. In both the above cases, we are in essence saying "does the first term CONTAIN the second term.