What makes this case different from the base? It worked yesterday, what’s changed? Why don’t we get the same answer? I have previously written about how the GRTMPS compare tool can be used to identify differences between spreadsheets that contain OMNI format input tables. Here's a tool included in MS Office 2016 that can identify the differences betweeen any pair of Excel workbooks, to help you when you have spreadsheets containing other data formats.
| I have previously written about how the GRTMPS compare tool can be used to identify differences between spreadsheets that contain OMNI format input tables. While useful, it is rather limited – it doesn’t tell you anything about the rest of information in the workbook, and doesn't handle spreadsheets with other relevant content – such as Spreadsheet Import (SSI) files and Process Simulator files (PSIs). Help is at hand if you have Office 2016 or newer. On the Start Menu, under Microsoft Office 2016 Tools you can launch Spreadsheet Compare (SPREADSHEETCOMPARE.EXE). By default, I think, it "installs on first use", so I had to run-as-administrator when launching it for the first time. |
 |
As an example, I’ve compared the VGO HDT simulator files from release 5.3 and 5.4.
The compared files appear in the top half, while details of what has changed are given below. As it is probably hard to see much in the picture of the full screen, here are some snips of the different sections.
Each spreadsheets appears in a window. The scroll bar and tab selection are active, so you can move around.
The original formatting has been stripped out and replaced with background colours on cells that have differences. The dark green cells, such as C5, are different input values. The purple cells contain a different formula. The bluish-green (teal?, aqua?) cells show where the formula is the same but the result is different.
 |
The key to the colours is found in the lower left segment. Here you can also control the types of differences to be highlighted. I often turn off the differences in Calculated values to reduce clutter, as these are usually a consequence of the differences in entered values. |
The central portion of the bottom half of the window is a list of all the differences, again colour coded by type.
Sheet Output has a number of red items, indicating structure differences. There is an Export button in the top ribbon so that you can save these results to a spreadsheet for later reference.
I have had this app pinned to my desktop for a few months now and can’t imagine how I used to manage without it. (Many thanks to Sotiris Schizas for telling me about it.) I really need to get around to trying out the Database Comparison tool as well.
From Kathy's Desk 24th May 2018.
Comments and suggestions gratefully received via the usual e-mail addresses or here.
You may also use this form to ask to be added to the distribution list so that you are notified via e-mail when new articles are posted.