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

Re: June/July 2008 Challenge of the Month

EugeneCarter said:

1) doesn't use the lookup table (you manually entered lookup values)
2) not scalable (excel can only support so many recursive IFs)

Click to expand...

Oh, I didnt know about the If function Limits, THANKS!

Re: June/July 2008 Challenge of the Month

barry houdini said:

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Click to expand...

I've been try to understand why this works by looking at the descriptions of the functions used. I don't quite understand why the Search() function works within the Lookup() function but not by itself....can anyone help me see why this is so?

Re: June/July 2008 Challenge of the Month

kevinmintz006 said:

sorry i am late, i am a new user as of today = ).

here's my solution (macro code):


Sub Solution()
'
' Solution Macro
' Macro recorded 7/17/2008 by e363131
'
'
Dim i As Long, strx As String, stry As String, strz As String
i = 2
Range("A:A").Select

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste

For i = 1 To 10


strx = "D" & i
'MsgBox (strx)
stry = Range(strx).Value
'MsgBox (stry)
strz = "~*" & stry & "~*"
Selection.Replace What:="*" & Range(strx).Value & "*", Replacement:="=E" & i, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next
End Sub



It's good to be partof the community.

Click to expand...

---------------------------------------------------
I'd like to post this edit to my original formula. (Just so you wouldn't have to change the If statement when you add more lines to the lookup table)
-----------------------------------------------------
Sub Solution()
Dim i As Long, strx As String, stry As String, strz As String
i = 2
Range("A:A").Select
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste

intRow = Range("D:D").SpecialCells(xlCellTypeConstants).Count
For i = 1 To intRow
strx = "D" & i
stry = Range(strx).Value
strz = "~*" & stry & "~*"
Selection.Replace What:="*" & Range(strx).Value & "*", Replacement:="=E" & i, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub
-------------------------------------------------------

what do you think?

Re: June/July 2008 Challenge of the Month

This

kevinmintz006 said:

Range("A:A").Select
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste

what do you think?

Click to expand...

Could be this:
Range("B:B").FormulaR1C1 = Range("A:A").FormulaR1C1
or
Range("B:B").Formula = Range("A:A").Formula
or
Range("B:B").Value= Range("A:A").Value
depending on your preference about values, absolute references, etc.

Re: June/July 2008 Challenge of the Month

kevinmintz006 said:

---------------------------------------------------
I'd like to post this edit to my original formula. (Just so you wouldn't have to change the If statement when you add more lines to the lookup table)
-----------------------------------------------------

what do you think?

Click to expand...

One flaw that I see with this, is that it only works well if none of lookup values are similar to others. For example if one is blue and another is blue-green

Re: June/July 2008 Challenge of the Month

here's a function. useage is as follows. In cell B2 enter formula =sLookup(A2,$D$2:$D$10,2) which calls the sLookup function as follows:

Function sLookup(txtInput As String, rngList As Range, wIndex As Integer)
Dim wRow As Integer
Dim wCol As Integer

' find how many keywords in rngList

wRow = 1
wList = rngList

For Each cell In rngList
If InStr(1, txtInput, cell) > 0 Then
wRow = cell.Row
wCol = cell.Column
sLookup = Cells(wRow, wCol + wIndex - 1)
Exit Function
End If
Next 'cell
End Function

I didn't understand it either!

I'm new here and really appreciate this forum. Thanks to everyone for your... brilliance. I realize I have so much more to leverage in xl.

Ok, I'm writing this post for anyone like me- who really scratched their heads over this one. Perhaps there are a few? I read Richard's explanation which got me halfway there but couldn't pull off the juggling necessary. Thought I'd explain how I finally understood it to those who may be having my experience. The formula to be interpreted is:

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

First, the SEARCH function has the argumants backward from what I'm used to. Usually with the SEARCH /FIND function I'm looking for 1 thing in an ocean of things. In this case an ocean of things are looking for one thing and returning 9 results. I kept thinking that a SEARCH involving 'A2' must only return one number. I was also visually thrown off by the lack of Braces {} reminding me of array formula.

So, the SEARCH function returns an array of 9 results each corresponding to the colors in the order they appear. The array always has 8 errors and one valid number like this:

#VALUE!
#VALUE!
#VALUE!
12
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

The lookup function will always match the huge number (32,767) to the one row with the valid number- row 4 in this case- and that is of course the row of the matching color. The Lookup function then goes 4 rows down in column 'E' to find the correct person's name.

I realize this is known to most but though I'd describe the formula in the way it finally came to me. And I agree the formula is beautiful in its simplicity.

My formula was pretty standard. I added ascending numbers next to the color/name table though.

={INDEX(Color_Tab,SUM(IF(ISERROR(FIND(Colors,$A2,1)),0,Color_Nums)),3))

Cheers

Re: June/July 2008 Challenge of the Month

It works, but I don't understand how. References to LOOKUP don't mention placing a value in front of the lookup_value. Yes, I'm am a beginner, looking for understanding.
I placed only the SEARCH portion of the equation into each cell in col. B, no array. The result was only the cells of the same row were evaluated. So I made each eq. in col. B an array. But, only the first cell "D2" was evaluated against each phrase. Cells B2 through B4 returned values. Getting closer. When using the full eq, I played with the value 2^15, when it is = or > to the value returned by the SEARCH eq., it returns the correct value from col.E. Need a 'nudge' understanding how the first number in the LOOKUP creates an array. Thanks.

Re: June/July 2008 Challenge of the Month

MIKE617 said:

It works, but I don't understand how. References to LOOKUP don't mention placing a value in front of the lookup_value. Yes, I'm am a beginner, looking for understanding.
I placed only the SEARCH portion of the equation into each cell in col. B, no array. The result was only the cells of the same row were evaluated. So I made each eq. in col. B an array. But, only the first cell "D2" was evaluated against each phrase. Cells B2 through B4 returned values. Getting closer. When using the full eq, I played with the value 2^15, when it is = or > to the value returned by the SEARCH eq., it returns the correct value from col.E. Need a 'nudge' understanding how the first number in the LOOKUP creates an array. Thanks.

Click to expand...

Thanks Rhino Dance!
Sorry all, should have read previous posts.

Re: June/July 2008 Challenge of the Month

Mike,

You wrote:
References to LOOKUP don't mention placing a value in front of the lookup_value.

There is no number in front of the lookup value. The lookup value is 2^15 (2 raised to the 15th power) which evaluates to a number near 32,000.

You are right that the below formula returns one result in the cell in which it is written.

{=SEARCH(D$2:D$10,A2)}

But now, highlight the whole formula up in the formula bar and then hit [F9]. [F9] calculates whatever is highlighted. You will then see the 9 array values in the formula bar which will look something like this:

{14;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

Also to answer your question, the first number in a Lookup is a repeating array of the same value equal to the same number of values that it is being compared to.

Take a Lookup that looks for 'a' in a lookup array of 'b', 'c' 'd'. It does 3 tests of 'a' against the three values. Like this:
'a' vs 'b'
'a' vs 'c'
'a' vs 'd'

So there's 3 repeating 'a' s. This is the same as 9 repeating 2^15 's in the original equation.

Maybe also worth pointing out is that although the Lookup formula processes via arrays (multiple values) it does only return one number. And that comes back to square with intuition. Hope this is all correct and hope it helps.