Macro to compare two columns and if condition is met write to a co

[Chris]

Hi I am new to macros. I need to write a macro that compares text in two
different columns and if the condition is met will write a numerical number
to a third column. I can do this in excel will if statements but I am
limited to only 7 nested functions. Here is the example:

IF((AND(F1="word1",G1="on")),3,IF((AND(F1="word2",G1="on")),4,IF((AND(F1="word3",G1="on")),5,0)))

Thanks

 

[joel]

=if(G1="on",0,MATCH(F1,{"word1","word2","word3"},0)+2)

 

[joel]

I'm assuming the numbers will start with VIN8
number of matches is

=SUMPRODUCT(--(LEFT(A1:A100,14)="VIN81FTDF15YS),--(B1:B100="myarea"))

The toal is
=SUMPRODUCT(--(LEFT(A1:A100,14)="VIN81FTDF15YS),--(B1:B100="myarea"),C1:C100)

so the average would b
=SUMPRODUCT(--(LEFT(A1:A100,14)="VIN81FTDF15YS),--(B1:B100="myarea"),C1:C100)/B2

where b2 is the number of mattches

 

[Chris]

Joel, I think I did a bad job at explaining it. I want the macro to run
through two colums and compare words in both of them and if they meet the
criteria, insert a selected number. There are 13 Different Criteria's they
have to meet but if it doesnt meet any of these conditions it enters a 0. EX
Column 1 Column 2 Column 3
dog on 1
dog off 0
cat off 0
cat on 2
hat on 3
hat off 0
sat on 4
sat off 0

Thanks

 

[joel]

this formula works, but I can also do it with a macro

=IF(B7="on",IF(ISNA(MATCH(A7,{"dog","cat","hat","sat"},0)),0,MATCH(A7,{"dog","cat","hat","sat"},0)),0)

Sub CompareData()

Data = Array("dog", "cat", "hat", "sat")

RowCount = 1
Do While Range("A" & RowCount) <> ""
If UCase(Range("B" & RowCount)) = "OFF" Then
Range("C" & RowCount) = 0
Else
CellData = Range("A" & RowCount)

found = False
For ItemCount = 0 To UBound(Data)
If Data(ItemCount) = CellData Then
Range("C" & RowCount) = ItemCount + 1
found = True
Exit For
End If
Next ItemCount
If found = False Then
Range("C" & RowCount) = 0
End If
End If
RowCount = RowCount + 1
Loop


End Sub