Index Match a col of long text stings to find short text strings and return adjacent

[RINCONPAUL]

In the attachment I need a function to reside in cells C1:C9, that looks at long text string in adjacent col D for words/numbers that match any in list of text/numbers within col A. If TRUE return the adjacent value in col B.

Example: Formula in cell C2 looks at text string in D2 for a match from Col A:A. It finds a match with 'MDN' and 'Maiden' and returns value '20'

Thanks

[dxider]

Hi man.
I came with the following code on VBA.

Public Function CheckValues(text As String, range As range)
Dim fila As Integer
fila = 1
Dim regEx As New RegExp
Dim strPattern As String
Dim returns As String
While range(fila, 1).FormulaR1C1 <> ""
    With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "(\w\s)*" & Change(range(fila, 1).FormulaR1C1) & "(\w\s)*"
    End With
    If regEx.Test(text) Then
        If returns = "" Then
            returns = range(fila, 1).Offset(0, 1).FormulaR1C1
        Else
            returns = returns & ", " & range(fila, 1).Offset(0, 1).FormulaR1C1
        End If
        
    End If
    
    fila = fila + 1
Wend
CheckValues = returns
End Function


Public Function Change(entrada As String) As String
entrada = Replace(entrada, "+", "\+")
entrada = Replace(entrada, "?", "[0-9]+")


Change = entrada
End Function

And to call it, you just have to use this on the C column:

=CheckValues(D2,$A$2:$A$52)

The only problem I see is with the string "Restricted" as it is contained in all the strings with Restricted ??
To solve this, is better that you put more values on the list in A (explode 6+ to something like 61,62, 63 and so on)

I used regular expression for Excel, so in order to work with this code, you have to enter in the VBA editor, and in the Tools menu, select "References" then add the reference to "Microsoft VBScript Regular Expressions 5.5".
Another thing that can be improved, is to use an array to save the matches and when a new match is found, check if the value has not been already added.

Check the attached picture for details:
regexp.jpg

[RINCONPAUL]

Hi dxider

Having issues with getting this to run? #NAME? error in C:C. As for the "Restricted" class, there were 740 old records in the spreadsheet and I'll modify them to "Restricted X", as going forward that class shouldn't occur again. Trouble is when I edited the cell entry, the code popped up with the 4th line regEx As New RegExp highlighted and I'm in a viscous loop in that I can't close the vba box to re-edit or whatever and get Compile Error?? I can't even close the program now aahh!!That's the nature of vba and why I hate it, but that's only because I don't know what I'm doing, and deserve what I get LOL

[dxider]

I see.

Well, my suggestiong was because with Regular Expressions, the sign ? and + have a special meaning.
Maybe if you have the complete list I can help you to choose the correct approach for the problem.

And, regarding the ?, what do you mean with that symbol?

Regarding the #NAME, this is because Excel wasn´t able to find the functions on the code. You have to add a Module and paste the defined functions on that part.

To stop that loop, you can press Ctrl + Pause key, it will stop the execution of the Macro.

[RINCONPAUL]

Hi dxider,

I started afresh and got it working now. It might have been the sequence of how I did things before. Yes I could remove the Restricted 6? and enter numbers, makes the list longer but does that really solve anything? Like if the code is matching Restricted and Restricted ??, it won't matter whether Restricted ?? is shown in Col A as Restricted 6? or Restricted 67, it'll still pick up two different classes: Restricted and Restricted 6?/7.

It's an innovative solution nonetheless and I appreciate your help.

Thankyou

[dxider]

Well, it was because when you use regular expressions, you have to declare a matching string, and when you apply that string to an input value, the regexp object tries to look if the input matches the defined string.
It's kind of complicated to explain right now, but, for example, the + sign is used on regular expressions to say "the previous character can appear one or any times consecutively".

But in this case, I used a matching sequence before and after the values from column A. If you see my code, on the pattern I declare something like this: "(\w\s)*". The * is used to say, "match the values between parenthesis 0 or any times", the values between parenthesis are any letter and any spacing character, so that part matches anything before the expresion. The same is used to finish the sequence "(\w\s)*".

In your case, with the "Restricted" string, it will look something like .Pattern = "(\w\s)*Restricted(\w\s)*", and that means: Match any string that contains "Restricted" with any values before or after, that was the reason that it matches even Restricted67 or anything with Restricted on it.

Hope I didn't confuse you more

And to avoid entering every Restricted?, Restricted?? or Restricted???, you can define only 3 rows with the following:

Restricted\d
Restricted\d\d
Restricted\d\d\d


or just one row with:
Restricted(\d){1,3}

\d matches any digit, and {1,3} tells the Regexp to match from 1 to 3 ocurrences of the previous expression (\d).

Looks confusing, huh??

[RINCONPAUL]

Lost me but it's the reason why I come to this forum....just chockabloc with very talented people.

Thanks again

[p45cal]

This array-entered formula in C2 then copied down gives the same results as you wanted, but that may be just luck. See attached file.
=MAX(IF(ISERROR(SEARCH($A$2:$A$52,D2)),FALSE,$B$2:$B$52))
(Array-Entering is pressing Ctrl+Shift+Enter rather than just Enter.)

[RINCONPAUL]

Wow!!..how easy was that....but I couldn't get it!! That was the solution I was looking for. Well done as always Pascal