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

[mgirvin]

Re: June/July 2008 Challenge of the Month

Daniel Ferry,

Although I too think that Barry’s is still the best, I like your formula here also:

=OFFSET(D$1,SUM((IFERROR(FIND(D$1:D$10,A2),0)>0)*ROW(D$1:D$10))-1,1)

A slight variation is:

=OFFSET(E$1,SUM(ISNUMBER(SEARCH(D$2:D$10,A2))*ROW(D$2:D$10))-1,)

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

So we got Barry’s straight to the point formula:

=LOOKUP(2^15,SEARCH(I$2:I$10,A2),J$2:J$10)

Wsjackman’s constructing a cell reference formula:

=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

And Daniel’s moving from a starting point formula:

=OFFSET(D$1,SUM((IFERROR(FIND(D$1:D$10,A2),0)>0)*ROW(D$1:D$10))-1,1)

Thank you all for helping me to see new concepts for solving the problem at hand.

For the formulas that have been entered so far, I have learned so much! Thanks to all the amazing Excel Masters here at the MrExcel Discussion Board.

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

A large problem with my original post

=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

is that if there are several matches, the wrong name or an error will occur.

The biggest problem with Barry Houdini's better equation is that only the first match is shown.

(both meet the description of the challenge for one match, but can cause practical problems, especially with my formula)

If your security lets you use UDFs, they are probably the best way to go.

 

[Quasi]

Re: June/July 2008 Challenge of the Month

Quasi,

How did you get your formula to run with semi-colons? I only got it to work when I replaced the semi-colons with commas, such as this:

=INDEX($E$1:$E$10,MAX((IF(COUNTIF(A2,CONCATENATE("*",$D$2:$D$10,"*")),ROW($D$2:$D$10),""))))

Is there a trick that allows you to you separate arguments with semi-colons?

Yes, You can change it in Windows - Control Panel/Regional and Language Options.

Here is a useful link
http://digilander.libero.it/foxes/Excel/Excel_How_to_change_the_argument_separator.htm

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

wsjackman,

Thank you for pointing out the shortcomings of the formulas. I will look at some of the other solutions posted that address those issues. I still have received great benefit from the concepts presented so far!

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

Quasi,

Cool! I had no idea. Thanks for the link.

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Mike Mel Girvin:

I'm always preaching compactness of formula, so your slight variation on my original formula is interesting. The reason I went with mine instead of your slightly more compact formula is compactness of application, which takes it a step further than just the formula.

You see in a real application of this, it is likely that the lookup tables would grow. I can thus replace three of the range references with ONE dynamic range in this fashion:

=OFFSET(KEY,SUM((IFERROR(FIND(KEY,A2),0)>0)*ROW(KEY))-1,1)

Of course, yours has the advantage of working in all versions. I've been on a mission lately to force myself to use 2007's new formulas so they are as old hat to me as the original roster.

A variant taking the better points of yours and mine that would work in all versions is:

=OFFSET(E$1,SUM(ISNUMBER(FIND(KEY,A2))*ROW(KEY))-1)

Thanks for the compliment. I'm glad you liked the approach. I won the last contest Mr Excel posted (more than a year ago). I like my approach as well, but nothing can compare with Houdini's. I had never seen this problem before and had to dream-up the solution. This technique with the large number in the Lookup formula has apparently been drifting around this board for some time. Although people that use the largest number that Excel can fathom and call it some sort of CONSTANT, really get me. There is absolutely no reason to use a number larger than 32767 (the max number of character that fit in a cell). These guys are using a number billions of times bigger than that. I am convinced that this causes Excel's engine to work harder than needed. I like the 9^5 method as it is the most compact method that will get you to a number large enough to account for every possible character, AND it requires 10 fewer multiplication steps than 2^15. In processing terms, each multiplication step is expensive.

Thanks again!

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

In processing, would 6E4 take more or less time than 9^5?

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

wsjackman:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
While I cannot speak with authority on the algorithm employed by Excel to interpret scientific notation, I can say that even if it were perfect you are still going to have four multiplication steps involved. First you are taking 10 and raising it to the 4th power, which means 10 * 10 * 10 * 10 = 10,000. That is 1E4 and it required three multiplication steps. Finally, you need to take that product and multiply by 4 to get 4E4, or 40,000. That is one more multiplication step giving a total of 4 multiplication steps.<o></o>
<o></o>
The 9^5 that I've been promoting also gets us there in 4 multiplication steps:<o></o>
<o></o>
9 * 9 * 9 * 9 * 9 = 59,049<o></o>
<o></o>
Both methods produce a number large enough in 4 multiplication steps and require only three characters of input, minimizing formula size. It is a good point that you bring up and I do not know which is more efficient in terms of processing. Since multiplication is so expensive, they are both in the same ballpark. It all depends on the relative algorithms employed by Excel to interpret both scientific notation and simple exponentiation. There may be a penalty by Excel’s calc engine on the scientific notation due to SN's slightly more complex interpretation, but I am speculating.

 

[ghatfanazzam]

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)

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

I have a formula for this challenge , I don't know if any body else posted it before.
in cell B2 put this formula
=VLOOKUP(INDEX($D$2:$D$10,MATCH(TRUE,COUNTIF(A2,"*"&$D$2:$D$10&"*")>0,0)),$D$2:$E$10,2,FALSE) then confirm it with Ctrl + Shift + Enter