GS-Calc 21.3 introduces the File > Compare Workbooks function to find differences between two data sets. These can be:
- entire workbooks and any files in of any the supported file formats, e.g. text/csv files, xbase, sqlite, mysql, xlsx files,
- single worksheets,
- cell ranges.
You can freely choose which of the above categories the 1st data sets comes from, then where the 2nd data set is.
The comparisons start from the top-left cell of each data sets, left to right, so if you compare cell ranges (to another range, entire sheet or workbook), they can be located anywhere within a given sheet. If the dimensions of the compared data sets differ, non-empty cells from the bigger data set will be treated as differences
unless you check the option to compare only existing, non-empty cells from both data sets.
If you compare entire workbooks, the subsequent sheets are compared one by one ignoring the sheet names. Cells from missing sheets are treated as differences with the above exception.
The comparison concerns the cell contents: text, numbers, formulas and their values. Formatting and other data are ignored.
The Compare Workbooks dialog box:
You can choose the source data check boxes independently for each workbooks/data sets. You must specify at least one of the three options and you can mix your selections any way you want to. For example:
- If the “Workbook” option is not checked, the “Sheet” and “Cell range” option will refer to the workbook that was active when you open this dialog box. If only the “Workbook” option is checked, the entire workbook will be compared.
- If the “Sheet” option is not checked, the specified cell range will refer to each sheet in a given workbook. If only the “Sheet” option is checked, only its data will be compared.
- If the “Cell range” option is checked, only cells from that range will be compared. If the “Sheet” option is not checked at the same time, all the same ranges from all sheets will be compared.
Clicking the Browse button opens the default Open view with the recently used files and folders/drives and a given file/workbook is opened the same as with the regular Open File command along with all the applicable file format options.
If the Save comparison results check box is clear, GS-Calc will only display the number of found differences
If the Save comparison results check box is selected, GS-Calc will save all found differences as a new time-stamped sheet in the workbook that was active when you click the Compare Workbooks command. You can also specify in which folder of that workbook the results/reports are saved.
The progress dialog box shows the current status of the comparison:
The comparison report contains two 4 column groups and up to 32 million rows with found differences, for example:
The first column in each of the two groups contains hyperlinks to the found different cells in the 1st and the 2nd data sets. Clicking them you can quickly e.g. edit/correct/update them. To jump back to the same position in the results/report, click the toolbar rightmost bookmark “back” button. (Note: such jumping back requires that report workbook to be saved/named first.)