Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-02-2013, 02:28 PM
silverspr silverspr is offline Search with multiple strings Windows 8 Search with multiple strings Office 2010 64bit
Novice
Search with multiple strings
 
Join Date: Apr 2011
Posts: 24
silverspr is on a distinguished road
Default Search with multiple strings

Hello, I have a search list called categories with each item in the list having a rank:
A2: employee safety
A3: end of life
A4: life
A5: lifespan
A6: patient safety
A7: quality

B2:3
B3:1
B4:4
B5:4
B6:3
B7:2



I am using this formula to return the value found in the string being searched "End of life span",
{=INDEX($A$2:$A$7,MAX(IF(ISERROR(SEARCH($A$2:$A$7, S11)),-1,1)*(ROW($A$2:$A$7)-ROW($A$2)+1)))}

The formula returns "life" but I want it to return "End of life", I tried using min in the formula, but to no avail. I need this to use Vlookup to find the rank of the item found, as the "ranks" will be tallied and scored.
Any help is appreciated.
Reply With Quote
  #2  
Old 03-02-2013, 02:53 PM
macropod's Avatar
macropod macropod is offline Search with multiple strings Windows 7 64bit Search with multiple strings Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Try:
=OFFSET($A$2,MATCH(S11,$A$2:$A$7,0)-1,0)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 03-02-2013, 03:45 PM
silverspr silverspr is offline Search with multiple strings Windows 8 Search with multiple strings Office 2010 64bit
Novice
Search with multiple strings
 
Join Date: Apr 2011
Posts: 24
silverspr is on a distinguished road
Default

HI
thanks for the new idea, works on a sorted list w/the [match_type] left off. You'd think an exact match would work!

And I realized afterward, I can use the index function to find the rank negating the need for the vlookup.

d
Reply With Quote
  #4  
Old 03-02-2013, 08:06 PM
macropod's Avatar
macropod macropod is offline Search with multiple strings Windows 7 64bit Search with multiple strings Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The MATCH function works regardless of whether the list is sorted. The match type = 0 prevents false matches. And, if you want the rank that corresponds with a given category, simply use:
=OFFSET($A$2,MATCH(S11,$A$2:$A$7,0)-1,1)
You can use the same logic to find which category corresponds with a given rank. For example, with a given rank in S12:
=OFFSET($A$2,MATCH(S12,$B$2:$B$7,0)-1,0)
Of course, where you have multiple values with the same rank, this only reports the first one. The formulae for finding the second & subsequent instances of the same rank are rather more complicated.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 03-03-2013, 08:21 AM
silverspr silverspr is offline Search with multiple strings Windows 8 Search with multiple strings Office 2010 64bit
Novice
Search with multiple strings
 
Join Date: Apr 2011
Posts: 24
silverspr is on a distinguished road
Default

Thanks again for taking the time to reply. This has been an interesting challenge. I've attached a worksheet with both formulas as you've suggested. The intent of this worksheet is to score each of the highlighted columns using this formula. As you can see it is not working as planned, there must be something I'm not doing right or something I've missed telling you as it does on paper seem fairly straight forward. I think what I missed telling you is I am searching for a string within a string which Match may not be able to do? The search string would contain more than just the item being searched for.

thanks again
d

Last edited by silverspr; 03-03-2013 at 03:03 PM.
Reply With Quote
  #6  
Old 03-03-2013, 12:43 PM
macropod's Avatar
macropod macropod is offline Search with multiple strings Windows 7 64bit Search with multiple strings Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The simple answer is that what you've input into S11, for example, doesn't match any of the entries in A2-A7. In S11, you have 'End of Life span', but that string doesn't exist in A2-A7.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 03-03-2013, 03:02 PM
silverspr silverspr is offline Search with multiple strings Windows 8 Search with multiple strings Office 2010 64bit
Novice
Search with multiple strings
 
Join Date: Apr 2011
Posts: 24
silverspr is on a distinguished road
Default

Thanks again
I thought as much, then the search function is a better option? Which brings me back to my original problem, depending on how the list is sorted the function returns "end of life" or "life" with the preferred option being "end of life".

thanks
I will have to muddle through this...
Reply With Quote
  #8  
Old 03-03-2013, 04:45 PM
macropod's Avatar
macropod macropod is offline Search with multiple strings Windows 7 64bit Search with multiple strings Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The problem with that approach is that you're likely to end up with false matches.

By trying to match an array that only contains sub-string(s) of the strings you're trying to match on, you are, in effect, asking the array to search the search string before it can tell you which search returns a match. Without a lot of work, that approach is only ever going to return the first of any possible number of matches.

To do that, it's probably best to employ a User-Defined Function, such as the following, which you can add to an ordinary code module in the workbook:
Code:
Function MatchSubString(StrVal As Variant, Arr As Range, Optional MatchCase As Variant) As Variant
Dim i As Long, j As Long, k As Long, l As Long
Dim StrArr As String, StrItm As String, Rng As Range
For Each Rng In Arr.Cells
  StrArr = StrArr & Rng.Text & ","
Next
If MatchCase = 1 Then
  MatchCase = vbBinaryCompare
Else
  MatchCase = vbTextCompare
End If
MatchSubString = "N/A"
For i = 0 To UBound(Split(StrArr, ","))
  StrItm = Split(StrArr, ",")(i)
  If InStr(StrItm, StrVal) > 0 Then
    MatchSubString = i + 1
    Exit Function
  ElseIf Len(StrVal) > Len(StrItm) Then
    For j = 1 To Len(StrVal)
      If InStr(1, StrItm, Left(StrVal, j), MatchCase) > 0 Then
        If j > k Then
          k = j
          MatchSubString = i + 1
        End If
      End If
    Next
  End If
Next
End Function
Then you can use the function just as you would any other formula. The MatchSubString function takes three arguments: the string or range of the string to match; the range of the arrary to match agains; and, optionally, whether the match should be case-sensitive. For example:
=MatchSubString(S11,$A$2:$A$7,0) or =MatchSubString(S11,$A$2:$A$7,0) would return '2' for "End of life span" in S11.
You can nest the function in other Excel functions, too:
=OFFSET($A$2,MatchSubString(S11,$A$2:$A$7,0)-1,1)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
multiple strings, search

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
search on multiple word documents Guy Roth Word 7 03-06-2017 01:31 PM
Search with multiple strings Find, aka search aka ctrl+pgdn across multiple lines snarfii Word 1 04-22-2012 09:56 PM
Search with multiple strings Autotext strings are being stripped of their format when called from VBA JEilerts Word VBA 2 04-21-2012 07:49 AM
Search with multiple strings Removing Character Strings digitalhecht Word 2 10-17-2011 12:53 PM
Search with multiple strings update style of all strings available between two specific strings vikrantkale Word 1 03-28-2011 06:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft