| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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|CSHOA F|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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparison - Strings and Arrays
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|CSHOA F|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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparison - Strings and Arrays
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|CSHOA F|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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparison - Strings and Arrays
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|CSHOA F|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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Routine with fuzzy logic to determine the relative comparison of two strings? | Excel Worksheet Functions | |||
| How do I compare two arrays of numeric strings? | Excel Programming | |||
| Help - Type mismatch when running loop with strings from arrays | Excel Programming | |||
| Arrays and Strings | Excel Programming | |||
| Arrays and Strings | Excel Programming | |||
Linear Mode
