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:

Please Login or Register  to view this content.

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.)

Please Login or Register  to view this content.

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..

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Give this code a try:

Please Login or Register  to view this content.

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..

Did you completely delete the old code that the most recent code was meant to replace? I have a feeling there are still references to 'Sub FindMatches()' and 'EndSub' wrapped around the latest code. If you want, go into your Sheet1 code and select EVERYTHING, then paste it back into the forum and we can check if you're not sure.

In the end, you shouldn't have any code except the code I provided last. The entire bit of code from 'Sub FindMatches()' to 'EndSub' in the original code should be deleted.

(Once the old code has been deleted, it should also no longer appear in the ALT+F8 window.)

Yes, it is supposed to update in real-time, as it does on my computer. You mentioned something about removing the 'EnableEvents' code. That could be a problem if you already ran Application.EnableEvents = False, but never ran Application.EnableEvents = True afterward. Excel would be sitting in a state where it cannot perform worksheet events.

For the heck of it, copy and paste this code into your vba window and run it (either from the vba window or through the macro list ALT+F8):

Please Login or Register  to view this content.

Then try re-running the latest code provided and let me know how it goes. If it still doesn't work right, you may need to post a zipped copy of your file so we can diagnose the problem.

The only way I can think to do this is if you stored the contents of column A either in another column (hidden, perhaps) or in an array variable in the macro. The next time the code ran (on any worksheet_change) it could check to see if column A had changed, and update that cell in column B accordingly.

I think an array would be cleaner, however I'm not familiar with coding array's and would ask some of the other folks here to assist if possible. Perhaps I'm reading too much into it, so another pair of eyes might be good anyway. Any takers out there?

I'll see what I can come up with, but hope another coder can assist in the mean-time.

For clarification, we're already checking to see
If cell Ax is blank, make cell Bx blank OR
If cell Bx is blank, make (leave) cell Bx blank OR
If cell Bx = cell Ax, make cell Bx blank OR
If cell Bx = "(s: " + Ax + ")", make cell Bx blank
Else
If cell Bx <> Ax AND Left(Bx),4 <> "(s: ", make cell Bx "(s: " + Bx + ")"

And now you're trying to add code that will check to see if the contents of cell Ax have changed, and if so, make Bx blank.

As an example, if A2 = "abc", and you enter "abc" into B2, B2 will turn blank upon pressing Enter.
If you had entered "bac" into cell B2, it would turn into (s: bac).

The current code will blank out cell B2 if you were to change A2 to "bac", but you're looking to blank it out ANY TIME you change the value in A2 (Ax)? It seems easy enough, but my mind just wonders why. (It does that a lot, don't be offended.)