Excel Splitter – Sheet, Rows and Columns
The splitter function allows users to take a range in a filter value and create data for each of the values in that range. Sharperlight provides 3 different types of splits that can be done within Excel:
- Row Split: Takes a filter range that is specified in that row and creates new rows for each of those filter values.
- Column Split: Takes a filter range that is specified in that column and creates new rows for each of those filter values.
- Sheet Split: Takes filter value ranges specified in the whole worksheet and creates new worksheets identical to the first, but for each filter value.
A simple example of a Split would be a database having multiple account codes for each group (i.e. Revenue. Expense, Cost, etc.), splits would allow the user to produce data for each of the account codes within the grouping (e.g. Revenue).
Row and Column Split
A Row Split will take the filter range on that row and create new rows for each individual filter value in that range. For example, if the user had a value formula for Revenue, which consists of the account codes from 100000 to 199999, they normally would get a single aggregated value for all the values within that range. However, if the user were to use a Row Split on that Revenue row, they would insert a new row for each of the different account codes within that range, and output a value for each. Row Split replicates the original row and passes the different account codes to each.
A Column Split is very similar to a Row Split. It essentially provides the same function, except it adds new columns instead of rows and it splits the columns based on the filter value range that is specified in that column.
Here is an overview on how to create Row and Column Splits using the Split wizard:
Create a Value Formula in Excel. In this example, there are two filters, which are Account Code and the Period. Both of which are referencing cells in the worksheet.
The Value Formula was then copied down and an Expense Row was created.
In the Excel ribbon, select the Sharperlight XL tab and click Splitter. Click on the Row or Column tab in the side panel and select the Value formula cell in the worksheet. Refer to the image below (red selection). Click on the search button on the Splitter panel and a Filter Name area should populate (the green selection in the image below).
Select the Filter Name and the Code Values/Filter Name and Code Cell Reference should be filled in automatically. If the filter has an in-built description (i.e. when the lookup is opened, there is an additional column with a description), then in the Description Cell Reference area, select an empty cell and it should output those descriptions together with the rest of the data. Alternatively, users are also able to type a comma delimited list directly into the panel or into the worksheet and reference that cell. Refer to the blue and purple selections in the image below.
Once the panel’s values are all filled in, select an empty cell in the worksheet, on the same row (if a Column Split is selected, select a cell on the same column). Click the button next to the search button in the Splitter panel and it will create the formula. Refer to the yellow selections in the image below.
Click the Refresh Worksheet button in the Excel ribbon and the new worksheet will be created with all the new rows.
Above is a small portion of the output from splitting the row. There is now a row for each Account Code within the Revenue filter value range. The above example only has one Split; however, a worksheet can have multiple that work in conjunction with each other. Refer to the example below, where a row and column split have been added to the same worksheet. The columns have been split by periods across a year and output a value per period per account code.
In the example above, we used the filter name @GLAccCod which was found in D6, however, as mentioned before, users are also able to enter a comma delimited list instead. This extends the potential of Splitting, as instead of typing a comma delimited list, users can actually use a value formula to output the comma delimited list, which allows them to use query builder to design their own custom logic. This can make the result dynamic, more specific and/or reduce the run time.
Sheet Split
A sheet split is very similar to a row or column split, however, instead of a row or column being replicated, the whole sheet is replicated, which means that a table formula can be used instead of value formulas. The process of creating a sheet split is:
Create a table formula and set the filter value. In this example, the filter Company is set to reference cell D6 (the splitter panel should not be open yet for this step).
Once the table has been created, open up the Splitter panel, select the Sheet tab, select the cell in the worksheet with the table formula and click the search button in the panel. The area beneath it in the panel should now be populated with the filter.
Select the Filter in the panel and the field values should now be populated. Select the values to be included in the split.
Select a blank cell in the worksheet and click the link button. Click the refresh worksheet button in the Sharperlight Excel ribbon and a new sheet will be created for each of these selected filter values.
Another example to split sheets based on a dynamic list is as follows:
1. In your Excel Workbook, create a Table Formula. In the Sharperlight Query, set the field that you want to split on as a filter by dragging it into the filters section.
2. Create a mdFilter function in a cell, using the desired column of the query as the range. Ensure the Mode is set to 4.
3. Lastly, Create a cell with a mdSplitSheet function. Set the codes and sheet names to the filter cell and set the code cell reference. After this, refresh the workbook and your split sheets will be generated.
Splitter Function
The aforementioned instructions have been based on using the Splitter Panel, however, users can also use the function feature to enter the values manually.
Select a cell and type “=mdSplitRow(“ (or Column or Sheet) and click on the function button. Fill in the details manually and click OK when finished. Click the refresh worksheet button and the rows will be created.
These are the different parameters in Split Row and Split Columns.
| Item | Description |
|---|---|
| Codes | A list of code values (filter values that will be split on). The codes can either be manually typed as a comma delimited list, enclosed in quotes (e.g. “4101,4102,4103) or it can reference a named filter in a Sharperlight query formula. |
| Descriptions | The descriptions can be hard coded in the formula as a comma delimited list and these will over-ride the descriptions returned by the code reference. If the user wishes to use the codes automatically returned from the code reference, from a Sharperlight formula, then this section should be left blank. |
| Code Cell Reference | This corresponds to where the codes are located on the worksheet. For example, if a Sharperlight formula is created and a filter was referencing a cell value, that cell reference would be what to put in this section. The code cell reference is the cell address which is updated with the code filter value on the cloned row or column. |
| Description Cell Reference | The description cell reference is the cell address which is updated with the description on the cloned row or column. |
| Exclude Parent | Sometimes the parent row or column from the master worksheet is not required on the child sheet, this boolean argument will delete the parent on the child sheet is set to TRUE and leave the parent alone if it is set to FALSE or left blank. |
| Before | This setting determines where the parent row or column appears when the children are generated. The default is after but it will accept a TRUE/FALSE boolean value. |
| Group | When set to TRUE the group argument will create group control using the Outline feature in Excel. The default is FALSE. |
| Collapse | This TRUE/FALSE boolean value will control whether the group control is collapsed or expanded. The default is FALSE. |
| Copy Range Reference | This argument is not implemented in the Sharperlight 3.1 release but the feature will allow multiple rows and columns to be cloned as a group. |
| Residuum | This a description label that will be returned when the <ALL> filter has been reference. To ensure cross balancing an additional Residuum row will be cloned and the code filter value will be an exclusion of all the other filters, eg <<!R20,R30,R40,R50,R60,R70,R80 |