Refine Data with BitCurb Crunch Add-in

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
  • “BitCurbResult” column to be added to all tables used in the filtering operation. Adding the column is optional, it is needed to mark the filtered result lines in the source data.
  • The example below uses “phones_spec-v2.xlsx” file as data source. You can download the sample file from our GitHub repository.

Filtering Templates and Rules

The operation type which will be run depends on the rules attached to it. In order to create a filtering template, one or more filtering 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:

Please note that the both “Side 1” and “Side 2” have the “Table1” selected since we are running the template on the same dataset. 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 new sheet will be created with unique name containing the filtered rows. The tab name will be set on the corresponding rows from the source data sheet in “BitCurbResult” column:

Please note that the data in the filter result sheet is is not formatted. It is important to set the column types in the new sheet, such as date columns. Date columns are being rendered as a numeric value – this is a special excel format which will be converted to the expected format once the column is changed to date type:

Select the column and apply the original formatting to view the result:

Tagged: