Selecting a list box value once – Part 1

At the request of Maurice and Harm Valentin, this next tip has been written.
In Excel tip 041 , I’ve explained how to avoid duplicates that are typed but, in this tip we will discuss how to avoid duplicates by using a list box, which reduces the number of options when a selection is made.

What we want is that when we select a name from the list (1), that name has to be removed from the list.

We first start by making a list of employees in the cells F3 to F22(2) (20 employees are in this example).

Then we decide what goes into the cells H3 to H22 when a name is selected from the list.
We type the following formula in cell H3:
= IF (OR ($ C $ 3: $ C $ 22 = F3), F3, “”) and press CTRL + SHIFT + ENTER. The formula is now an array formula  {= IF (OR ($ C $ 3: $ C $ 22 = F3), F3, “”)}.
With the cell H3 still selected, we copy the formula down to cell H22(3) with the fill handle.

Now, for the formula for the names that have not yet been used.

We type this in cell I3:
=IF (ROWS ($ I $ 3: I3)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22), “”,
INDEX ($ F $ 3: $ F $ 22, SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3)))).
We then press CTRL + SHIFT + ENTER. The formula will change to a matrix formula:
{= IF (ROWS ($ I $ 3: I3)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA($ C $ 3: $ C $ 22), “”,
INDEX ($ F $ 3: $ F $ 22, SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3))))}

How does this formula work?
Consider the first portion:
IF (ROWS ($ I $ 3: I3)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22), “”
The ROWS function returns the number of rows in a reference.
The COUNTA function counts the number of nonblank cells in a range.
If the number of rows in the range I3 to I3 (= 1 row) is more than COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22), then this function does not return anything.

If we copy down the formula, it subsequently changes to = IF (ROWS (I $ 3: I22)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA($ C $ 3: $ C $ 22),””
If the number of rows in the range I3 to I22 (= 20 rows) is more than COUNTA ($ F $ 3: $ F $ 22) -COUNTA($ C $ 3: $ C $ 22), then this function does not return anything.

Eg. for cell I22: If a name is selected from the list, in cell C10 (in this example: Marc).
If the number of rows of the range I3 to I22 (= 20)(4) is more than COUNTA($F$3:$F$22) (= 20)(5) -COUNTA ($ C $ 3: $ C $ 22) (= -1) (6)  (= 19), there is no result (7).

The second part of the formula, pertains to what is to be done if the IF condition is met.
We consider the formula for cell I3 (i.e, the first row of the cell area I3 to I22) as an example.
INDEX ($ F $ 3: $ F $ 22, SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3)))).

The = INDEX function returns a value or reference to a value from a table or range.
The INDEX function has the following syntax: INDEX (array, row_num, [column_num]).
Parameter: 1 = An array, which is required. This can either be a range of cells or an array constant.

If the array contains only one row or column, the corresponding row_num or column_num is optional.

If the array contains multiple rows and columns and only the row_num or column_num is used, INDEX returns an array of the entire row or column in array.

Parameter 2: = Row_num, which is required. This selects the row in the array from which you want to return a value.  If row_num is omitted, the column_num argument is required.

Parameter 3: = column_num, which is optional. This selects the column in the array from which you want to return a value. If the Column_num is omitted, the row_num argument, is required.

INDEX ($ F $ 3: $ F $ 22) = The range of cells from which we need to select the names.
For the second parameter of the index function, which is the row number, we use the following formula:
SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3)
The SMALL function determines the k-th smallest value in a given range. We can 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 in which the k- th smallest value is to be determined.

Parameter 2: = k, which is required. This is the position (counted from the lowest value) in the array or the range of cells with data.
Here’s an example of the function = SMALL:
In this example, we have a series of numbers in the range of cells A1 through A10 and the formula = SMALL (A1: A10, 3) in cell C1.
The formula works as follows: It checks the cell range A1 to A10, and gets the third smallest value.

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

We will continue Part 2
Part 1 – Part 2 – Part 3

Copyright © 2013 – Swotster Ltd.

Featured Courses

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