excel vba regex pattern exact string match

Hi I'm trying to fix my code to do the following (with some context)

There are 2 columns in excel: column P and column S. Both columns are thousands and thousands of rows long.

Column P are all multi-lined text strings (descriptions of products)
Column S are all multi-lined text strings (comments of products)

I need to write a vba function that will look into the cell in column P and return the exact string match if there is a match pertaining to the values in column S.

Example:

Using regular expressions I have been able to do this comparing one row at a time (P3 to S3) using the code below:

Public Function RxMatch( _
ByVal SourceString As String, _
ByVal Pattern As String, _
Seperator As String, _
Optional ByVal IgnoreCase As Boolean = True, _
Optional ByVal MultiLine As Boolean = True) As Variant

Dim arrWords() As String
arrWords = Split(SourceString, separator)

Dim oMatches As MatchCollection
For Each word In arrWords
With New RegExp
.MultiLine = MultiLine
.IgnoreCase = IgnoreCase
.Global = False
.Pattern = Pattern
Set oMatches = .Execute(SourceString)
If oMatches.Count > 0 Then
RxMatch = oMatches(0).Value
Else
RxMatch = "No match"
End If
End With
Next word

End Function


however instead of comparing P3 to S3 for a match I need to compare P3 to all of column S to see if any of the descriptions have a match. Is there a way to update this code that I provided so that it matches off of the entire column S instead of cell to cell?

foreach

1 Answer
1

If you are careful about any hidden characters, line breaks etc then you should be able to use arrays and Instr function.

Option Explicit
Public Sub FindMatches()
Dim arr(), i As Long, j As Long
With ActiveSheet
arr = .Range("P1:T" & .Cells(.Rows.Count, "P").End(xlUp).Row).Value
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 1) To UBound(arr, 1)
If InStr(arr(i, 1), arr(j, 4)) > 0 Then arr(i, 5) = arr(i, 5) & "," & arr(j, 4)
Next j
Next i
For i = LBound(arr, 1) To UBound(arr, 1)
arr(i, 5) = Application.WorksheetFunction.Substitute(arr(i, 5), ",", vbNullString, 1)
Next i
.Range("P1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End With
End Sub


Dataset with output in column T:

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.