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

[Randombard]

I know that the primary driving aspect of this formula has already been posted,

I have just added to it to make it more versatile.

=IF(ISERROR(LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)),"Not In List",LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10))

In no way can I take credit for the clever part of this formula, I just noticed that on those occations that the word in A1 was not in the lookup vector I got #N/A

[crimson_b1ade]

Challenge of the...Month?!? Last challenge was June/July 2008, and the previous challenge before that was March/April 2007. At that rate..."Challenge of the Year" would be a more fitting description.

[mgirvin]

I agree: change it to challenge of the year if it remains as is, or do us all a favor and change it back to challenge of the month!

[Malix]

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

[Hooseria]

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]

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]

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]

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

[Hooseria]

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

[Nilotna]

I now, it's too long but I'm starting
You can call this procedure from the WorkSheet_Activate and Worksheet_SelectionChange
*****************************
Public Sub Colorear()
Dim WB As Workbook
Dim Sh As Object
Dim RngColores As Range
Dim RngColor As Range
Dim RngFrases As Range
Dim RngCelda As Range
Dim rngAsignacion As Range
Dim DoFilaFrase As Double
Dim DoFilaColor As Double
Dim DoFinalRow As Double
Dim StPalabra As String
Dim StFrase As String
Dim InContador As Integer
Dim InCaracter As Integer
Dim InLongitud As Integer
Dim Asci As Integer
Set WB = ThisWorkbook
Set Sh = WB.Sheets("Sheet1")
'Determina el rango de Frases
DoFinalRow = Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row
Set RngFrases = Range(Sh.Cells(2, 1), Sh.Cells(DoFinalRow, 1))
Set rngAsignacion = Range(Sh.Cells(2, 2), Sh.Cells(DoFinalRow, 2))
rngAsignacion.Clear
'Determina el rango de Colores
DoFinalRow = Sh.Cells(Sh.Rows.Count, 4).End(xlUp).Row
Set RngColores = Sh.Range(Sh.Cells(2, 4), Sh.Cells(DoFinalRow, 4))
'Para cada frase
For Each RngCelda In RngFrases
DoFilaFrase = RngCelda.Row 'Anota la fila de la frase
StFrase = StreenCleaner(RngCelda.Value)
'Busca un espacio
InLongitud = Len(StFrase)
For InContador = 1 To InLongitud + 1
StCaracter = Mid(StFrase, InContador, 1)
If StCaracter = " " Or InContador > InLongitud Then
'Busca un nombre de color
Set RngColor = RngColores.Find(What:=StPalabra, LookIn:=xlValues, lookat:= _
xlWhole, searchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not RngColor Is Nothing Then 'Es un color
DoFilaColor = RngColor.Row 'Anota la fila del color
'Escribe en la fila de la frase la segunda columna del color
rngAsignacion(DoFilaFrase - 1, 1) = RngColores(DoFilaColor - 1, 2)
Else
StPalabra = ""
End If 'No es un color
Else
StPalabra = StPalabra & StCaracter
End If
Next InContador
StPalabra = ""
Next RngCelda
End Sub

Public Function StreenCleaner(Cadena As String)
Dim Contador As Integer
Dim Caracter As String
Dim CadenaLimpia As String
Dim Prueba As Integer
For Contador = 1 To Len(Cadena)
Caracter = Mid(Cadena, Contador, 1)
On Error GoTo ErrXEsp
If Asc(Caracter) > 128 Then Caracter = " "
Prueba = Asc(Caracter)
On Error GoTo 0
CadenaLimpia = CadenaLimpia & Caracter
Next
StreenCleaner = Trim(CadenaLimpia)
Exit Function
ErrXEsp:
Caracter = " "
Resume Next
End Function