Comparing cell ranges
The [highlight txt=’compare range’] instruction allows a user to compare one cell range with another cell range to determine whether the formulas or the values within cell of the first range are an exact match for each cell of the second range. When comparing values, the instruction can be configured to be ‘case sensitive,’ that is, the cases of the values between the two files must also be an exact match. The step can be further configured so that if the comparison determines that the ranges are not an exact match for each other, the step will either fail, issue a warning, or pass successfully.[message type=”warning”]For the purposes of this article we are going to use two simple and fictitious Excel files. They have already been uploaded into a data step. You will want to replace these files with those of your own.[/message]
Before we get started
Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.
The source file that we are going to use looks like this:
The target file that we are going to use looks like this:
We can see that the two files are identical in every way except that cell B2 of the source file is empty, whereas cell B2 of the target file contains ‘B’.
1. Create a ‘Compare range’ instruction.
2. Link the file named “Source” as the source file and the file named “Target” as the target file.
3. Select Sheet1 from both files.
4. For both files, use an Advanced Range from cell A1 to cell B4.
5. Immediately below, the comparison rule is set to “Compare Values” automatically.
6. The rule for ‘If this comparison finds the values are different this step should’ is set to “fail”.
7. Save and run the step.
8. Due to the difference between the source file and the target file in respect of cell B2, the step will fail with the following error message: