Comparison - Strings and Arrays

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

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


"jbtenor1" wrote:

> 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

Sorry

For i = 2 To 10

should be

For i = 1 To 9



"Toppers" wrote:

> 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
>
>
> "jbtenor1" wrote:
>
> > 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

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!

"Toppers" wrote:

> Sorry
>
> For i = 2 To 10
>
> should be
>
> For i = 1 To 9
>
>
>
> "Toppers" wrote:
>
> > 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
> >
> >
> > "jbtenor1" wrote:
> >
> > > 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