Comparison - Strings and Arrays

[Guest]

I'm posting this again in the hopes that someone has an answer. I am not
having any luck with the 'Find' or 'Search' functions.

I am wanting to take a delimited string in a cell (B2) containing multiple
data elements and compare it to an array ($AA$2:$AA$10), then have the
matching data from the cell trimmed and displayed in a neighboring cell (C2).
I believe the best result would be for it to return the value(s) that match
one or more of the data elements in $AA$2:$AA$10.

From the example below, if I could have B2 contain the string, C2 would
contain the function that gives me the result of the match. For instance,
$AA$2:$AA$10 contains:

ACLEMON
BDUFOUR
BLANGLI
DDILUCE
DWELLS
ESCOTT
KRENKER
THUMENI
WROCHES

If B2 contains:

MROMANO|MTELLIG|JGURECK|MWOLK|LGAZDA|GVANBUH|CSHOAF|STILLER|RVERBIC|DSTEPHE|BLANGLI

C2 should return a result of BLANGLI. If there is more than one piece of
matching data, then it would be great if C2 could display the multiple items
in a new pipe-delimited string. Then, I want to do this for the remaining
cells (B3 to B146) in a successive list. Any advice on accomplishing this is
GREATLY appreciated. Thanks in advance!

Jeff Bloomer
Business/Reporting Analyst
Standard Register
www.standardregister.com

 

[Guest]

Try this UDF

in C2 put =findstrings(B2) and copy down

Function findStrings(ByVal srchstr As String)
Set comprng = Range("aa2:aa10")
findstr = ""
For i = 2 To 10
n = InStr(1, srchstr, comprng(i), vbTextCompare)
If n <> 0 Then findstr = findstr & Mid(srchstr, n, Len(comprng(i))) & "|"
Next i
If findstr <> "" Then
findStrings = Left(findstr, Len(findstr) - 1)
Else
findStrings = ""
End If
End Function

 

[Guest]

Sorry

For i = 2 To 10

should be

For i = 1 To 9

 

[Guest]

I'm getting an error when I run it right now. Here's the exact function I
entered:

Function findStrings(ByVal srchstr As String)
Set comprng = Range("ab2:ab54") <-- not a mistake, I had to change my
range by a column
findstr = ""
For i = 1 To 53
n = InStr(1, srchstr, comprng(i), vbTextCompare)
If n < 0 Then
findstr = findstr & Mid(srchstr, n, Len(comprng(i))) & "|"
Next i
If findstr < "" Then
findStrings = Left(findstr, Len(findstr) - 1)
Else
findStrings = ""
End If
End Function

The error I get is "Compile Error: Next without For". Is that because the
"For" statement is outside the "If" statement? I'm a little rusty on my VB
syntax, but I'm thinking that's what's causing the problem. Please advise,
and I really appreciate the help!