Skip to main content

Delete Columns Selectively

Cameron Duncan
  • Updated

The Delete columns selectively instruction allows a user to delete columns from within an Excel worksheet based on the data contained in a row or in various rows across each of the selected columns.

  • For example, a user can configure the instruction to delete any columns from Column A to Column M if the total amount is 0.
For the purposes of this article, we are going to use a simple and fictitious Excel file. Consider the following Sales report. There are months that have no Sales data yet and we would like to delete these columns. This could be done by deleting all the columns that have a value of 0 for Total Sales.
Delete_columns_selectively_0_v2.png
 

Step-by-step guide

1. Create a data step and upload the data.

2. Create an action step to manipulate data.

3. Select the Delete columns selectively instruction.

Delete_Columns_Selectively_1.gif 

4. Configure the target range to select just the Total Sales row.

Hide_Columns_selectively_2_v2.png

5.  Enter the condition(s) on which a row will be hidden and click "Add". 

In the following example, columns will be deleted on the condition that the value in row 1 of the selected range in step 4 (Total Sales) is equal to '0'.

Delete_Columns_Selectively_3_v2.gif

6. Click "SAVE" and run the step. 

Once this step completes successfully, the appropriate columns will be deleted.

Delete_columns_selectively_6.png

Multiple conditions

In the Delete columns selectively instruction, you can specify more than one condition to be met, in order for a column to be deleted.

1. To add more conditions, fill the condition in the template row (the first row with the blue background colour) and remember to click "Add" after entering the condition (as seen in step 5 in the previous section).

2. In the case of multiple conditions, select whether you want ALL or ANY of the conditions to be met for the columns to be deleted.

  • ALL: all conditions you enter must be satisfied for the row to be deleted
  • ANY: at least one condition you enter must be satisfied for the row to be deletedmceclip1.png

For example, suppose we would also like to delete column A (which contains the shop number) in the report.

  • Two conditions are specified and we choose to meet "ANY" of the conditions for the column to be deleted. 

Delete_columns_selectively_7.png

This will provide the following output.

Delete_columns_selectively_8.png

Useful tips and resources

  • The conditions in step 5 can be generalised into different operators. For example, we can use a wide range of various operators including the below options.

mceclip4.png

  • Users can choose to fix their conditional value (select "Literal") or set it to vary depending on a property elsewhere in the process (select "Data Property Link").

          mceclip5.png

  • For those who select "Data Property Link", instead of writing a literal value in the text box, you can link the value to a data property that contains a variable value 

          mceclip6.png

  • When specifying a condition, the text can be toggled using the tick-box, depending on whether case-sensitivity is desired or leading & trailing whitespace can be ignored. 

        mceclip1.png

The file containing the example used in this article can be downloaded below.

Comments

0 comments

Please sign in to leave a comment.