Prerequisites
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 that 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:
Since the rules can be reused between BitCurb Crunch Excel add-in and API, you will see the rules created earlier. If the rules are not available, you will need to create them following the instructions in the article above.
Click on Back arrow to go back to Home screen and select Template link to load the templates. Templates cannot be reused between BitCurb Crunch Excel add-in and API, the templates created for the API will not be listed in the Excel add-in.
To create a new template click on “New Template” button at the top of the Templates page:
This will open New Template page and populate the required data and select the rules to apply to the template:
Select the corresponding tables for the both “Side 1” and “Side 2” which will be used as data sources. The source data used as example in “bank.xslx” book has the “Table2” (from “gl” sheet) as Side1 and “Table1” (from “bank” sheet) as Side2. Save the template, the home page will load again where the newly created template will show:
Press on the “Run” button next to the template name to run the template:
As a result, a unique Id will be populated in the “BitCurbGroup” column for the matching rows for both sheets indicating the items corresponding the group.
If you examine the results in the screenshot above, you will notice for example that group ID “97b48ba7-be46-4b94-a9da-2be89105d876” is matched to 2 items (row 8 from Table1 and row 5 from Table2) and this result corresponds to the one to one rule we created earlier for records that have the same payment reference number and same amounts. These results we observed when running the same data through the API, more details and detailed break-down of the results can be found here.
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”:
Important note: We do not change the source data in excel, we only add, but do not remove or modify the source content. In this case if a previous execution of the matching operation has resulted in new variance rows that data will be included in the next run. Please, review the source data before executing the operations and make sure that transaction data between different transactions is cleared.