Solved: Searching for multiple values within cell

[herzberg]

Hi all,

I've have this complex problem here (or so I think) and I'd appreciate if anyone can help. It's gonna be a long explanation, so please bear with me.

I've got 1 column worth of data and each cell contains multiple values seperated by commas. Depending on what the cell contains, I'll need to return a value to the next column.

For illustration's sake, I have 10 rows in Column A, each cell (apart from the header row) has a combination of the following: Chicken, Apples, Duck, Pies, Cakes, Fish, Beef. In column B, the corresponding row will return values based on the following criteria:
Beef, Chicken, Duck, Fish = Meat present. In other words, if any cells in Column A has any of the meat items, the corresponding cell in Column B would show "Meat present".

So, for instance, if Range("A2") has the string "Cakes, Chicken, Pies", Range("B2") should show "Meat present", since "Chicken" is there. On the other hand, if the value in Range("A2") is "Pies, Apples", then Range("B2") should show "No meat", since none of the meat items are found. Note that there is no specific arrangement of the values in Column A, i.e. 1 cell may show "Cakes, Chicken, Pies", while another may show "Chicken, Pies, Cakes"; both should show the same value in Column B.

For a human to do this job, it's intuitively simple: just check the cell for any meat items and return the appropriate value. However, the sheer number of rows in my actual worksheet makes it tedious for human effort and I want to automate this.

Any suggestions on how I should go about approaching this problem? I'd be grateful for any ideas or solutions. Thanks!

[unmarkedhelicopter]

=if(find("Beef",A2)+find("Chicken",A2)+find("Duck",A2)+find("Fish",A2)>0,"M eat Present","No Meat")

I don't understand why there is a space in "Meat Present" cos I've checked twice, there's not ! ...

[herzberg]

=if(find("Beef",A2)+find("Chicken",A2)+find("Duck",A2)+find("Fish",A2)>0,"M eat Present","No Meat")

I don't understand why there is a space in "Meat Present" cos I've checked twice, there's not ! ...

Thanks for the reply! I tried it out and it returned me a "#VALUE!" error. It turns out that if the first value - in this case, Beef - is not found, a #VALUE! error is returned. Subsequently, even if there are any found values, it'll still return a #VALUE! error.

What I'm doing for the time being is this:

[vba] With ActiveSheet
For Counter = 2 To 10

TextLength = Len(.Cells(Counter, 1).Value)

For TextCounter = 1 To TextLength
If Mid(.Cells(Counter, 1).Value, TextCounter, 1) = "B" Then
.Cells(Counter, 2).Value = "Meat present"
ElseIf Mid(.Cells(Counter, 1).Value, TextCounter, 1) = "C" Then
If Mid(.Cells(Counter, 1).Value, TextCounter + 1, 1) = "h" Then
.Cells(Counter, 2).Value = "Meat present"
End If
'
'
'
'
'So forth
Next TextCounter
Next Counter
End With
[/vba]
What it does basically is to search for a 1st letter of a particular word and then returns the value accordingly. It gets the job done but the number of nested If..Else statements are numerous and complex, as there are many words that begins the same way for my real data set.

Hence, I'm looking to see if there's a simpler way to achieve the same outcome.

[Charlize]

Try this one : [VBA]Sub To_Meat_Or_Not_To_Meat()
Dim cell As Range
Dim rng As Range
Dim vMeat As Boolean
Dim vMenu As Variant
Dim vLoop As Long
Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In rng
vMeat = False
vMenu = Split(cell, ",")
For vLoop = LBound(vMenu) To UBound(vMenu)
Select Case Trim(vMenu(vLoop))
Case "Beef", "Chicken", "Duck", "Fish"
vMeat = True
cell.Offset(, 1).Value = "Meat"
Exit For
End Select
Next vLoop
If vMeat = False Then cell.Offset(, 1) = "No Meat"
Next cell
End Sub[/VBA]

[johnske]

[VBA]Option Explicit
'
Option Compare Text
'
Sub CheckForMeat()
'
Dim Cell As Range
'
For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If Cell Like "*beef*" Or Cell Like "*chicken*" Or Cell Like "*duck*" Or Cell Like "*fish*" Then
Cell.Offset(0, 1) = "Meat present"
Else
Cell.Offset(0, 1) = "No Meat"
End If
Next
End Sub[/VBA]

[unmarkedhelicopter]

=if(find("Beef",A2)+find("Chicken",A2)+find("Duck",A2)+find("Fish",A2)>0,"M eat Present","No Meat")

Bugger, I keep writting formulae without trying them out first, well I hate to go against tradition :-

if(if(iserror(find("Beef",A2)),0,1)+if(iserror(find("Chicken",A2)),0,1)+if(iserror(find("Duck",A2)),0,1)+if(iserror(find("Fish",A2)),0,1)>0,"Meat Present","No Meat")

This should be quicker than a UDF, does not need updating like a sub and you don't have to have macros running.
Though it does need "Beef, Chicken, Duck & Fish" to be written PROPER (i.e. Capital first letter only) though we can get round that if it's a problem.

Why oh why does FIND not return a zero if not found ??????

[mdmackillop]

Though it does need "Beef, Chicken, Duck & Fish" to be written PROPER (i.e. Capital first letter only) though we can get round that if it's a problem.

How about Search, it's not case sensitive.

[Shazam]

You could try using mdmackillop suggestion.

Example:

=IF(ISERROR(SEARCH("Chicken",A2)),"No meat","Meat present")

Or for multiple searches.


=IF(COUNT(SEARCH({"Chicken","Beef","Fish"},A2)),"Meat present","No meat")

[unmarkedhelicopter]

You could try using mdmackillop suggestion.

Example:

=IF(ISERROR(SEARCH("Chicken",A2)),"No meat","Meat present")

Or for multiple searches.


=IF(COUNT(SEARCH({"Chicken","Beef","Fish"},A2)),"Meat present","No meat")

I'll buy one of these please

[herzberg]

Thanks for all the replies so far! I wished I could reply earlier but I was out of office for the past few days and couldn't try out the solutions that you guys provided.

I've tried out Charlize's code and it works like a charm. I've also tried out the code suggested by johnske but it always returned me the first condition, i.e. "Meat present".

As for the formula-based solutions, I gave them a shot and they worked too. I've amended my code and currently using the following:

[vba]With ActiveSheet
RowCount = .Cells(1, 1).End(xlDown).Row
Set Activerg = .Range(Cells(2, 2), Cells(RowCount, 2))
Activerg.FormulaR1C1 = "=IF(COUNT(SEARCH({""beef"",""duck"",""chicken"",""fish""},RC[-1])),
_""Meat present"", ""No meat"")"
End with[/vba]
The formula is a lot longer in my actual code, since there are 9 conditions that needs to be checked but everything works out smoothly anyway.

Once again, thanks for the suggestions!