Question:

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

create-a-drop-down-list-containing-only-unique

Answer:

Table of contents

Sort values using array formula

create-a-drop-down-list-containing-only-unique2

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?

create-a-drop-down-list-containing-only-unique2

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-unique3

Download example workbook

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


Functions in this article:

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
          ' Step through the elements in the array starting with the
          ' last element in the array.
          For i = UBound(TempArray) To 0 Step -1
              ' Set MaxVal to the element in the array and save the
              ' index of this element as MaxIndex.
              MaxVal = TempArray(i)
              MaxIndex = i
              ' Loop through the remaining elements to see if any is
              ' larger than MaxVal. If it is then set this element
              ' to be the new MaxVal.
              For j = 0 To i
                  If TempArray(j) > MaxVal Then
                      MaxVal = TempArray(j)
                      MaxIndex = j
                  End If
              Next j
              ' If the index of the largest element is not i, then
              ' exchange this element with element i.
              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

Download excel 97-2003 *,xls file

Create-a-drop-down-list-containing-only-unique_vba.xls