Thread: Formula to Compare 2 columns and Manipulate the Resulting Data and Output

You can't do what you're looking for without either a helper column or a vba macro.

If you have formulas going down column B, such as:

=IF(B1=A1,"","No match: "&B1)

and you type or paste something into B1, it will overwrite that formula.

Your options are to paste your data into B1 and in C1 use a formula such as:

=IF(B1=A1,"","No match: "&B1)

You could then hide column B if you prefer.

Or, write a vba macro, which wouldn't require you to use a helper column (the macro could parse through the column and update the cells as necessary).

A macro like this should handle it if you don't want to use a helper column:

Sub FindMatches()
Dim Row As Integer, iLastRow As Integer

    iLastRow = Range("B65536").End(xlUp).Row
    
    For Row = 1 To iLastRow
            If Cells(Row, 2).Value <> Cells(Row, 1).Value Then
                Cells(Row, 2) = "No match: " & Cells(Row, 2).Value
            Else
                Cells(Row, 2) = ""
            End If
    Next Row
        
End Sub

As always, before running any macro on your data, make a backup copy of your file or use test data.

Actually, you don't want to put this into a Worksheet_Change or SelectionChange event, otherwise it will continuously update and you'll end up with huge strings in column B (e.g. No Match: 77 No Match: 77 No Match: 77....)

Keep the code below as a separate function (I modified it to check Column A for the bottom row of text rather than column B so it checks every cell in column B that has a value in Column A.)

Sub FindMatches()
    Dim Row As Integer, iLastRow As Integer

    iLastRow = Range("A65536").End(xlUp).Row
    
    For Row = 1 To iLastRow
            If Cells(Row, 2).Value <> Cells(Row, 1).Value Then
                Cells(Row, 2) = "No match: " & Cells(Row, 2).Value
            Else
                Cells(Row, 2) = ""
            End If
    Next Row
End Sub

To insert this code in Excel 2007, use either the Developer ribbon bar or simply press ALT+F11 to open the Visual Basic editor.
Next, expand the 'VBA Projects (yourworkbookname)' tree until you see 'Sheet1', 'Sheet2', 'ThisWorkbook'.
Next, double-click on the sheet you want the code to run on and paste this code into the whitespace on the right.
Finally, close the VBA window to return to Excel.

Your options for running the macro are:
1) Manually through the Macros window (View or Developer ribbon bars, or press ALT+F8)

2) This option looks longer, but after the initial setup it will save you time in the future... Attach the macro to a command button on your sheet to run it at the click of a button. On the Developer ribbon bar, in the Controls section, click the Insert button and select the Command Button icon (should be first one under Form controls). Draw a rectangle on the sheet where you want the button. Once you do, you should be prompted with the 'Assign Macro' window. Select your 'FindMatches' macro and click OK. You can then click on the button and change the text shown to something other than "Button 1". Turn off Design Mode by clicking the button in the Developer-Controls ribbon bar and you're all set in the future to type or paste data into column B then click your button.

Hi Luu... this modified code should fix both issues..

Sub FindMatches()
    Dim Row As Integer, iLastRow As Integer

    iLastRow = Range("A65536").End(xlUp).Row
        
    For Row = 1 To iLastRow
        If Cells(Row, 2).Value = "" Or _
        Cells(Row, 2).Value = Cells(Row, 1).Value Then
            Cells(Row, 2).Value = ""
        ElseIf Cells(Row, 2).Value <> Cells(Row, 1).Value And _
        Left(Cells(Row, 2).Value, 8) <> "No match" Then
            Cells(Row, 2).Value = "No match: " & Cells(Row, 2).Value
        End If
    Next Row
End Sub

Let me know how it goes.

Hi pjoaquin,

Awesome! That modification worked perfectly for all changes to column B!

Ugh! I didn't test it thoroughly enough the first time. I noticed an additional possibility which causes an issue.

If I change the data in column A, the spreadsheet will add additional "No Match:" "No Match:", etc. to all the rows in column B.

Can you make an additional modification so that if the data in A is changed, that row for B will be re-scanned and modified without repeating "No Matches:" and without affecting the other cells and rows in the columns?

Thank you soo much!!!

That's odd, since I don't get that result at all. If I change the data in column A it doesn't update column B, but it also doesn't add "No match:" to any of the cells in B.

For example, if A1 = "abc" and B1 = "acb", upon running the macro I get "No match: acb" in B1. I can run it ten times and nothing will change. Now when I change A1 to "acb" and run the macro, it still doesn't update B1 to show blank.

I'll look into it a bit and see what I can find out.

Left(Cells(Row, 2).Value, 8) <> "(s: " Then
            Cells(Row, 2).Value = "(s: " & Cells(Row, 2).Value & ")"

The problem isn't the parentheses, it's the length you're checking against. In my original formula it would add "No match: " to the left of the value, so I checked the first 8 characters for "No match". Now that you're just using "(s: " to the left of your value, change the 8 to 4, e.g.

Left(Cells(Row, 2).Value, 4) <> "(s: " Then
     Cells(Row, 2).Value = "(s: " & Cells(Row, 2).Value & ")"

Give this code a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Row As Integer, iLastRow As Integer

    iLastRow = Range("A65536").End(xlUp).Row
            
    Application.EnableEvents = False
    
    For Row = 1 To iLastRow
        If Cells(Row, 2).Value = "" Or _
        Cells(Row, 2).Value = Cells(Row, 1).Value Or _
        Cells(Row, 2).Value = "(s: " & Cells(Row, 1).Value & ")" Then
            Cells(Row, 2).Value = ""
        ElseIf Cells(Row, 2).Value <> Cells(Row, 1).Value And _
        Left(Cells(Row, 2).Value, 4) <> "(s: " Then
            Cells(Row, 2).Value = "(s: " & Cells(Row, 2).Value & ")"
        End If
    Next Row
    
    Application.EnableEvents = True
End Sub

I added or changed the items in red font (if you just copy and paste the entire code over your current code that will suffice).

I put it into the Worksheet_Change event, but had to add Application.EnableEvents = FALSE & Application.EnableEvents = TRUE to avoid an infinite loop of changes. I also added the third test to the first section of the If routine, to check if the value in column A has been changed to the value in column B with the "(s: " and ")" wrapped around it.

Let me know how it goes..