Compare data within same cell

[Psyanide]

I require a macro code which compares data within same cell and accordingly provides status either "match found" or "mismatch"
I work on a discrepancy report and most of the property addresses are same only difference is abbreviations
eg. street = st, Road = rd and so on.

In the below example data on left side of "|||" is from 1 source and data on right side of "|||" is from other source
In the below example "A2" and "A3" are good to be deleted however "A4" has "drive" against "avenue" hence it needs to be verified.
Can you please provide me a macro code which would give me output as presented in "B" column based on the data on column "A"

Note: There are many such abbreviations I come across which I will add in the code by myself once I have the basic code.


[TABLE="width: 365"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Property Address[/TD]
[TD]Macro result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple Street ||| Apple St[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue Road colony ||| Blue Rd colony[/TD]
[TD]Delete[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sunshine Drive ||| Sunshine Avenue[/TD]
[TD]Check[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[wesborland1234]

Within the For Loop, just put an if statement to check if the current cell's value is blank. Something like this:

For currRow = 1 To maxRow
        address = Cells(currRow, 1).Value
        If address = "" or address = " " Then
           'Whatever you want to do with blank cells, if you just want to ignore them, leave this as is
        Else
        add1 = address1(address)
        add2 = address2(address)
        If replaceAll(LCase(add1)) = replaceAll(LCase(add2)) Then
            Cells(currRow, 2).Value = "Delete"
        Else
            Cells(currRow, 2).Value = "Verify"
        End If
       End If
    Next