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

Re: June/July 2008 Challenge of the Month

Here you go.

Before my macro:

Excel Workbook
ABCDE
1PhrasesAssigned toKeywordAssigned to
2the ocean is blueblueJoe
3I like this blue shirtredBob
4blue is my favorite coloryellowMary
5the ocean is redpinkFred
6I like this red shirtorangeRalph
7red is my favorite colorbrownLora
8the ocean is yellowwhiteTracy
9I like this yellow shirtlavendarEarl
10yellow is my favorite colormagentaJeny
11the ocean is pink
12I like this pink shirt
13redditch
14the ocean is blue
15I like this blue shirt
16blue is my favorite color
17the ocean is red
18I like this red shirt
19red is my favorite color
20the ocean is yellow
21I like this yellow shirt
22yellow is my favorite color
23the ocean is pink
24I like this pink shirt
25redditch
26the ocean is blue
27I like this blue shirt
28blue is my favorite color
29the ocean is red
30I like this red shirt
31red is my favorite color
32the ocean is yellow
33I like this yellow shirt
34yellow is my favorite color
35the ocean is pink
36I like this pink shirt
37redditch
Sheet1



After the macro:

Excel Workbook
ABCDE
1PhrasesAssigned toKeywordAssigned to
2the ocean is blueJoeblueJoe
3I like this blue shirtJoeredBob
4blue is my favorite colorJoeyellowMary
5the ocean is redBobpinkFred
6I like this red shirtBoborangeRalph
7red is my favorite colorBobbrownLora
8the ocean is yellowMarywhiteTracy
9I like this yellow shirtMarylavendarEarl
10yellow is my favorite colorMarymagentaJeny
11the ocean is pinkFred
12I like this pink shirtFred
13redditchBob
14the ocean is blueJoe
15I like this blue shirtJoe
16blue is my favorite colorJoe
17the ocean is redBob
18I like this red shirtBob
19red is my favorite colorBob
20the ocean is yellowMary
21I like this yellow shirtMary
22yellow is my favorite colorMary
23the ocean is pinkFred
24I like this pink shirtFred
25redditchBob
26the ocean is blueJoe
27I like this blue shirtJoe
28blue is my favorite colorJoe
29the ocean is redBob
30I like this red shirtBob
31red is my favorite colorBob
32the ocean is yellowMary
33I like this yellow shirtMary
34yellow is my favorite colorMary
35the ocean is pinkFred
36I like this pink shirtFred
37redditchBob
Sheet1



Code: 
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Re: June/July 2008 Challenge of the Month

Daniel Ferry said:
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.
Click to expand...

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.
 
Re: June/July 2008 Challenge of the Month

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

:confused:
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.
 
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)))
 
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?
 
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
 
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)
 
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):

Code: 
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
 
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
 
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>
 
Last edited:
You must log in or register to reply here.

Forum statistics

Threads
1,216,500
Messages
6,131,014
Members
449,614
Latest member
indiglo

Share this page

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