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

[Oorang]

Re: June/July 2008 Challenge of the Month

Has anyone break tested these over large datasets to see how they perform? (Obviously I haven't)

 

[Daniel Ferry]

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.

 

[Daniel Ferry]

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.

 

[Daniel Ferry]

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.

 

[Krishnakumar]

Re: June/July 2008 Challenge of the Month

Emma,

If it's the case that A2 has a bunch of CHAR(160), it is simpler to invoke:

=LOOKUP(9.99999999999999E+307,
   SEARCH(" "&$D$2:$D$10&" "," "&SUBSTITUTE(A2,CHAR(160),"")&" "),
   $E$2:$E$10)

May be..

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10," "&A2&" "),$E$2:$E$10)

 

[wsjackman]

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.

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)

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

typical typos for me
c needs to be cc, n needs to be nn, x needs to be xx

 

[Daniel Ferry]

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)

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...

 

[Quasi]

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

 

[jindon]

Re: June/July 2008 Challenge of the Month

How about ?
UDF
=VLookLike(A2, $E$2:$E$10)

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