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

MrExcel

.

Joined

Feb 8, 2002

Messages

3,417

Office Version

1. 365

Platform

1. Windows

• Jun 17, 2008

• #1

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

 

Last edited by a moderator: Mar 13, 2009

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jun 17, 2008

• #2

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

 

Last edited: Jun 17, 2008

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jun 17, 2008

• #3

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

Well-known Member

Joined

Mar 4, 2005

Messages

2,071

• Jun 17, 2008

• #4

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

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Jun 17, 2008

• #5

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

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jun 17, 2008

• #6

Re: June/July 2008 Challenge of the Month

That's great Barry!

 

G

gingerafro

Active Member

Joined

Mar 23, 2005

Messages

448

• Jun 17, 2008

• #7

Re: June/July 2008 Challenge of the Month

barry houdini said:

Here's one possibility.....

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

Click to expand...

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

 

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Jun 17, 2008

• #8

Re: June/July 2008 Challenge of the Month

Here's a VBA approach:

Code:

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

MrExcel MVP, Moderator

Joined

Apr 6, 2004

Messages

10,912

Office Version

1. 365

Platform

1. Windows

• Jun 17, 2008

• #9

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

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jun 17, 2008

• #10

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)

Code:

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