Hi,

I got four columns of stored list of addresses and dates (two for address
and two for dates) which I want to match against each other. The formats of
those columns are in example below

Column A
10 V Street

Colum B
12/16/2009

Column C
12 DEC:PTD:DEC 2009

Column D
10 V Street

I want column A and D matched as is and the column Bs first two digits (12)
and last four digits (2009) should be matched with column Cs first two
digits (12) and the last four digits (2009). If they are not the same it
should appear differently (may be red text). Any help on this will greatly
appreciated.

hi
formulas return values to the cell in which they reside. they cannot perform
actions like change font colors.
this formula should work or at least it work on your example.

=IF(AND(A2=D2,MONTH(B2)=VALUE(LEFT(C2,2)),YEAR(B2) =VALUE(RIGHT(C2,4))),"matched", "no match")

careful. it wrapped.
you can use conditional formatting on the cell (E column?) to change the
color if Matched or another color if No Match.

regards
FSt1


"Help on formula" wrote:

Hi,

I got four columns of stored list of addresses and dates (two for address
and two for dates) which I want to match against each other. The formats of
those columns are in example below

Column A
10 V Street

Colum B
12/16/2009

Column C
12 DEC:PTD:DEC 2009

Column D
10 V Street

I want column A and D matched as is and the column Bs first two digits (12)
and last four digits (2009) should be matched with column Cs first two
digits (12) and the last four digits (2009). If they are not the same it
should appear differently (may be red text). Any help on this will greatly
appreciated.

hi
sorry. you mentioned macro in the subject but my slow brain keyed on your
long on name "help with formula".
Sub matchnomath()
Dim fc As String
Dim sc As String
Dim tc As String
Dim fcl As String
fc = Range("A2").Value
sc = Range("B2").Value
tc = Range("C2").Value
fcl = Range("D2").Value
If fc = fcl And _
Left(sc, 2) = Left(tc, 2) And _
Right(sc, 4) = Right(tc, 4) Then
MsgBox "Matched"
Range("A2").Resize(1, 4).Font.ColorIndex = 3 'red
Else
MsgBox "no match"
End If
End Sub

regards
FSt1

"FSt1" wrote:

hi
formulas return values to the cell in which they reside. they cannot perform
actions like change font colors.
this formula should work or at least it work on your example.

=IF(AND(A2=D2,MONTH(B2)=VALUE(LEFT(C2,2)),YEAR(B2) =VALUE(RIGHT(C2,4))),"matched", "no match")

careful. it wrapped.
you can use conditional formatting on the cell (E column?) to change the
color if Matched or another color if No Match.

regards
FSt1


"Help on formula" wrote:

Hi,

I got four columns of stored list of addresses and dates (two for address
and two for dates) which I want to match against each other. The formats of
those columns are in example below

Column A
10 V Street

Colum B
12/16/2009

Column C
12 DEC:PTD:DEC 2009

Column D
10 V Street

I want column A and D matched as is and the column Bs first two digits (12)
and last four digits (2009) should be matched with column Cs first two
digits (12) and the last four digits (2009). If they are not the same it
should appear differently (may be red text). Any help on this will greatly
appreciated.

Thank so much. It turns the text red only to the second row, although it's
matching.


hi
sorry. you mentioned macro in the subject but my slow brain keyed on your
long on name "help with formula".
Sub matchnomath()
Dim fc As String
Dim sc As String
Dim tc As String
Dim fcl As String
fc = Range("A2").Value
sc = Range("B2").Value
tc = Range("C2").Value
fcl = Range("D2").Value
If fc = fcl And _
Left(sc, 2) = Left(tc, 2) And _
Right(sc, 4) = Right(tc, 4) Then
MsgBox "Matched"
Range("A2").Resize(1, 4).Font.ColorIndex = 3 'red
Else
MsgBox "no match"
End If
End Sub

regards
FSt1

"FSt1" wrote:

hi
formulas return values to the cell in which they reside. they cannot perform
actions like change font colors.
this formula should work or at least it work on your example.

=IF(AND(A2=D2,MONTH(B2)=VALUE(LEFT(C2,2)),YEAR(B2) =VALUE(RIGHT(C2,4))),"matched", "no match")

careful. it wrapped.
you can use conditional formatting on the cell (E column?) to change the
color if Matched or another color if No Match.

regards
FSt1


"Help on formula" wrote:

Hi,

I got four columns of stored list of addresses and dates (two for address
and two for dates) which I want to match against each other. The formats of
those columns are in example below

Column A
10 V Street

Colum B
12/16/2009

Column C
12 DEC:PTD:DEC 2009

Column D
10 V Street

I want column A and D matched as is and the column Bs first two digits (12)
and last four digits (2009) should be matched with column Cs first two
digits (12) and the last four digits (2009). If they are not the same it
should appear differently (may be red text). Any help on this will greatly
appreciated.