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

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
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
 
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
 
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.)
 
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!
 
Re: June/July 2008 Challenge of the Month

Does Bill accept UDFs were accepted for challenge solutions?
 
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 :)
 
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
 

Forum statistics

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