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

[stanleydgromjr]

Re: June/July 2008 Challenge of the Month

Here you go.

Before my macro:

Excel Workbook
	A	B	C	D	E
1	Phrases	Assigned to		Keyword	Assigned to
2	the ocean is blue			blue	Joe
3	I like this blue shirt			red	Bob
4	blue is my favorite color			yellow	Mary
5	the ocean is red			pink	Fred
6	I like this red shirt			orange	Ralph
7	red is my favorite color			brown	Lora
8	the ocean is yellow			white	Tracy
9	I like this yellow shirt			lavendar	Earl
10	yellow is my favorite color			magenta	Jeny
11	the ocean is pink
12	I like this pink shirt
13	redditch
14	the ocean is blue
15	I like this blue shirt
16	blue is my favorite color
17	the ocean is red
18	I like this red shirt
19	red is my favorite color
20	the ocean is yellow
21	I like this yellow shirt
22	yellow is my favorite color
23	the ocean is pink
24	I like this pink shirt
25	redditch
26	the ocean is blue
27	I like this blue shirt
28	blue is my favorite color
29	the ocean is red
30	I like this red shirt
31	red is my favorite color
32	the ocean is yellow
33	I like this yellow shirt
34	yellow is my favorite color
35	the ocean is pink
36	I like this pink shirt
37	redditch
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Phrases	Assigned to		Keyword	Assigned to
2	the ocean is blue	Joe		blue	Joe
3	I like this blue shirt	Joe		red	Bob
4	blue is my favorite color	Joe		yellow	Mary
5	the ocean is red	Bob		pink	Fred
6	I like this red shirt	Bob		orange	Ralph
7	red is my favorite color	Bob		brown	Lora
8	the ocean is yellow	Mary		white	Tracy
9	I like this yellow shirt	Mary		lavendar	Earl
10	yellow is my favorite color	Mary		magenta	Jeny
11	the ocean is pink	Fred
12	I like this pink shirt	Fred
13	redditch	Bob
14	the ocean is blue	Joe
15	I like this blue shirt	Joe
16	blue is my favorite color	Joe
17	the ocean is red	Bob
18	I like this red shirt	Bob
19	red is my favorite color	Bob
20	the ocean is yellow	Mary
21	I like this yellow shirt	Mary
22	yellow is my favorite color	Mary
23	the ocean is pink	Fred
24	I like this pink shirt	Fred
25	redditch	Bob
26	the ocean is blue	Joe
27	I like this blue shirt	Joe
28	blue is my favorite color	Joe
29	the ocean is red	Bob
30	I like this red shirt	Bob
31	red is my favorite color	Bob
32	the ocean is yellow	Mary
33	I like this yellow shirt	Mary
34	yellow is my favorite color	Mary
35	the ocean is pink	Fred
36	I like this pink shirt	Fred
37	redditch	Bob
Sheet1

Option Explicit
Sub ColorAssignedTo()
'
' ColorAssignedTo Macro
' Macro created 06/21/2008 by Stanley D. Grom, Jr.
'
    Dim LRA&
    Dim LRD&
    Application.ScreenUpdating = False
    LRA& = Cells(Rows.Count, 1).End(xlUp).Row
    LRD& = Cells(Rows.Count, 4).End(xlUp).Row
    With Range("B2")
        .FormulaArray = "=INDEX(R2C5:R" & LRD& & "C5,MATCH(1,SEARCH(""*""&R2C4:R" & LRD& & "C4&""*"",RC[-1]),0))"
        .Copy Range("B3:B" & LRA&)
    End With
    With Range("B2:B" & LRA&)
        .Copy
        .Value = .Value
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[Richard Schollar]

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.

The maximum length of a cell has been 32767 since at least xl2000 (it was 32000 in xl97 per xl97's specification limits). 2^15 carries meaning as it is exactly 1 more than the maximum length of a cell in xl2000 and above.

 

[wsjackman]

Re: June/July 2008 Challenge of the Month

9^5 is greater than 2^15 with fewer characters.


Is there any penalty, Time, processing, etc., for using a number greater than is needed?
similarly, is there any penalty, for using a number raised to a power vs using the written out number?
also, is there any penalty, for using a named constant rather than having the value in the formula

I have been bit by Excel's floating decimal method before.

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

Wow, not only is Barry’s formula EXCELlent, but this formula from wsjackman is pretty cool too:

=INDIRECT("E"&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

One could even not hard code the E with this adaptation on wsjackman’s formula:

=INDIRECT(LEFT(ADDRESS(ROW($E$2),COLUMN($E$2),4),1)&SUMPRODUCT(NOT(ISERR(FIND($D$2:$D$10,A2)))*ROW($D$2:$D$10)))

 

[mgirvin]

Re: June/July 2008 Challenge of the Month

Quasi,

How did you get your formula to run with semi-colons? I only got it to work when I replaced the semi-colons with commas, such as this:

=INDEX($E$1:$E$10,MAX((IF(COUNTIF(A2,CONCATENATE("*",$D$2:$D$10,"*")),ROW($D$2:$D$10),""))))

Is there a trick that allows you to you separate arguments with semi-colons?

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Richard:

I was referring to the number of characters that can be printed from a cell. The new limit is 32k; the old is 1k. This comes from the Excel Team Blog at MSDN:

http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx

At any rate, 9^5 works in all cases and is the most compact method possible. It is also slightly faster than 2^15 (less multiplications going on). But that takes us in a different direction. I have been arguing for compactness of formula. The QUICKEST formula would replace all of these large numbers generators with the constant:

32767

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

For the record, I believe these formulas to be the best possible in their respective categories:

Shortest at 39 characters (without names):
=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)

Fastest:
=LOOKUP(32767,FIND(D$2:D$10,A2),E$2:E$10)

Most Convienent with Dynamic Ranges for the tables:
=LOOKUP(9^6,FIND(KEY,A2),ASSIGN)

 

[stanleydgromjr]

Re: June/July 2008 Challenge of the Month

Daniel Ferry,

With your rated "fastest" formula, a change to my original macro (which handles changing ranges):

Option Explicit
Sub ColorAssignedTo()
    Dim LRA&
    Dim LRD&
    Application.ScreenUpdating = False
    LRA& = Cells(Rows.Count, 1).End(xlUp).Row
    LRD& = Cells(Rows.Count, 4).End(xlUp).Row
    With Range("B2:B" & LRA&)
        .FormulaR1C1 = "=LOOKUP(32767,FIND(R2C[2]:R" & LRD& & "C[2],RC[-1]),R2C[3]:R" & LRD& & "C[3])"
        '.Copy
        '.Value = .Value
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Stan:

I like your approach, as it compliments the "fastest" nature of the formula with this fast VBA approach (linear with no loops).

You could optimize further by turning off calculation before setting the formula; and turning it back on afterwards.

For the scenario where you replace the formulas to values, you could improve it by not using the Windows Clipboard:

Change this:
With Range("B2:B" & LRA&)
.FormulaR1C1 = "=LOOKUP(32767,FIND(R2C[2]:R" & LRD& & "C[2],RC[-1]),R2C[3]:R" & LRD& & "C[3])"
.Copy
.Value = .Value
End With

To:
With Range("B2:B" & LRA&)
.FormulaR1C1 = "=LOOKUP(32767,FIND(R2C[2]:R" & LRD& & "C[2],RC[-1]),R2C[3]:R" & LRD& & "C[3])"
.Copy .Value
End With


Good job!

And you have a nice day as well.
Daniel

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

So, here is the list of best thus far including macros:


Formulas all based on Barry Houdini's approach:

Shortest at 39 characters (without names):
=LOOKUP(9^6,FIND(D$2:D$10,A2),E$2:E$10)

Quickest:
=LOOKUP(32767,FIND(D$2:D$10,A2),E$2:E$10)

Most Convienent with Dynamic Ranges for the tables:
=LOOKUP(9^6,FIND(KEY,A2),ASSIGN)<!-- / message -->


Quickest VBA based on stanleydgromjr's approach:

<CODE>
Option Explicit
Sub ColorAssignedTo()
Dim LRA&
Dim LRD&
with Application
.ScreenUpdating = False
.Calculation =xlCalculationManual
LRA& = Cells(Rows.Count, 1).End(xlUp).Row
LRD& = Cells(Rows.Count, 4).End(xlUp).Row
With Range("B2:B" & LRA&)
.FormulaR1C1 = "=LOOKUP(32767,FIND(R2C[2]:R" & LRD& & "C[2],RC[-1]),R2C[3]:R" & LRD& & "C[3])"
'.Copy .Value
End With
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
</CODE>