Using IF in 3 columns

[Mr80s]

Hi Folks,

I'm sure this is pretty basic to some, but I'm trying to confirm the best way to go through all rows of data and if columns F, O and V contain specific strings then write something to column Z. Then if those same columns contain something else, write something to column AA

Basically:

• if F="a value" or "another value" AND O ="one of 6 values" AND V = "one of two values" then place Yes in Z,
• if F="a value" or "another value" or "a third value" AND O ="one of 6 values" AND V = "one value" then place Yes in Z,

ie:

Column F	Column O	Column V	Z	AA
Cat or Dog	Red or Black or Brown	house or condo	Yes
Cat or Snake	Red or Black or Brown	house		Yes

The # of rows will vary so will have to determine rows then search in Column F O and V for values.

Any starting ideas and I'll run with it from there.

Cheers,

 

[Joe4]

Here are a few threads that will show you the tools to use to do these things:

Using IF with AND, OR and NOT functions - Microsoft Support

How to use the IF function (combined with the AND, OR, and NOT functions) in Excel to make logical comparisons between given values.

support.office.com

Excel IF function with multiple conditions

Advanced Excel IF formula examples: multiple AND/OR criteria, nested IF statements, array formulas and more.

www.ablebits.com

 

[Mr80s]

Hi Joe4,

Thanks for pointing me in the direction of learning more. Always good to learn how to do something.
I'm wondering if each condition I'm looking for in my columns could be several things if I should put what I'm looking for into a table or array or something?

ie: Column F I'm looking for 1 of 4 text values, Column O i'm looking for any of 10 values and column V is one of two...

So it's if (F = Cat or Dog) (AND O = (Black or Brown or Red or Orange or Purple or Blue) AND (V = house or condo)) then write to Z

I'm thinking a very big if statement to capture all the different values or would it be more logical to put all the values of i'm looking for into a variable? ie:

Var1 = Cat or Dog
Var2 = Black or Brown or Red or Orange or Purple or Blue
Var3 = House, Condo

If (instr(Var1) And Instr(Var2) and Instr(Var3)) then....

Cheer,

 

[Joe4]

Column F I'm looking for 1 of 4 text values, Column O i'm looking for any of 10 values and column V is one of two...

Perhaps create lists for each thing (i.e. a list of the 4 possible text values for F, another list for the 10 possible values for O, etc).
Then you can use COUNTIF to see if it matches any of the values (each countif will either return 1 if there is a match, or 0 if there is not), i.e.
=IF(COUNTIF(your 1st list, F1)>0,...

 

[Mr80s]

Ok I may be way off and my lack of VBA showing...but something along the lines of:

Dim aValue As Variant
Dim aAltValue As Variant
Dim bValue As Variant
Dim cValue As Variant
Dim cAltValue As String
Dim ws As Worksheet

Dim F As Range
Dim O As Range
Dim V As Range

Set F = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set O = Range("B1", Range("B" & Rows.Count).End(xlUp))
Set V = Range("C1", Range("C" & Rows.Count).End(xlUp))

aValue = Array("Cat", "Dog")
aAltValue = Array("Cat", "Horse")
bValue = Array("Blue", "Brown", "Red")
cValue = Array("House", "Condo")
cAltValue = "House"

Set ws = Worksheets("Sheet1")
ws.Range(Z) = Application.WorksheetFunction.CountIf((ws.Range(F),aValue))>0, If(CountIF(ws.Range(O),bValue))>0, If(CountIF(ws.Range(V),cValue))>0))



' other stuff I was trying


'For rc = 2 To Rows.Count
'    If(CountIF(F, aValue))>0, If(CountIF(O, bValue))>0, If(CountIF(V, cValue))>0) Then "AB".F.Value = "Yes"

So what I'm trying to do is a little hard to explain.
I'm searching columns within each row of multiple rows. If a row in column A has "Cat" AND Col B has any value "Blue, Brown,Red" AND column C has any value of "House" or "Condo" then write "Yes" to that row's column Z

If A = "Dog" AND B = "blue" or "Brown" or "Red" AND C = "House" or "Condo" then write "yes" to that row's col Z
If A = "Horse" and B = "blue, brown or red" AND C = "House" then write "yes" to col AA



with an output like:

A	B	C	Z	AA
Cat	Blue	House	Yes
Dog	Red	Condo	Yes
Horse	Red	House		Yes
Cat	Brown	House	Yes
Cat	Pink	Condo

So I'm not really sure if it should be something like:

Set array of values for A, B & C :
aValue = Array("Cat", "Dog")
aAltValue = Array("Cat", "Horse")
bValue = Array("Blue", "Brown", "Red")
cValue = Array("House", "Condo")
cAltValue = "House"

Then create a loop through rows:
For rc = 2 to Rows.count

However, I'm not sure how to iterate through each row...

If (((CountIF("A"&rc, aValue))>0, if(CountIf("B"&rc,bValue))>0,If(CountIF("C"&rc,cValue))>0) then "Z"&rc.Value="yes"

Then run another if for colum C of only house?

I'm not sure if I'm explaining it properly and appreciate pointing me in the right direction so I can learn. I've search for IF's with multiple conditions and most examples use integers. I'm looking for if with text which I assuming means put those strings I'm looking for into an array. What I'm really struggling with is how to loop through each row and say if row 2 column A = and row 2 column B= and row 2 column C = then write to that row's col Z. Then do the same for all the other rows until end of rows.

Thanks for any and all help!

 

[Joe4]

I am a little confused. I thought you were looking for a formulaic solution.
Are you looking for a VBA solution?
Because I don't think you need to use VBA, if it is not necessary.

 

[Mr80s]

Yes I'm looking for a VBA solution as this is part of a bigger script that does some file manipulation and this section works on a newly created Sheet.

Regards,

 

[Joe4]

OK, if we can use VBA, I would approach this a different way. I would simply loop through each row and check the conditions for each one, like this:

Sub MyMacro()

    Dim r As Long
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting with row 1
    For r = 1 To lr
    
'       First check
        If (Cells(r, "A") = "Dog" Or Cells(r, "A") = "Cat") And _
          (Cells(r, "B") = "Blue" Or Cells(r, "B") = "Brown" Or Cells(r, "B") = "Red") And _
          (Cells(r, "C") = "House" Or Cells(r, "C") = "Condo") Then
'           Update column Z
            Cells(r, "Z") = "Yes"
        Else
'           Second check
            If Cells(r, "A") = "Horse" And _
              (Cells(r, "B") = "Blue" Or Cells(r, "B") = "Brown" Or Cells(r, "B") = "Red") And _
              Cells(r, "C") = "House" Then
'               Update column AA
                Cells(r, "AA") = "Yes"
            End If
        End If
    
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"

End Sub

 

[Mr80s]

Hey Joe4,

Thank you...this was the thing I was looking for:

If (Cells(r, "A") = "Dog" Or Cells(r, "A") = "Cat") And _

As I wasn't sure of the format for Cells(r,"A") <--the row variable. I was going with Cells(i, 1) and using column numbers. "A" is much better and easier to read.. Then of course where and how to place the "Or" and "And"...

If I have a lot of variables to check for for "colours" would it make sense to put those in an array and then how would you structure the "if" against the array?

ie:

Dim colourValue as Variant
colourValue = Array("Blue", "Brown", "Red","Pink","Green", "Black",) 
If (Cells(r, "A") = "Dog" Or Cells(r, "A") = "Cat") And _
  (Cells(r, "B") = colourValue) And _
......

Thank you very much for your help

 

[Joe4]

Yes, with Cells(row,column), you can use either the column letter, or the comparable column index (number).

If you want to try using arrays, take a look at these links here for ways to do that:

Check if a value is in an array or not with Excel VBA

I've got some code below, that is supposed to be checking if a value is in an Array or not. Sub test() vars1 = Array("Examples") vars2 = Array("Example") If IsInArray(Range("A1").Value,

stackoverflow.com

.

VBA Tip: Search in an Array

The inArray function for Excel (VBA) checks if a value exists in an array...

www.excel-pratique.com