Question:

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

Table of contents

Sort values using array formula

The helper column D filters unique distinct values sorted from A to Z from values in column B. The drop-down list in cell F3 is populated with the values in column D.

Array formula in cell D3

=INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

Watch a video explaining the formula

Watch a video explaining how to populate Drop-Down list with a dynamic named range

Recommended article:

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Comments(81) Filed in category: Excel, Sort values

Create dependent drop down lists containing unique distinct values

Here is a list of order numbers and products. We are going to create two drop-down lists. The first drop […]

Comments(113) Filed in category: Dependent drop down lists, Excel

How to create an array formula

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

Recommended article:

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

How to copy array formula

  1. Select cell D3
  2. Copy cell (Ctrl + c)
  3. Select cell range D4:D7
  4. Paste (Ctrl + v)

Explaining array formula in cell D3

Step 1 - Filter unique distinct values

COUNTIF($D$2:D2,$B$3:$B$11)=0

The COUNTIF function counts previous values above to make sure that no duplicates are displayed.

If no values in previous cells above are found the COUNTIF function returns a 0 (zero) for that position in the array.

COUNTIF($D$2:D2,$B$3:$B$11) returns {0;0;0;0;0;0;0;0;0}

COUNTIF($D$2:D2,$B$3:$B$11)=0 returns {TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE;TRUE}

The picture below shows the array in cell range C3:C11. The array tells the formula that no values have yet been displayed in cells above cell D3

Note that the first argument in the COUNTIF function ($D$2:D2) is a growing cell reference meaning when you copy the formula to cells below the cell reference expands automatically.

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

Step 2 - Create an array with sort rank numbers

The COUNTIF function comes once again to rescue, it allows you to rank each value in cell range B3:B11 based on its position in a sorted array.

COUNTIF($B$3:$B$11, "<"&$B$3:$B$11)

Value "BB" is first in a sorted list indicated by the corresponding value 0 (zero) in column C. The second value is "DD" with rank number 1.

COUNTIF($B$3:$B$11, "<"&$B$3:$B$11) returns {1;3;5;3;7;0;5;7;1}

Step 3 - Build an array with rank numbers based on which values that have not been shown yet

IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),"")

becomes

IF({TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE;TRUE},{1;3;5;3;7;0;5;7;1},"")

and returns {1;3;5;3;7;0;5;7;1}

No values in the array have been filtered out, this makes sense since the array formula is in cell D3 and no values have been displayed yet.

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 4 - Find the smallest value in the array

The SMALL function lets you filter the n-th smallest value from an array of values. It has a major advantage to the MIN function as it also ignores text and blank values.

SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1)

becomes

SMALL({1;3;5;3;7;0;5;7;1}, 1)

and returns 0 (zero).

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

Step 4 - Find the position of a value in an array of values

To be able to fetch the right value to cell D3 we need to know it's position in column B. The MATCH function returns the position.

MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0, COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

becomes

MATCH(0, {1;3;5;3;7;0;5;7;1}, 0)

and returns 6. Value 0 (zero) is in position 6 in this array {1;3;5;3;7;0;5;7;1}

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

Step 5 - Fetch value

The INDEX function retrieves a value based on a coordinate, since it is only a column the INDEX function allows you to only use a row number as a coordinate.

INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

becomes

INDEX($B$3:$B$11, 6)

becomes

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

and returns value "BB" in cell D3.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

How to create a drop-down list that expands with new values automatically

  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

Recommended articles:

Create dependent drop down lists containing unique distinct values

Here is a list of order numbers and products. We are going to create two drop-down lists. The first drop […]

Comments(113) Filed in category: Dependent drop down lists, Excel

Final note

Convert the data in column B into an excel defined table to make it dynamic. Then you don't need to adjust the cell references in the array formula in column D every time you add or delete values in column B.

Download *.xlsx file

Create a drop down list containing only unique.xlsx

Recommended articles:

Create dependent drop down lists containing unique distinct values in multiple rows

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]

Comments(12) Filed in category: Dependent drop down lists, Excel

Invoice template with dependent drop down lists

Overview This post descibes how to use a basic invoice template I created. The invoice template let´s you use dropdown […]

Comments(11) Filed in category: Dependent drop down lists, Excel, Invoice, Templates

Dependent drop down lists – Enable/Disable selection filter

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

Comments(8) Filed in category: Dependent drop down lists, Excel

Dependent data validation lists in multiple rows

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]

Comments(29) Filed in category: Dependent drop down lists, Excel

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 row number 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) &amp;amp;amp;amp;amp;gt; 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) &amp;amp;amp;amp;amp;gt; MaxVal Then
                      MaxVal = TempArray(j)
                      MaxIndex = j
                  End If
              Next j

              If MaxIndex &amp;amp;amp;amp;amp;lt; 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