If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I compare the stored data in micro
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:PTDEC 2009 Column D 10 V Street I want column A and D matched as is and the column B’s first two digits (12) and last four digits (2009) should be matched with column C’s 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. |
#2
|
|||
|
|||
How do I compare the stored data in micro
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:PTDEC 2009 Column D 10 V Street I want column A and D matched as is and the column B’s first two digits (12) and last four digits (2009) should be matched with column C’s 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. |
#3
|
|||
|
|||
How do I compare the stored data in micro
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:PTDEC 2009 Column D 10 V Street I want column A and D matched as is and the column B’s first two digits (12) and last four digits (2009) should be matched with column C’s 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. |
#4
|
|||
|
|||
How do I compare the stored data in micro
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:PTDEC 2009 Column D 10 V Street I want column A and D matched as is and the column B’s first two digits (12) and last four digits (2009) should be matched with column C’s 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. |
Thread Tools | |
Display Modes | |
|
|