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

[davejago]

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)

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.

 

[EugeneCarter]

Re: June/July 2008 Challenge of the Month

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.

Check out the posts from around Jun 17th, 2008 and the links within them. There were many posts on Barry's solution.

 

[andrewman]

Re: June/July 2008 Challenge of the Month

When we have the result for June/July 2008 Challenge?

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

That is a good question! I have been waiting too!

In fact, in the MrExcel Survey they just sent out yesterday, I said that they should do more of this monthly challenge stuff!

 

[Mattison]

Re: June/July 2008 Challenge of the Month

A minor tweak I think could make the "fastest" formula faster...

By changing it from: =LOOKUP(9^6,SEARCH(D$2:D$10,A2),E$2:E$10)

to: =LOOKUP(LEN(A2),SEARCH(D$2:D$10,A2),E$2:E$10)

...it would only search only the number of characters actually in the cell, not the ~32k as previously defined.

 

[bosco_yip]

Re: June/July 2008 Challenge of the Month

A minor tweak I think could make the "fastest" formula faster...

By changing it from: =LOOKUP(9^6,SEARCH(D$2:D$10,A2),E$2:E$10)

to: =LOOKUP(LEN(A2),SEARCH(D$2:D$10,A2),E$2:E$10)

...it would only search only the number of characters actually in the cell, not the ~32k as previously defined.

Hi Mattison,

9^6 is a number,

but,

LEN(A2) is a function

Replaced 9^6 with LEN(A2) would add one more function to the formula

…..the changes made the formula slower

Regards
Bosco

 

[QuinDavis]

Re: June/July 2008 Challenge of the Month

Compared to others, I feel like such a beginner...

Function FLookup(FullString As String, KeywordList As Range) As String
Dim Key As String
For i = 1 To KeywordList.Rows.Count
Debug.Print i & " " & FullString & " " & KeywordList(i, 1) & " " & KeywordList(i, 2)
Key = "*" & KeywordList(i, 1) & "*"
If KeywordList(i, 1) = "" Then Exit Function
If FullString Like Key Then
For j = 1 To KeywordList.Rows.Count
If KeywordList(i, 1) = KeywordList(j, 1) Then
FLookup = KeywordList(j, 2)
Exit Function
End If
Next
End If
Next
End Function

 

[tony567]

Re: June/July 2008 Challenge of the Month

Compared to others, I feel like such a beginner...

Function FLookup(FullString As String, KeywordList As Range) As String
Dim Key As String
For i = 1 To KeywordList.Rows.Count
Debug.Print i & " " & FullString & " " & KeywordList(i, 1) & " " & KeywordList(i, 2)
Key = "*" & KeywordList(i, 1) & "*"
If KeywordList(i, 1) = "" Then Exit Function
If FullString Like Key Then
For j = 1 To KeywordList.Rows.Count
If KeywordList(i, 1) = KeywordList(j, 1) Then
FLookup = KeywordList(j, 2)
Exit Function
End If
Next
End If
Next
End Function

QuinDavis<SCRIPT type=text/javascript> vbmenu_register("postmenu_1768518", true); </SCRIPT>

Join Date: Dec 2008
Posts: 0



QuinDavis <<<<< you got zero count on posting???
How you did that?

 

[QuinDavis]

Re: June/July 2008 Challenge of the Month

QuinDavis <<<<< you got zero count on posting???
How you did that?

I'm either a master hacker or a virgin newbee... (Not hacker..)

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Posts in the Lounge do not count towards your total post count - so if QuinDavis has not posted in any other forum on the Board then he will have a post count of zero