Comparing Records in Excel

There are various times when it is useful to be able to compare the data in two or more separate records from the same table. For example when you have imported a change to a record, you should make a copy of it before you run your import which you can then use to compare with the same record after the import.

To make a copy before your import, run a sql query of the form:
select * from table where keyfield = ‘NameOfRecord’
and paste the output into a spreadsheet.

A good practice is to put the data you capture into a worksheet of the file containing the source for your import. Name the tab something along the lines of “pre-import data”.
Once you have run your import, you can use the same method to capture the data once more, and paste it into the source file in a new worksheet called (for example) “post-import data”.

Whatever else you do with this file, it now acts as a record of what change(s) you made, when (if you follow my file-naming convention), and the state of the data before and after the import.

Comparing the two sets of data

I use two different ways of comparing data, depending upon its volume.

For individual or small numbers of records:
  1. Paste the two sets of data into a temporary spreadsheet, one above the other.
  2. Highlight both records – select cell A1 and hit Shift-Ctrl-End – and Ctrl-C to copy them.
  3. Highlight the first cell – ie in column A – in the empty are below the data.
  4. Select Paste Special, and use the Transpose option. This will paste both records vertically side-by-side.
  5. Select AutoFilter on.
  6. Delete all the rows above your newly-pasted columns of data.
  7. Insert a blank column between the two records.
  8. In the top row of the blank column, enter the function =B1=E1 (adapt this function to the cell references of your actual data – I am assuming your source data consists of one row of field names and a one row of data).
  9. Copy the function and paste it in the same column all the way down to the bottom of the data.
  10. Using the autofilter deselect the value TRUE from view.

Whatever lines are left will show you changes made to the data.

For large volumes of data

This method assumes you have a file with two worksheets containing your pre-import and post-import data. The data should be arranged in the same order in each sheet – ie copied from the system in exactly the same order of rows and columns, and pasted into the same range in the worksheets.

  1. Create a new worksheet in the file containing your pre-import and post-import worksheets.
  2. In cell A1 put the formula
    =’pre-import data’!A1=’post-import data’!A1
    adapt the cell references if you have used different names for your worksheets.
  3. Copy A1 and paste it into the same range as covered in the data worksheets.
  4. The comparison worksheet will show you the cell references where there is a difference between the two data sets.

To increase the utility of this method, you can use conditional formatting to highlight the FALSE cells.
To increase the utility further, use the formula below, which will show you the two values side-by-side where they are different
=IF(‘pre-import data’!A1=’post-import data’!A1,1,’pre-import data’!A1&” : “&’post-import data’!A1)

As a final refinement, the first row of data in your pre and post sheets should contain the field names of the columns. If you post this first row into your checking worksheet, you can see instantly what column the changed data is in.