Comparing cell ranges

Overview

The 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.

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.

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’.

Steps

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:

Leave a Reply

Latest Tweets

Free eBook: 10 Tips for sourcing good data yucalc.com/2017/02/17/fre…

reply · retweet · favorite

Newsletter

By signing up, you agree to our Terms of Service and Privacy Policy.

Address

Suite 1506, Westfield Tower 2, 101 Grafton St Bondi Junction NSW 2022 Australia

View Larger Map

Contact Us

AUS: +61-2-9386-0202
UK: +44-20-8123-1251
Send us an email

ContactUs.com
WordPress Image Lightbox