OAK's Compare tools | Remove compare modifications removes any changes to a spreadsheet that have been made by OAK's Compare tools | Compare Workbooks/Worksheets command, putting the spreadsheet back to the condition it was in before it was compared.
Why would you want to use it?
The OAK4 | Compare | Workbooks/Worksheets/Ranges command inserts rows and columns into the spreadsheets being compared in order to minimize the number of reported differences That action alters the spreadsheet. Under some circumstances it is desirable to reverse the alterations.
Operis makes extensive use of this facility when commissioned to audit financial models. It refers to the client's spreadsheet as the MUT, the Model Under Test. It builds a new spreadsheet, which it calls the AWP, or Audit Working Papers, which contain hundreds of tests. Some are obvious, like whether any balance sheet balances. Others are more proprietary.
Frequently the client provides a revised MUT, reflecting changes to the deal, or reactions to issues raised in the audit process. This MUT2 is highly likely to have a row layout slightly different from the original MUT1.
In such circumstances Operis will
| • | load into Excel the AWP linked to MUT1, and MUT2 |
| • | use OAK's Compare feature to get the rows in MUT1 and MUT2 as aligned as possible |
| • | use Excel's Edit Links command to switch the links of the AWP from MUT1 to MUT2 |
| • | use OAK's Remove compare modifications so that MUT1 and MUT2 are restored to their original conditions |
The result is that the AWP extracts necessary information from the appropriate regions of MUT2 even though they are not necessarily in exactly the same position as they were in MUT1.
In this way, Operis can validate new versions of spreadsheets in a matter of minutes, which is handy if a deal is to be signed imminently. Spreadsheet auditors who do not have a process similar to this one are forced to impose a cutoff point beyond which they no longer accept changes, since they need time to redo all their work from first principles.
HOW TO USE IT
To remove the comparison modifications, select the OAK4 | Compare | Remove compare modifications command and the following dialog box appears:
Select either ‘Whole Workbooks’ or ‘Selected Worksheets’ as necessary. In the corresponding boxes below each option, select the required workbook(s) or worksheets (Ctrl and click to select multiple items) by clicking on the names in the relevant box. Multi worksheets can only be selected if only one workbook is selected.
Wrinkles
If, as well as aligning the spreadsheets, you have OAK deliver a report of the differences it found during its comparison, the removal of the aligning rows will leave the report referring to where the items used to be before the row removal, not where they are now.
What you could do if you didn't have OAK
The question of what how you would achieve a similar action if you did not have OAK is somewhat hypothetical. If you didn't have OAK, you would not have any changes made by OAK's Compare workbooks command to reverse. (Unless you had an old version of OAK, which did have the Compare command, but not the means to remove the compare modifications.)
But leaving that point aside, OAK aligns spreadsheets by inserting rows which it marks clearly, by color and by tagging them with a comment. In the absence of the Remove compare modifications command, one could search for and delete the modifications made manually.
If you do not have links between the workbooks being compared, such as the ones to the AWP above, you don't need OAK to get back to the unaltered spreadsheet. Just discard it and reload it.