Function syntax to compare cell contents
 

I am trying to create a macro to compare the contents of cells in two
columns. I tried using the 'EXACT' syntax, but it doesn't seem to work for
the comparison I need to do.

Here is an example of the comparison I would like to perform:

cell a: apple, orange cell b: apple orange = true
cell a: apple, orange, pear cell b: apple, orange = true
cell a: apple, orange cell b: apple, pear = false

So the logic I am trying to capture is that if anything in cell b is foriegn
to cell a than the result is false. But if cell b contains data that is
found in cell a than the formula is true, even if cell a contains more data
than cell b.

My question is if it is possible to create a formula to solve this logic.

Thank you,
ES

Here's a shot

Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long, k As Long
Dim fA As Boolean, fB As Boolean
Dim aryB, aryA

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
aryA = Split(Replace(Cells(i, "A").Value, ",", ""), " ")
aryB = Split(Replace(Cells(i, "B").Value, ",", ""), " ")
For j = LBound(aryA) To UBound(aryA)
fA = False
For k = LBound(aryB) To UBound(aryB)
If aryA(j) = aryB(k) Then
fA = True
Exit For
End If
Next k
If Not fA Then Exit For
Next j
For j = LBound(aryB) To UBound(aryB)
fB = False
For k = LBound(aryA) To UBound(aryA)
If aryB(j) = aryA(k) Then
fB = True
Exit For
End If
Next k
If Not fB Then Exit For
Next j
If fA Or fB Then
Cells(i, "C").Value = True
Else
Cells(i, "C").Value = False
End If

Next i

End Sub

--
HTH

Bob Phillips

"ES" wrote in message
...
I am trying to create a macro to compare the contents of cells in two
columns. I tried using the 'EXACT' syntax, but it doesn't seem to work
for
the comparison I need to do.

Here is an example of the comparison I would like to perform:

cell a: apple, orange cell b: apple orange = true
cell a: apple, orange, pear cell b: apple, orange = true
cell a: apple, orange cell b: apple, pear = false

So the logic I am trying to capture is that if anything in cell b is
foriegn
to cell a than the result is false. But if cell b contains data that is
found in cell a than the formula is true, even if cell a contains more
data
than cell b.

My question is if it is possible to create a formula to solve this logic.

Thank you,
ES

Try using a combination of INSTR and AND

--
Don Guillett
SalesAid Software

"ES" wrote in message
...
I am trying to create a macro to compare the contents of cells in two
columns. I tried using the 'EXACT' syntax, but it doesn't seem to work
for
the comparison I need to do.

Here is an example of the comparison I would like to perform:

cell a: apple, orange cell b: apple orange = true
cell a: apple, orange, pear cell b: apple, orange = true
cell a: apple, orange cell b: apple, pear = false

So the logic I am trying to capture is that if anything in cell b is
foriegn
to cell a than the result is false. But if cell b contains data that is
found in cell a than the formula is true, even if cell a contains more
data
than cell b.

My question is if it is possible to create a formula to solve this logic.

Thank you,
ES