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

Joined
Feb 8, 2002
Messages
3,381
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:
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
 
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)
 
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)
 
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
 
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
 
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
 

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top