Archive for May, 2011

Sometimes you just need to quickly identify cells in column B that don’t match the values in the corresponding rows in column A. Sure, you can define Conditional Formatting rules to identify these rows, but for a quick & dirty fix, try using one of the often overlooked “Go To… Special” functions.

Assume that you’re comparing Column A, Rows 1 through 50 to Column B, Rows 1 through 50.

* Select both columns of data A1:B50 (i.e. every cell in both columns that contain names to be compared).

* Click F5 to bring up the “Go To” dialog.

* Click the “Special…” button.

* Select “Row differences” option, then click OK.

* This will “select” every cell in column 2 that is different from the same row’s value in column 1.

* While the cells are still selected, just select a “Fill Color” from the ribbon and every mismatched cell in column 2 will be highlighted with that fill color.


* While the cells are still selected, press CTRL-C to copy them, then right-click on Cell C1 and select “Paste Special…” from the pop-up menu. This will bring up the “Paste Special” dialog. Click the “Skip Blanks” check box, then click OK. Column C will be populated with only the cells in column B that were mismatched.


Read Full Post »