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

Re: June/July 2008 Challenge of the Month

Dear jindon

On page 6 I think your formula needs to be corrected as

=VLookLike(A2, $D$2:$E$10)

and it works.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Re: June/July 2008 Challenge of the Month

While Barry's solution seems to be far and away the best formula-based entry, I have another solution nobody else has posted:
Code:
=INDEX(E$2:E$10,MATCH(INDEX(D$2:D$10,MATCH(MIN(LEN(SUBSTITUTE(A2,D$2:D$10,""))),(LEN(SUBSTITUTE(A2,D$2:D$10,""))),0)),D$2:D$10,0))

Not as clean, and it gets different results when a second color is added. Just thought I would toss in my two bits.

I didn't know until this challenge that the LOOKUP function would work against an array containing error values. My LEN(SUBSTITUTE array contains all numerical values for the INDEX(MATCH to work on. Good to know there's a better way.
 
Last edited:
Re: June/July 2008 Challenge of the Month

I'm new to the VB/computer programming thing...judging from the entries, I imagine I could have done this 10 times more efficiently (if I knew how). I'm learning a ton though!!! :biggrin:

Public Function colorfun(x As String)
Dim phraselettercount As Integer
Dim keyword_assignedto(9, 2) As String
Dim col As Integer
Dim wordcount As Integer
Dim wordstart As Integer
Dim rollingword As String
phraselettercount = Len(x)
wordstart = 1
col = 1
row1 = 1
x = Trim(x)
For i = 1 To 18
If i <= 9 Then
keyword_assignedto(i, col) = Range("D" & i + 1).Value
Else
If col = 1 Then
col = col + 1
End If
keyword_assignedto(i - 9, col) = Range("E" & i - 9 + 1).Value
End If
Next i
col = 1
For Z = 1 To phraselettercount + 1
If Mid(Trim(x), wordstart, 1) <> " " And wordstart < Len(x) + 1 Then
rollingword = rollingword & Mid(x, wordstart, 1)
wordstart = wordstart + 1
Else
For p = 1 To 9
If rollingword = keyword_assignedto(p, col) Then
colorfun = keyword_assignedto(p, col + 1)
End If
Next p
wordstart = wordstart + 1
rollingword = ""
End If
Next Z
End Function
 
Re: June/July 2008 Challenge of the Month

I can't seem to get this formula to work if I add to the list of colors since its locked. Is there anyway to make this dynamic?

Otherwise, this is FABULOUS!
 
Re: June/July 2008 Challenge of the Month

Hi

Which formula are you using and what do you mean when you say "it is locked"?
 
Re: June/July 2008 Challenge of the Month

Apparently, I prematurely hit send or thought everyone was psychic...

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

And the cell references are frozen/locked (by the $)
 
Re: June/July 2008 Challenge of the Month

When I select the whole row as a DNR, and put the names into the formula, I get a 0 result.
 
Re: June/July 2008 Challenge of the Month

What formula are you using to create the DNR?
 

Forum statistics

Threads
1,212,929
Messages
6,110,740
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