WINNERS ANNOUNCEMENT June/July 2008 Challenge of the Month

Richard Schollar · Mar 13, 2009

Re: June/July 2008 Challenge of the Month

Hi Everyone

It will come as no surprise to anyone that in 1st place was the great Barry Houdini with his superlative Lookup formula.

The full list of winners and honourable mentions can be found here (there are quite a few of them!):

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CRichard%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CRichard%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CRichard%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; mso-themecolor:hyperlink; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color

urple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>[FONT=&quot]http://www.mrexcel.com/pc18.shtml[/FONT]

Well done to everyone who took part!

I have it on good authority that another Challenge will be posted shortly and this time the timescales will be somewhat tighter

Malix · Mar 14, 2009

Follwing is my solution towards previous chellange. I am new at forum and need inspitaion from all of you.

=TRIM(IF(ISNUMBER(FIND("blue",A2))=TRUE,"Joe","")&IF(ISNUMBER(FIND("red",A2))=TRUE,"Bob","")&IF(ISNUMBER(FIND("yellow",A2))=TRUE,"Mary","")&IF(ISNUMBER(FIND("pink",A2))=TRUE,"Fred","")&IF(ISNUMBER(FIND("orange",A2))=TRUE,"Ralph","")&IF(ISNUMBER(FIND("brown",A2))=TRUE,"Lora","")&IF(ISNUMBER(FIND("white",A2))=TRUE,"Tracy","")&IF(ISNUMBER(FIND("lavendar",A2))=TRUE,"Earl","")&IF(ISNUMBER(FIND("magenta",A2))=TRUE,"Jenny",""))

Regards
Arshad

barry houdini · Mar 14, 2009

Re: June/July 2008 Challenge of the Month

RichardSchollar said:
......Barry Houdini with his superlative Lookup formula.

OK, I'm officially flattered, thanks Bill...but I have to say that the solution is by no means original, I probably picked it up from somebody on this board, most likely Aladin.......

So thanks to Aladin and everybody who has contributed to my ongoing Excel education here, and everybody else who participated in the Challenge. I'm already looking forward to the next one......

Salvita · Apr 7, 2009

Re: June/July 2008 Challenge of the Month

Kudos, Barry!

I just found this site yesterday and so far, I have learned so much!

I had a tough time trying to understand Houdini's solution, but finally did it, mostly thanks to Rhino Dance's post #157.

There is still one issue that eludes me: control-shift-enter. What is this for?

I know I am late, but would like to offer this macro as the WORST offered here, but still, I wrote it, so here it is:

Public Sub SOLUTION()
Dim renPhrases As Integer
Dim renKey As Integer
Dim nKey As Integer
Dim nPhrases As Integer
Dim color As String
Dim Assigned As String
Dim phrase As String
renPhrases = Range("A65536").End(xlUp).Row
renKey = Range("D65536").End(xlUp).Row
For nKey = 2 To renKey
color = Cells(nKey, 4)
Assigned = Cells(nKey, 5)

For nPhrases = 2 To renPhrases
phrase = Cells(nPhrases, 1)
If Cells(nPhrases, 2) = "" Then
Cells(nPhrases, 2) = "=IF(ISERROR(FIND(" & """" & color & """" & "," & """" & phrase & """" & ")),"""",1)"
If Cells(nPhrases, 2) = 1 Then
Cells(nPhrases, 2) = Assigned
Else: Cells(nPhrases, 2) = ""
End If
End If
Next nPhrases
Next nKey
End Sub

Domski · Apr 7, 2009

Re: June/July 2008 Challenge of the Month

There is still one issue that eludes me: control-shift-enter. What is this for?

It's for confirming a formula as what is known as a CSE or more commonly Array Formula. These work differently to other formula (other than maybe Sumproduct and some others in 2007) and as such Excel requires that you tell it as such that you are creating one.

There's a good intro to them here: http://www.cpearson.com/excel/ArrayFormulas.aspx

Dom

Hooseria · May 8, 2009

Hi, Just bought Book, by Jelen and Systad, discovered your Website (Wow!!) anyway I know this challenge is old. Don't understand why a VLookup doesn't work, heres a user adaptable version of my Lookup and an explanation of why I used each formula...... Would like to hear some comments about it.

Look forward to the next challenge.

={VLOOKUP((OFFSET($E$3,MATCH(TRUE,ISNUMBER(SEARCH(Keyword,B3)),0)-1,0)),Table,2,FALSE)}

1.) Enter new enteries within borders
2.) Copy Formula's down in column C to adjacent new entries in Column B
A.) Firstly I needed to extract the colours from the Phrases, then use vlookup to look it up in the adjacent table
B.) Two named ranges, 1 for extracting the colour, and second for the vlookup.

Keyword is the named range for the colours and and Table for the lookup table Column ("Keyword and Assigned to")

....How do you attach a file to a post??

Domski · May 8, 2009

Hi & welcome,

....How do you attach a file to a post??

You can't I'm afraid. Only way is to pop it on a hosting site and post the link.

Dom

Hooseria · May 8, 2009

Domski

Thanks, Could you reccomend a hosting site, I'm eager to get a response on my answer to the latest challenge. Will stick it in the Hosting site. Would be great for future threads as well.

Cheers

Domski · May 8, 2009

I don't use them myself but you could try www.box.net which I know a few folk use.

Hooseria · May 8, 2009

Domski

Thanks, Great site, Here's the attachment of my Answer to June Challenge Using a Vlookup of the Extracted Colour based on the Adjacent Table.
http://www.box.net/shared/rqcsgla6rf
http://www.box.net/shared/rqcsgla6rf

WINNERS ANNOUNCEMENT June/July 2008 Challenge of the Month

Richard Schollar

MrExcel MVP

Excel Facts

Malix

New Member

barry houdini

MrExcel MVP

Salvita

New Member

Domski

Well-known Member

Hooseria

New Member

Domski

Well-known Member

Hooseria

New Member

Domski

Well-known Member

Hooseria

New Member

Forum statistics

Share this page

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

MrExcel MVP

Excel Facts

New Member

MrExcel MVP

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

WINNERS ANNOUNCEMENT June/July 2008 Challenge of the Month