One of our customers told us Rainbow was worth buying for its comparison function alone, and another customer found an unexpected use for it in resolving version conflicts. Rainbow’s Comparison Toolbox function provides you with a whole toolbox of different options, allowing you to compare:
- corresponding sheets in different workbooks, or
- different sheets within the same workbook, or
- ranges within the same sheet, or
- fixed or variable ranges across sheets.
At the same time Rainbow lets you choose whether you want to compare:
- formulas (calculations only), or
- formulas plus input constants, or
- results (constants only), or
- results (constants plus calculations).
If you are comparing formulas, Rainbow can group together differences which are due to copied formulas in the two worksheets or workbooks. This can make it very much easier to see what is really happening; instead of a report showing hundreds of individual differences, you can see that (for instance) they are all due to a change in one formula, copied over hundreds of cells.
As well as generating a colour-coded report, the Comparison Toolbox function highlights differences in the actual worksheet(s). So in the example shown above, difference 1 (cells L8:M8 in CY11 v2 sheet P&L) will be highlighted in green on the actual worksheet, and difference 2 (cell N8) will be highlighted in red. And you can jump straight to the relevant cells in the two workbooks by clicking the hyperlinks in the Cell 1 and Cell 2 columns of the report.
Rainbow will attempt to adjust automatically for differences caused by inserted or deleted rows and columns, and you can see in the example above that it has correctly identified two inserted rows (10:11) in workbook CY11 v1. So whatever kind of comparison you need to carry out, Rainbow will almost certainly be able to help!