oraclefrontovik

Sharing what I learn

This is my first post on Excel and is an aide-mémoire as I know this will come in useful to a future me and if it helps other people then that is even better.

I recently needed to compare two columns of data in Excel and identify differences between them. For this example I will use the first 20 values from the Fibonacci sequence. Below is the  two columns with data that should be identical:

Fib1-Copy

However it doesn’t take long to see the values differ on row 15. If this data set was twice as long or had more inconsistencies the chances of me missing something will increase. So over to Excel to do the heavy lifting of comparing these columns and flagging any differences.

A quick review of the available functions within Excel and the IF function looks like it will be a good starting point and after some experiments I settled on the following:

=IF(A1=C1, "Match", "ERROR")

What this formula will do is; if Row 1, Column A1 matches row 1 of C1 I will see the text Match and if they do not I will see ERROR.

After adding to a new column, I can see that for the row where the data is different, Excel reports ERROR.

Fib2-Copy

This is a good start but I could still miss the ERROR, so I really would like it to stand out. One method is to achieve this is to use Excel’s Conditional Formatting.

You can find the Conditional Formatting on the Home tab of the Ribbon.

Fib3

First select all the values in column E, the ones with either Match or Error then select Conditional Formatting followed by Highlight Cell Rules and finally Text that Contains… which will bring up the following dialog box:

Fib5

I am most interested in cells that contain the ERROR text so I enter that in the text box and left the default colour choices.  After pressing OK I see that the ERROR now stands out.

Fib6

I hope you found this useful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: