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

[arkusM]

Re: June/July 2008 Challenge of the Month

Thanks Barry and Aladin.
I use the Lookup() and the 9.99999e+307 variable often.
But the 2^15 was intriguing, and I forgot/was-unaware of the exact function of the Search() formula. Barry your reason for using it makes sense.

Thanks for the clarification and explanation.

2^15 is I think an unfortunate variant on Excel's well-known constant:

9.99999999999999E+307

If you want to know how such a number as lookup value operates, see:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

Wow!

barry houdini's solution is quite clever! I am still trying to get my head fully around it...

Had you run into this problem before, or similar problem, or did you just think this up today?

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

a (to me) more traditional equation would be
=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

an alternate would be to name the "color" range and when new colors were added, just change the named range

=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND(color,A2)))*ROW(color)))

with color defined as d2:d10

 

[arkusM]

Re: June/July 2008 Challenge of the Month

Wow!

barry houdini's solution is quite clever! I am still trying to get my head fully around it...

Had you run into this problem before, or similar problem, or did you just think this up today?

I have run into this "problem" before, though my solution was ridiculously complex, cumbersome and "expensive". I wish that I had found this site long ago!! would have saved me time waiting for XL to calc!! Thanks to all who contribute to this board. God bless you.

M

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

Barry Houdini's solution is great. I will use it today, but will be working on a simple method to find any second or third "color" reference in the same line. [hidden columns and substitute and a reapplication of Barry's equation are highly probable] (People I work with know I always have plenty of complicated methods for doing things.)

 

[zapicm]

Re: June/July 2008 Challenge of the Month

Barry Houdini's solution is great. I will use it today, but will be working on a simple method to find any second or third "color" reference in the same line. [hidden columns and substitute and a reapplication of Barry's equation are highly probable] (People I work with know I always have plenty of complicated methods for doing things.)

The UDF that I posted should cover any number of colors on one line, if you try it please let me know if it worked.

Thanks!

 

[Oorang]

Re: June/July 2008 Challenge of the Month

Does Bill accept UDFs were accepted for challenge solutions?

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Yes I believe any kind of solution may be proposed.

 

[zapicm]

Re: June/July 2008 Challenge of the Month

I read the challenge again, and it does say that user defined functions, or even macros, are allowed. By the way, other part of the challenge says that it has to be adaptable to any number of entries, you can use the full columns (D:E) with my formula

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

I like the udf.
My application actually works better with the names in different columns
=substitute(A2,LOOKUP(2^15,SEARCH(D$2:D$10,A2),d$2:d$10)<!-- / message -->),"")
in column C
for simplicty, i went to an empty column and reused the houdini formula referencing c2

The actual formula I used had vastly different ranges so I hope I have not done any of my typical typos in this formula
I could have continued for third matches but didnt need to for the data set i used