***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[Jonmo1]

Re: June/July 2008 Challenge of the Month

OK, last one I promise...

After further consideration, I changed a rule in the UDF.
With the Nth option, it was set to use the Last Match if Nth was larger then the # of Matches Found. In other words, in this sentence..
The blue ocean has a red boat in it.

If you put 3 or higher as the Nth, it would have used red. Even though red was only the 2nd color in the sentence.

I now think that would probably be a bad idea, it could be misleading to think there is a 3rd color in the sentence when there actually isn't....so I changed it to return blank in that case instead...

Public Function Jonmo1(C As Range, L As Range, Optional Nth As Long = 1, _
Optional Fuzzy As Boolean = False) As String
Dim MyArray As Variant
Dim Pos() As Variant
Dim Word() As Variant
Dim MyRange As Range
Dim r As Range
Dim counter As Long, X As Long, Y
Set MyRange = Range(L(1, 1), L(L.Rows.Count, 1).End(xlUp).Offset(0, L.Columns.Count - 1))
If Fuzzy = False Then
    MyArray = Split(Replace(C, Chr(160), ""))
    For X = LBound(MyArray) To UBound(MyArray)
        Y = Application.Match(MyArray(X), MyRange.Columns(1), 0)
        If Not IsError(Y) Then
            counter = counter + 1
            If counter = Nth Then
                Jonmo1 = MyRange(Y, 2)
                Exit For
            End If
        End If
    Next X
Else
    For Each r In Range(MyRange.Columns(1).Address)
        X = InStr(1, C, r)
        If X > 0 Then
            counter = counter + 1
            ReDim Preserve Pos(1 To counter)
            ReDim Preserve Word(1 To counter)
            Pos(counter) = X
            Word(counter) = r
        End If
    Next r
    If counter < 1 Then Exit Function
    If Nth > UBound(Pos) Then Exit Function
    X = Application.Small(Pos, Nth)
    Jonmo1 = Application.VLookup(Word(Application.Match(X, Pos, 0)), MyRange, 2, 0)
End If
End Function

 

[arkusM]

Re: June/July 2008 Challenge of the Month

Aladin,
I am running XL2003 and have no issue with the first posted formula. It works perfectly.

M

You'd need to invoke a formula of the type as in:

http://www.mrexcel.com/forum/showthread.php?t=321942

http://www.mrexcel.com/forum/showthread.php?t=323188


Applied to the problem/task at hand...

=LOOKUP(9.99999999999999E+307,
    SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),
    $E$2:$E$10)

On Excel 2003 and beyond, convert D2:E10 into a list (Data|List|CreateList).

On earlier version, we would need define dynamic named ranges, say, KEYS and SLOTS, and invoke:

=LOOKUP(9.99999999999999E+307,
    SEARCH(" "&KEYS&" "," "&A2&" "),
    SLOTS)

 

[arkusM]

Re: June/July 2008 Challenge of the Month

Barry,

I get the max number of Characters in a Cell thing, but is your formula really counting the len() of the cell? Maybe I am missing something but it would appear it is simply looking for the value of 2^15. (I have yet to try this in an application of my own where my numbers are way greater than 32k). I am not trying to nit-pick but, there are some great solutions here that I like to understand so I can implement them in my work.

Thanks for your time.

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Search returns the position of the sought for term within the being searched string (so max value would be 32,767). 2^15 is simply one bigger than the absolute maximum value in the lookup range (ie SEARCH(D$2:D$10,A2)). LOOKUP expects a sorted lookup range (sorted ascending) and if it doesn't find a match, it will return the next item in the lookup range which is less than the looked for value. Thus 2^15 which match against whichever of the cells in D2:D10 returns a value when Search is applied (ie whichever color is present in the string). The relative position D2:D10 is then used to return the person from column E.


Does that rambling explanation help or hinder?

 

[Aladin Akyurek]

Re: June/July 2008 Challenge of the Month

Search returns the position of the sought for term within the being searched string (so max value would be 32,767). 2^15 is simply one bigger than the absolute maximum value in the lookup range (ie SEARCH(D$2:D$10,A2)). LOOKUP expects a sorted lookup range (sorted ascending) and if it doesn't find a match, it will return the next item in the lookup range which is less than the looked for value. Thus 2^15 which match against whichever of the cells in D2:D10 returns a value when Search is applied (ie whichever color is present in the string). The relative position D2:D10 is then used to return the person from column E.


Does that rambling explanation help or hinder?

2^15 is I think an unfortunate variant on Excel's well-known constant:

9.99999999999999E+307

If you want to know how such a number as lookup value operates, see:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[zapicm]

Re: June/July 2008 Challenge of the Month

Hi all,

First post in this forum, got attracted by the challenge on the home page and I am happy to see the high level of the responses (I specially like the simplicity of the LOOKUP one).

Before searching the thread, I came up with a slightly variation of the arrays formulas proposed, but as it was not going to add anything new, I tried to come with an UDF that could use one of my favorite programming tools: regular expressions. This is my attempt:

Function MatchColors(strValue As String, rngList As Range) As String
    Dim regEx, Matches, i, strResult, bFlag
    
    'Create and set the parameters for the regular expressions object
    Set regEx = CreateObject("vbscript.regexp")
    regEx.Global = True
    regEx.IgnoreCase = True
    
    For i = 1 To rngList.Rows.Count
        'Just in case someone uses full column ranges
        If rngList.Cells(i, 1).Value = "" Then Exit For
        'Wrap the values on \b to mark word boundaries
        regEx.Pattern = "\b" & rngList.Cells(i, 1).Value & "\b"
        Set Matches = regEx.Execute(strValue)
        'If there is a match, add the relevant name to the string
        If Matches.Count > 0 Then
            'If that is not the first match, include a word separator
            If bFlag Then strResult = strResult & ", "
            strResult = strResult & rngList.Cells(i, 2).Value
            bFlag = 1
        End If
    Next
    MatchColors = strResult
End Function

The usage would be:
=MatchColors(A2,$D$2:$E$10)
And it should return a list of the names for all the colors present as complete words, separated by commas.

 

[tjmcgough]

Re: June/July 2008 Challenge of the Month

Hi everyone,

Was happy to see my question posted as the challenge. I'm a new user to this forum and appreciate all the responses.

Richard, have tried your solution and it solves most of the issue. However, i noticed a few limitations still exist;

1. If no keyword match is found the first result is still returned. Anyway to have a blank returned if no match found?
2. If a new row is added to the keyword table, the entire formula "breaks". Was thinking of adding placeholder keywords that would never match to allow for future growth of new rows. Example, adding a bunch of rows with ">>>Unused For Now<<<", and then changing to the "real" keyword if/when needed.

Any ideas on how to fix one or both?

Many thanks,

Tom

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Hi Tom

i think the answer to both is to use Barry's formula (possibly with Aladin's modification).

 

[rgdwar1]

Re: June/July 2008 Challenge of the Month

=index($e$2:$e$10,match(1,search("*"&$d$2:$d$10&"*",a2),0))

Ctrl-shift-enter

 

[rgdwar1]

Re: June/July 2008 Challenge of the Month

Also, I realized that if no phrase was entered or one was accidentally deleted, I didn't have any code to catch this so I added the following:

=IF(A2="","No Phrase Entered",INDEX($E$2:$E$10,MATCH(1,SEARCH("*"&$D$2:$D$10&"*",A2),0)))

confirmed with CTRL-SHIFT-ENTER