Filtering data with the help of a formula:

At the request of Rudy De Grauwe, I wrote the following tip.
You can download the sample file here.

Rudy wants to compile a shopping list by selecting items(1) and displaying these items as a contiguous sequence in a different column (2).

If we look at the example below, for the cells in column B which fulfill the filter criteria, the corresponding rows of column C should be displayed in a different location.
We are going to do this filtering by using a formula.

We first place the boxes in column A and link these to the cells of column B. See Excel tip 022 .

We type the following formula in cell E2:
= IF (ROWS (E $ 2: E2)> COUNTIF ($ B $ 2: $ B $ 25, TRUE), “”, INDEX ($ C $ 2: $ C $ 25, SMALL (IF ($ B $ 2: $ B $ 25 = TRUE, ROW ($ B $ 2: $ B $ 25)-ROW ($ B $ 2) +1), ROWS ($ B $ 2: B2))))

We then press CTRL + SHIFT + ENTER.

The formula becomes an array formula:

{= IF (ROWS (E $ 2: E2)> COUNTIF ($ B $ 2: $ B $ 25, TRUE), “”, INDEX ($ C $ 2: $ C $ 25, SMALL (IF ($ B $ 2: $ B $ 25 = TRUE, ROW ($ B $ 2: $ B $ 25)-ROW ($ B $ 2) +1), ROWS ($ B $ 2: B2))))}

We copy the formula down to cell E25.

The first part of the formula is:
= IF (ROWS (E $ 2: E2)> COUNTIF ($ B $ 2: $ B $ 25, TRUE), “”,)

According to this, if the number of rows of the cell area E2 to E2 (= 1) exceeds the number of times the value TRUE occurs (in our example is 3) in the cell area B2 to B25, we get nothing.

The IF function displays the value if the condition is met and another value when it is not met.
The ROWS function returns the number of rows in a reference.
The COUNTIF function counts the number of cells in a range that meets a single criterion.

When we have selected one or more check boxes in column A , (does not matter which) then the number of rows of the cell area E2 to E2 (= 1) is less than or equal to the number of times the value TRUE appears.
So, (=> 1) in the cell area B2 to B25, i.e, the IF condition is not met and so it brings the second part of the formula into consideration.

INDEX ($ C $ 2: $ C $ 25, SMALL (IF ($ B $ 2: $ B $ 25 = TRUE, ROW ($ B $ 2: $ B $ 25)-ROW ($ B $ 2) +1), ROWS ($ B $ 2 : B2)
The INDEX function returns a value or reference to a value from within a table or range.

The syntax of the INDEX function has the following parameters:
INDEX (array, row_num, [column_num]).

Parameter: 1 = Array, which is required. This can be a range of cells or an array constant.

If the array contains only one row or column, the corresponding argument, row_num or column_num is optional.
If we have an array of multiple rows and columns and only a single row_num or column_num is used, INDEX returns an array of the entire row or column in the array.

Parameter 2: = row_num, which is required. This selects the row_array from which a value should be retrieved. If you omit the row_num, column_num becomes a required argument.

Parameter 3: =column_num, which is optional. This selects the column array from which a value should be retrieved. If you omit column_num, row_num becomes a required argument.

INDEX ($ C $ 2: $ C $ 25;= The range of cells from which we need to extract the data.
For the second parameter of the INDEX function, i.e, the row number, we use the formula:
SMALL (IF ($ B $ 2: $ B $ 25 = TRUE, ROW ($ B $ 2: $ B $ 25)-ROW ($ B $ 2) +1), ROWS ($ B $ 2: B2)

The SMALL function determines the k th smallest value in a given range. We use this function to find values with a certain relative position in a data set

The SMALL function has 2 parameters.

Parameter 1: = array, which is required. A array or a range of numerical data should be specified, which the k- th smallest value is determined.

Parameter 2: = k, which is required. It is the position (counted from the lowest value) in the array or the range of cells with data.

Again, we have just described in Excel Tip 068 Part 2 how to assign a numerical value to the cells B2 to B25 which contain the value TRUE.

Eg: If we select the flour, milk and oil check boxes and we look in cell E2 for the matrix parameter of the SMALL function, which is done by the part IF ($ B $ 2: $ B $ 25 = TRUE, ROW ($ B $ 2: $ B $ 25) ROW ($ B $ 2) +1).

Then you have this result:
FALSE, 2, FALSE, 4, FALSE, 6, FALSE, FALSE, FALSE, FALSE, FALSE;
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}
The parameter K of the SMALL function gives the result: 1

So, using cell C3 for the index, we take the First smallest value (=2) from the range C2 to C25, for cell E2. Using the index cell C5, we take the smallest value(=4) in the range C2 to C25 for cell E3, and using the index from cell C7 we take the smallest value (=6) in the range C2 to C25 from the cell C7.

Tip: make the worksheet more visually appealing hide column B: Course Excel 2007 Lesson 16 , Select cells E1 to E25 and define a print area: Course Excel 2007 Lesson 80.

FYI: Swotster.com thinks of its students, so you also please remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats