***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

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

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

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

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]

Re: June/July 2008 Challenge of the Month

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]

Re: June/July 2008 Challenge of the Month

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

That's great Barry!

 

[gingerafro]

Re: June/July 2008 Challenge of the Month

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]

Re: June/July 2008 Challenge of the Month

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]

Re: June/July 2008 Challenge of the Month

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]

Re: June/July 2008 Challenge of the Month

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