How can I tell what's different between 2 spreadsheets?

Factual Questions
1

I’ve go a spreadsheet (v. 1) (single “sheet”, but 1000 lines long) that I modified from home. (v. 2a)
Then I modified v.1 at work, to get (v.2b)

Some lines were added and some cells changed in each case, and I’m pretty sure some of the changes are going to fall in the same rows.

Is there an automatic way to tell where the differences are so I can try to match them up?

2

Got FileMaker? Let FileMaker do it for you.

Open v. 2a in FileMaker to create database V2aTable.fp5

Open v. 2b in FileMaker to create database V2bTable.fp5

(If you are not using Excel for your spreadsheet, you may first need to save a copy as SYLK or tab-delineated text. If it’s Excel, FileMaker will import it directly).

Define a new field in each of the two databases, Row, type number. Do a Replace and replace with serial values starting with 1 in each database.

Define a new relationship within V2BTable, let’s call it “Corresponding”, to the other file, based on linking the fields RecID to each other.

Define a new field in V2BTable, calc, type text, defined as:

Case(Col1&Col2&Col3&Col4&Col5&Col6&Col7 <> Corresponding::Col1&Corresponding::Col2 & Corresponding::Col3&Corresponding::Col4 & Corresponding::Col5&Corresponding::Col6 & Corresponding::Col7, “DISCREPANCY”, “SAME”)

Now every row containing a difference between the two sheets is marked with a “DISCREPANCY” flag.

You can also display the Sheet 2 values directly below the Sheet 1 values in each row, one in red and one in blue or something of that nature, if you wish.

3

On second thought, that’s silly. Your spreadsheet program should be able to do it for you natively, and more easily. (Hey, I work in FileMaker all the time; when you’re always holding the hammer everything starts to look like a nail…)

Move to the right of your rightmost occupied column, enter the cell on row 1 of that column, and define a calc formula such that If [use the function wizard thingie and reference cell A1 of the corresponding spreadsheet] = A1, “SAME”, “DIFFERENT”.

Now select as many cells to the right as you have occupied columns in your spreadsheet, and fill right. When it fills right it will automatically adjust the reference to A1 to A2 and then to A3 and so on, for both sheets.

Now you can see at a glance where the discrepancies between the two spreadsheets lie, on a cell by cell basis.