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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com