Filter/Sort

Filter is one of the most important functions and usually the most used function in excel. When dealing with large databases, Filter helps in narrowing down the data according to parameters defined by the user, allowing quicker access to required information. Sorting is the arrangement of data in an ordered sequence defined by the user, to increase its usefulness for specific operations.

You can access the filter option in SheetKraft as follows:

There are various filter options available, here are the steps for filtering out data of a specific company from the entire database.

Step 1: Click on the Filter/Sort button to open this dialogue box and select the data that is to be filtered. You can click on the change sheet option if the data is on some other sheet and check the my data has headers box if the data contains headers, otherwise untick it.

Step 2: The filter UI will appear. The dialogue box looks like this:

Step 3: Choose the various conditions that you need for the filtered data. In this example, I want to filter out only Kingfisher company details. This is how the condition will look like:

Step 4: Tick the copy headers box if you need to copy the headers. Then, click on next. The following dialog box will appear:

Choose the sheet from the existing worksheet drop down and blank cells where the output must be pasted or click on new worksheet if you want to paste it in a new sheet.

This is how the output would look like:

NOTE: The table below explains the functionality of the various conditions present under the conditions tab.

From the above mentioned conditional operators, almost all of them are straightforward and easier to understand. Let's look how we can pass a condition using a substring just for an example.

We want to look for data corresponding to Product names which contain the word Loan. Now for this, we need to filter data with product names containing the substring. We can do this by passing the condition Contains substring with the substring as "Loan" as shown in the figure below.

You can also pass the substring by absolute referencing the cell ($column $row) containing substring only or by directly selecting the cell from the dataset.

Applying multiple conditions:

You can also use multiple conditions if required by clicking on the “+” sign in the conditions tab in the UI.

Two or more conditions can be linked using either the AND option or the OR option

● AND: This option is used when both the conditions must be satisfied. If a data satisfies only one condition, it won’t be shown in the output.

● OR: This option is used when either of the conditions must be satisfied. If a data satisfies only one condition, it will be shown in the output.

For example, we are interested in secluding Kingfisher company with interest rate 6.2 or more. Since we want both conditions to be satisfied, we use AND. If we selected OR then the filtered data would have contained all the Kingfisher company data as well as all the data entries which had interest rate more than 6.2.

Nested Conditions.

SheetKraft provides an option to construct custom nested conditions. Nested conditions comprise condition statements contained within the definition of other condition statements.

You will notice a Plus sign appearing on the right hand side of the conditions. Click on the Plus sign besides the condition you want to nest.

For example, let’s suppose we want all data entries with Kingfisher company which have either the interest rates more than 6.2 or the product type as Term Loan.

This is how the condition would look like:

Advanced Section

This section contains two options:

● Distinct: This will make sure all the data entries in the output are distinct and there is no repetitive entry.

● Max Rows: This option gives you an option to add the maximum rows that you want in the output, let’s say you want only 5 rows. Check on the max rows box and type 5 or give a cell reference which has the number of rows required.

You can also partition the data as shown in the image below:

SORTING.

Sorting is the organisation of data into an ordered list on the basis of some parameter decided by the user. Let us suppose we are interested in sorting our data on the basis of date(ascending).

Step 1: Click on the sort option which is there in the sheetkraft tab. You can access it as follows:

Step 2: Select the data to be sorted. You can select Change sheet if you want to select data from some other sheet.

After selecting the data, click on OK.

Step 3: A dialogue box will appear as follows which has an option as sort the result

Select the column on the basis of which you want to sort the data from the drop down list. Choose Ascending/Descending based on your preference and click Next.

Sorting based on multiple columns:

SheetKraft provides the option to sort data on the basis of more than one column. It sorts the data priority-wise ie. first sorts using the first condition, then within that sorts on the basis of the subsequent conditions.

To sort using multiple columns, click on the Plus sign present below the Sort the result tab. ( See figure)

Add the second field on the basis of which you want to sort. Similarly, you can add further fields.

Step 3: After you have selected/rearranged the columns click on Next. You will be asked where to save the filtered data.

Step 4: To save the data in the existing sheet(s), you can input the index of the starting cell where you want to copy the filtered data or you can save the data in a new sheet.

After entering your choice, click Finish.