-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month
- Thread starter MrExcel
- Start date
Excel Facts
Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Daniel Ferry
New Member
- Joined
- Jun 20, 2008
- Messages
- 0
Re: June/July 2008 Challenge of the Month
My idea was this for B2 and down (CSE, of course):
=OFFSET(D$1,SUM((IFERROR(FIND(D$1:D$10,A2),0)>0)*ROW(D$1:D$10))-1,1)
While that may not get me the win again this year since Harry Houdini's is a slicker approach, his can be improved upon. This is shorter by a few characters and works well:
=LOOKUP(2^9,FIND(D$2:D$10,A2),E$2:E$10)
Great job Harry.
My idea was this for B2 and down (CSE, of course):
=OFFSET(D$1,SUM((IFERROR(FIND(D$1:D$10,A2),0)>0)*ROW(D$1:D$10))-1,1)
While that may not get me the win again this year since Harry Houdini's is a slicker approach, his can be improved upon. This is shorter by a few characters and works well:
=LOOKUP(2^9,FIND(D$2:D$10,A2),E$2:E$10)
Great job Harry.
Last edited:
Daniel Ferry
New Member
- Joined
- Jun 20, 2008
- Messages
- 0
Re: June/July 2008 Challenge of the Month
I meant this for Harry's formula:
=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)
which will work for all of the rows in 2007, and earlier versions. From a character count perspective, I do not think it's possible to improve this and still maintain the ability to copy the formula down.
I meant this for Harry's formula:
=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)
which will work for all of the rows in 2007, and earlier versions. From a character count perspective, I do not think it's possible to improve this and still maintain the ability to copy the formula down.
Daniel Ferry
New Member
- Joined
- Jun 20, 2008
- Messages
- 0
Re: June/July 2008 Challenge of the Month
By the way, 9^5 is sufficient (in Harry's approach) since the limit to the number of characters in a cell is now 32,000 in Excel 2007. The limit was just 1,000 characters in prior versions. These alarmingly large numbers are overkill: the "color" could at most be 32,000 characters into the string in a cell, so 9^4 is too small to account for the "ultimate" case, while 9^5 is overkill but will work in any situation, without exception.
By the way, 9^5 is sufficient (in Harry's approach) since the limit to the number of characters in a cell is now 32,000 in Excel 2007. The limit was just 1,000 characters in prior versions. These alarmingly large numbers are overkill: the "color" could at most be 32,000 characters into the string in a cell, so 9^4 is too small to account for the "ultimate" case, while 9^5 is overkill but will work in any situation, without exception.
Krishnakumar
Well-known Member
- Joined
- Feb 28, 2003
- Messages
- 2,615
Re: June/July 2008 Challenge of the Month
May be..
=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10," "&A2&" "),$E$2:$E$10)
May be..
=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10," "&A2&" "),$E$2:$E$10)
Re: June/July 2008 Challenge of the Month
Using named ranges [c =$D$2:$D$10 and n =$E$2:$E$10 x =9^6]
charactercount is reduced by
=LOOKUP(x,FIND(c,A2),n)
Using named ranges [c =$D$2:$D$10 and n =$E$2:$E$10 x =9^6]
charactercount is reduced by
=LOOKUP(x,FIND(c,A2),n)
Last edited:
Daniel Ferry
New Member
- Joined
- Jun 20, 2008
- Messages
- 0
Re: June/July 2008 Challenge of the Month
Obviously my post meant that my improvement on Houdini's formula was the shortest character count possible WITHOUT resorting to named formulas. One could simpy name any of the formulas that work, "zz".
Now all you need to enter is:
=zz
But that's pointless.
My improvement still reigns as the SHORTEST character count possible WITHOUT named formulas.
HOWEVER, dynamic ranges would be an improvement usability-wise, for the lookup tables...
Obviously my post meant that my improvement on Houdini's formula was the shortest character count possible WITHOUT resorting to named formulas. One could simpy name any of the formulas that work, "zz".
Now all you need to enter is:
=zz
But that's pointless.
My improvement still reigns as the SHORTEST character count possible WITHOUT named formulas.
HOWEVER, dynamic ranges would be an improvement usability-wise, for the lookup tables...
Last edited:
Quasi
New Member
- Joined
- Jun 21, 2008
- Messages
- 36
Re: June/July 2008 Challenge of the Month
Hi,
I`m a new person here. My first solution looks like that
=INDEX($E$1:$E$10;MAX((IF(COUNTIF(A2;CONCATENATE("*";$D$2:$D$10;"*"));ROW($D$2:$D$10);""))))
This is array formula (Ctrl+Shift+Enter)
Best regards,
Quasi
Hi,
I`m a new person here
. My first solution looks like that=INDEX($E$1:$E$10;MAX((IF(COUNTIF(A2;CONCATENATE("*";$D$2:$D$10;"*"));ROW($D$2:$D$10);""))))
This is array formula (Ctrl+Shift+Enter)
Best regards,
Quasi
Re: June/July 2008 Challenge of the Month
How about ?
UDF
=VLookLike(A2, $E$2:$E$10)
How about ?
UDF
=VLookLike(A2, $E$2:$E$10)
Code:
Function VLookLike(txt As Variant, rng As Range) As Variant
Dim myPtn As String
myPtn = "(" & Join(WorksheetFunction.Transpose(rng.Columns(1).Value),"|") & ")"
With CreateObject("VBScript.RegExp")
.Pattern = myPtn
If .test(txt.Value) Then myMatch = .execute(txt.Value)(0)
End With
VLookLike = rng.Columns(2).Cells(WorksheetFunction.Match(myMatch,rng.Columns(1),0)).Value
End Function
