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

[MrExcel]

I've posted a new challenge of the month today. Like the last challenge, this one will have many different approaches. Post your entries here. While the ultimate "best" entry wins the LiveLessons DVD, several podcast DVD's are available to anyone who proposes a significant advancement towards a cool solution.

Entries are due by 15 July 2008.

Bill Jelen


***WINNERS LIST PUBLISHED HERE***

http://www.mrexcel.com/pc18.shtml

[RichardSchollar]

Looks like I get to be first:

Entered in B2 copied down as far as required (matches against the last colour occurrence in the cell):

=INDEX($E$2:$E$10,MAX(ISNUMBER(SEARCH($D$2:$D$10,A2))*(ROW($D$2:$D$10)-ROW($D$2)+1)))

Confirmed with Ctrl+Shift+Enter


This one matches against the first colour occurrence in the cell):

=INDEX($E$2:$E$10,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$10,$A2)),0))

Confirmed with Ctrl+Shift+Enter

[RichardSchollar]

In case of multiple occurrences of individual colours (could include submatches too eg red and redditch) the following will select the name against the colour with the highest number of occurrences:

=INDEX($E$2:$E$10,SUMPRODUCT(MAX(((LEN($A2)-LEN(SUBSTITUTE($A2,$D$2:$D$10,"")))/LEN($D$2:$D$10)=MAX((LEN($A2)-LEN(SUBSTITUTE($A2,$D$2:$D$10,"")))/LEN($D$2:$D$10)))*(ROW($D$2:$D$10)-ROW($D$2)+1))))

Confirmed with Ctrl+Shift+Enter

[Oorang]

Got caught by the edit thingy. The full post should have been:


I did the challenge before I looked at the thread so this may have some overlap on RS but it's a bit different so I posted it anyway.

It will find the first occurrence of any color in the color range in the cell's value and vlookup on that value. Place in B2 and copy down. Returns #VALUE! on fail.

=VLOOKUP(INDEX($E$2:$E$10,SUMPRODUCT(--NOT(ISERROR(SEARCH($E$2:$E$10,A2))),ROW(INDIRECT("1:" & ROWS($E$2:$E$10))))-1),$E$2:$F$10,2)

[barry houdini]

Here's one possibility.....

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

[RichardSchollar]

That's great Barry!

[gingerafro]

Originally Posted by barry houdini

Here's one possibility.....

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

taken a few minutes to get my head round it, but that is quality.

[schielrn]

Here's a VBA approach:

Sub returnName()
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To Cells(Rows.Count, 4).End(xlUp).Row
        If InStr(1,Cells(i, 1).Value, Cells(j, 4).Value) Then Cells(i, 2).Value = Cells(j, 5).Value
    Next j
Next i
End Sub

[Jon von der Heyden]

Yes very slick indeed, although I must ask, why have you specifically used 2^15? I understand the bignum concept, only most people seem to use 9.99999999999999E+307. Was there particular purpose or did you just throw it in knowing that it would be large enough?

Regards
Jon

[jonmo1]

Well, since no one else did a VBA UDF solution...Maybe I'll be the first to do that..

formula in B2 filled down is

=Jonmo1(A2,D:E)

Public Function Jonmo1(C As Range, L As Range)
Dim MyArray As Variant
Dim X As Long, Y
MyArray = Split(C, " ")
Jonmo1 = ""
For X = LBound(MyArray) To UBound(MyArray)
    Y = Application.Match(MyArray(X), L.Columns(1), 0)
    If Not IsError(Y) Then
        Jonmo1 = L(Y, 2)
        Exit For
    End If
Next X
End Function