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

[smartchap]

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.

 

[gardnertoo]

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:

=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.

 

[Papa Omaha]

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

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

 

[robind21283]

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!

 

[Richard Schollar]

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"?

 

[robind21283]

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 $)

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

I see. Well, you could make your list of colors a Dynamic Named Range which will expand and contract as you add to them, then reference that in the formula instead of the specific range. See details of DNRs here:

http://www.contextures.com/xlNames01.html#Dynamic

 

[robind21283]

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.

 

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

What formula are you using to create the DNR?

 

[robind21283]

Re: June/July 2008 Challenge of the Month

=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!D:D),1)