Solved: Formula to help identify specific words in titles?

[jasonr704]

I am looking for the best way to identify colors in titles and then if a color is identified put that color name into a separate column. Multiple colors could be included in the same cell, separated by a comma. If no color exists then the cell if left blank.

For example:
Product Name #1: Cosmos Green & White case for Samsung Gravity
Product Name #2: Cosmos Green case for Samsung Gravity

(List of colors: white, blue, green, black, yellow, red, purple)


A1: Product Name | A2:Colors
Product #1 name | Green , White
Product #2 name | Green

Any ideas for how best to accomplish this?

[Shred Dude]

Paste this code in a module and then use the UDF getcolors() in the column you want the colors to appear in

Public Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
IsInArray = (UBound(Filter(arr, valueToFind)) > -1)
End Function

Public Function getColors(strprodname As String) As String
Dim arrColors()
Dim strColors As String
Dim x As Variant
Dim n As Integer

arrColors = Array("white", "blue", "green", "black", "yellow", "red", "purple")

x = Split(strprodname, " ")
For n = 0 To UBound(x)
If IsInArray(arrColors, LCase(x(n))) Then
strColors = strColors & x(n) & " , "
End If
Next n

If Len(strColors) > 0 Then
strColors = Trim(Left(strColors, InStrRev(strColors, ",") - 1))
End If

getColors = strColors
End Function

for example, in cell a2:


=getcolors(a1)

[jasonr704]

That worked really well! Thanks.
Actually couple of interesting things that popped up when I ran it:

1) Celtic Cross on Black designed skin for Amazon Kindle 2 returned the result:
on , Black

2) da Vinci - Sketch of a roaring lion designed skin for Amazon Kindle 2 returned the result: a

3) Hello Kitty - On a Cloud designed skin for Amazon Kindle 2 returned the result: On , a

Not sure if there's a way to get rid of those unwanted results?
Thanks again for the help. You are one smart cookie!

[Shred Dude]

I can't explain the "On" making it into the results unless you've added a color to the arrColors variable that contains the letters "on" in sequence. Blonde?

This is a bit sloppy, assuming you'll have no single or two lettered colors, we could go with this:


Public Function getColors(strprodname As String) As String
Dim arrColors()
Dim strColors As String
Dim x As Variant
Dim n As Integer

arrColors = Array("white", "blue", "green", "black", "yellow", "red", "purple")

x = Split(strprodname, " ")
For n = 0 To UBound(x)
If Len(x(n)) > 2 Then
If IsInArray(arrColors, LCase(x(n))) Then
strColors = strColors & x(n) & " , "
End If
End If
Next n

If Len(strColors) > 0 Then
strColors = Trim(Left(strColors, InStrRev(strColors, ",") - 1))
End If

getColors = strColors
End Function

Hope that helps...

[mdmackillop]

A slightly different function?

Public Function IsInArray(arr As Variant, valueToFind As Variant) As Boolean
IsInArray = Not IsError(Application.Match(valueToFind, arr, 0))
End Function

[jasonr704]

That worked great! Thanks for the help.

[Cyberdude]

And they said MD is just another pretty face . . .

Sid

[jasonr704]

yes, strikingly handsome in addition to brilliant!

[jasonr704]

One more question for you brilliant minds :)
what if I wanted to reference a second worksheet of colors instead of listing them individually in the macro? My list of colors could grow to over 40 and there's some mispellings I am trying to include in the results.

Let's assume the ref worksheet name is ColorsList.
Also, For each color, I'd like to include some possible mispellings. Solet's say there's up to 10 columns total:

A | B | C | D | E | F | G | H | I | J |

1) Blue | Bleu | Blu |
2) Navy Blue | Nave Blue | Navy Bleu | Navi Blue | navy blue
3) Forest Green | Forst Green | Forest Grene | Forest Greeen | forestgreen

So now, if any color is a match within each row, all keywords in that row get entered into the cell. forest green, forst green, forest grene, forest green, forestgreen, green, forest, greeen, forst

Each row may contain different number of additional keywords as shown below.

Is this even possible to do in a macro? Maybe I have to break out the 2 word colors into individual columns first before those can get entered as well?

[jasonr704]

I will definitely send a beer (or 6) to the guy who can help me with this one!

[mdmackillop]

Can you post some sample data.

[jasonr704]

yes, here's a sample workbook for you. I left the previous macro from above in Column C and added an example of how the additional keywords should ideally look in Column D.

Really not sure how to work this into a macro. doesn't have to be the same one, could be a new one altogether. The key to what I'm trying to do is that if any color variation matches what's in the product name, then all keywords get combined into the result in Column D.

Thanks a million for taking a look!

[mdmackillop]

Best guess!

[jasonr704]

unfortunately i'm just seeing #VALUE! errors.

[mdmackillop]

Try downloading again, I tidied up the code.

[jasonr704]

unfortunately same thing. attached the screen shot

[mdmackillop]

You should see this. Sorry, I need to leave this for now. Away til tuesday

[jasonr704]

ok. thanks for the help.
is there a quick way to "refresh" or "restart" the macro?

[jasonr704]

We'll I got it to work, but it was very strange. all i did was change =getcolors(B2) to =getcolors(B1) and changed it back to =getcolors(B2)
and that seemed to "refresh" it. so it looks great now!