return TRUE in Column B where Column A cell values is either BLANK, equals "To be Determined", "UNKNOWN" or contains COMMA ELSE False

[Ananthak275]

Trying to create Column B where values in Column A

the following criteria for TRUE:
1. Cell value equals "To be Determined" OR "UNKNOWN"
2. Cell value equals BLANK
3. Cell value contains COMMA

Column A	Column B
Unknown	True
Apple	False
To be Determined	True
	True
Trees	False
Apple, Trees	True
Trees, Apple	True
Orange	False

 

[Ananthak275]

UPDATE:
1. Cell value equals "To be Determined" OR "UNKNOWN"
2. Cell value equals BLANK
3. Cell value contains COMMA OR "/"

 

[etaf]

EDIT

how about
=OR(A2="to be determined", A2="unknown",A2="",NOT(ISERROR(SEARCH(",",A2,1))),NOT(ISERROR(SEARCH("/",A2,1))) )

Book4
	A	B	C
1	Column A	Column B
2	Unknown	TRUE	TRUE
3	Apple	FALSE	FALSE
4	To be Determined	TRUE	TRUE
5		TRUE	TRUE
6	Trees	FALSE	FALSE
7	Apple, Trees	TRUE	TRUE
8	Trees, Apple	TRUE	TRUE
9	Or/ange	FALSE	TRUE
10	Feed/jon		TRUE
11	orange		FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=OR(A2="to be determined", A2="unknown",A2="",NOT(ISERROR(SEARCH(",",A2,1))),NOT(ISERROR(SEARCH("/",A2,1))))

 

[Micron]

Fortheheckofit, I was working on a code solution ('cause that's my bent) and was using

If rngValue = "To be Determined" Or rngValue = "UNKNOWN" Or rngValue = "" Or InStr(1, rngValue, ",") > 0 Or InStr(1, rngValue, ",") > 0 Then
     testVal = "True"
Else
     testVal = "False"
End If

but thought, "surely I could use Select Case block instead, but how to do that when you can't perform tests (such as Instr) on a Case statement"? After one or two wobbly pops I think the answer came to me. I can test/post if anyone is interested - but warning - I'm not 100% sure I figured it out.

 

[My Aswer Is This]

Try this using select case:

Sub Select_Me()
'Modified  5/31/2022  11:42:59 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To Lastrow
    Select Case Cells(i, 1).Value
        Case "To be Determined", "UNKNOWN", ""
            Cells(i, 2).Value = True
        Case Else
            Cells(i, 2).Value = "False"
    End Select
        
    Select Case True
        Case InStr(Cells(i, 1).Value, ",") > 0
        Case InStr(Cells(i, 1).Value, "/") > 0
        
            Cells(i, 2).Value = "True"
    End Select
Next
End Sub

 

[Micron]

Might as well test for True in all cases? I did that using a function so that the entire column doesn't get calculated each time. Code is just 12 lines all inclusive. Not saying that this way is better, just different I guess.