### Question:

How do I create a drop down list with unique distinct alphabetically sorted values?

### Array formula in cell B2

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

How to create an array formula

1. Select cell B2
2. Type above array formula
3. Press and hold Ctrl + Shift
4. Press Enter once
5. Release all keys

How to copy array formula

1. Select cell B2
2. Copy (Ctrl + c)
3. Select cell range B3:B6
4. Paste (Ctrl + v)

### Explaining array formula in cell B2

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

Step 1 - Convert text to numbers

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition

COUNTIF(List, ">"&List)+1

becomes

COUNTIF({"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD";"TT";"FF";"VV";"VV";"FF"}, ">"&{"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD";"TT";"FF";"VV";"VV";"FF"})+1

becomes

{11;9;5;9;3;13;5;3;11;2;5;0;0;5}+1

becomes

{12;10;6;10;4;14;6;4;12;3;6;1;1;6}

Step 2 - Identify previous unique text values above current cell

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition

NOT(COUNTIF(\$B\$1:B1, List))

becomes

NOT(COUNTIF("Unique list sorted alpabetically", {"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD";"TT";"FF";"VV";"VV";"FF"}))

becomes

NOT({0;0;0;0;0;0;0;0;0;0;0;0;0;0})

becomes

{1;1;1;1;1;1;1;1;1;1;1;1;1;1}

Step 3 - Calculate maximum number in array

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))

becomes

MAX({1;1;1;1;1;1;1;1;1;1;1;1;1;1}*({12;10;6;10;4;14;6;4;12;3;6;1;1;6})

and returns 14.

Step 4 - Convert maximum number into Boolean value

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1)

becomes

IF(14={12;10;6;10;4;14;6;4;12;3;6;1;1;6}, 0, 1)

and returns this array:

{1;1;1;1;1;0;1;1;1;1;1;1;1;1}

Step 4 - Return the relative position of an item in an array

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0)

becomes

MATCH(0, {1;1;1;1;1;0;1;1;1;1;1;1;1;1}, 0)

and returns value 6.

Step 5 - Return a value of the cell at the intersection of a particular row and column

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range

=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF(\$B\$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0))

becomes

=INDEX(List, 6)

becomes

=INDEX({"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD";"TT";"FF";"VV";"VV";"FF"}, 6)

and returns value BB.

### Create a dynamic named range

1. Click "Formulas" tab
2. Click "Name Manager"
3. Click List
4. Type =OFFSET(Sheet1!\$A\$2, 0, 0, COUNT(IF(Sheet1!\$A\$2:\$A\$1000="", "", 1)), 1) in  "Refers to:" field.
5. Click "Close" button

### Named range

List (dynamic)
What is named ranges?

### How to create a drop down list with values updated dynamically in excel 2007

1. Click Data tab
2. Click Data validation button
3. Click "Data validation..."
4. Select List in the "Allow:" window. See picture below.
5. Type =OFFSET(\$B\$2, 0, 0, COUNT(IF(\$B\$2:\$B\$1000="", "", 1)), 1) in the "Source:" window
6. Click OK!

Create-a-drop-down-list-containing-only-unique.xls
(Excel 97-2003 Workbook *.xls)

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

### Sort values using vba

Array formula in cell B2:B8000:

=FilterUniqueSort(\$A\$2:\$A\$8212)

How to create array formula

1. Select cell range B2:B8000
2. Type array formula above
3. Press and hold Ctrl + Shift
4. Press Enter once
5. Release all keys

VBA code
You can find the selectionsort function here: Using a Visual Basic Macro to Sort Arrays in Microsoft Excel

```Function FilterUniqueSort(rng As Range)
Dim ucoll As New Collection, Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
For Each Value In rng
If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0
For Each Value In ucoll
temp(UBound(temp)) = Value
ReDim Preserve temp(UBound(temp) + 1)
Next Value
ReDim Preserve temp(UBound(temp) - 1)
SelectionSort temp
FilterUniqueSort = Application.Transpose(temp)
End Function
Function SelectionSort(TempArray As Variant)
Dim MaxVal As Variant
Dim MaxIndex As Integer
Dim i, j As Integer
For i = UBound(TempArray) To 0 Step -1
MaxVal = TempArray(i)
MaxIndex = i
For j = 0 To i
If TempArray(j) > MaxVal Then
MaxVal = TempArray(j)
MaxIndex = j
End If
Next j
If MaxIndex < i Then
TempArray(MaxIndex) = TempArray(i)
TempArray(i) = MaxVal
End If
Next i
End Function
```

Where to copy vba code?

1. Press Alt + F11
2. Insert a module into your workbook
3. Copy (Ctrl + c) above code into the code window