Example: finding missing items in a pair of spreadsheets
How to check spreadsheets for missing data
You can check for missing lines and missing fields in Beyond Compare. The sample data for this example can be downloaded here.
In this example we will show
how to set the key column
how to make column 2 in Table 1 align with column 4 in Table 2.
how to ignore columns
how to summarize the differences in a report
spreadsheets are read-only in Beyond Compare (ou can neither edit nor save changes)
How to set the key column
1) Open two spreadsheets in Beyond Compare. This looks terrible. Nothing is lined up. This is useless right now:
2) By clicking Rules->Columns->rightclick:Edit (or rightclicking the column tops in main view), select an appropriate column to be the key. For our example here, ID is an appropriate key. A key should be a column that exists in both spreadsheets and is unique within its spreadsheet. Make other columns not the key, with rightclicking and unselecting Key.
3) To help the columns line up: Rules->Columns->[arrow keys]
4) Hide or remove columns you are not interested in (by doing more clicking). Don't worry, you are operating on temporary files. Your original spreadsheet is treated as read-only. At this point I would expect a lot of the lines to match up. Why are they pink?? They are pink because I hid the unwanted columns, when what I should have done is delete the unwanted columns.
5) Rightclick in the main view to unhide columns. Now remove them: Rules->Columns->[remove] Now the view looks much better.
The only colored rows are red letters on blue highlight (orphan rows that are entirely missing from one file) and pink highlight (missing field info).
6) The last thing you might want to do is export a list of the differences. Exporting can be done in Text (black and white, old school) or HTML (colored and formatted to look like your GUI). Session->TableCompareReport.
Last updated
