In order to run filtering operations form BitCurb Crunch Excel add-in the following prerequisites are required:
- Excel Online or Excel 2016
- BitCurb Crunch add-in to be installed and configured. Please refer our article for setting up our Excel Add-in
- Excel data which will be filtered to be formatted into tables
- “SourceBitCurbGroup” and “BitCurbGroup” columns to be added to all tables used in the matching operation. Adding the columns is optional, however without them the results won’t be displayed.
- The example below uses “bank.xlsx” file as data source. You can download the sample file from our GitHub repository.
Matching Templates and Rules
The operation type which will be run depends on the rules attached to it. In order to create a matching template, one or more matching rules will need to be attached to it. This can be done through the portal, or the BitCurb Crunch add-in. For the purposes of this article all screenshots are from the BitCurb Crunch add-in and we will be using the rules described in this article.
Once login to the BitCurb Crunch add-in, open the Home page and click on Rules link to load the rules:
To create a new template click on “New Template” button at the top of the Templates page:
More important is to review the variance results. In the API example, we received a variance value of “16.89” (please refer to the article above). When using the BitCurb Excel add-in, and the result contains variance value, new rows representing the variance value will be added to the “Side1” table with “BitCurbGroup” or “SourceBitCurbGroup” containing the unique Id of the corresponding matching result group and the “Crunch field” column containing the variance value. The newly added rows are highlighted in red to make sure that they are easily identified. To make the result more meaningful, the data from one of the source rows will be used to populate the rest of the values in the new rows. The row we use is the one containing the largest variance value number. Adding the rows is not considering the calculated columns, the data in the columns is text only and the functions are not preserved. Since we do not want to change the overall value of the source data, we add 2 rows: one with the variance value, and the other one the reverse variance value to zero it out with the unique group Id in the “SourceBitCurbGroup” column. This can clearly be seen in the result below, with group Id “101b8c57-b24e-4c17-9110-c8ad5a3d6bf3”: