Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
This blog article is one out of five articles on the same subject.
Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel
Question: How do I create a drop down list with unique distinct alphabetically sorted values?
See picture of original list to the right.
Answer:
Excel array formula in B2. See picture below
Edit: 2009-09-02, new somewhat smaller formula.
=INDEX(List, MATCH(0, IF(MAX(NOT(COUNTIF($B$1:B1, List))*(COUNTIF(List, ">"&List)+1))=(COUNTIF(List, ">"&List)+1), 0, 1), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.
=IF(COUNT(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""))>(ROW(List)-ROW(List_start)), INDEX(List, MATCH(SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), (ROW(List)-ROW(List_start)+1)), IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), COUNTIF(List, "<"&List)+1, ""), 0)), "")
Named ranges
List (A2:A10)
List_start (A2)
What is named ranges?
How to create a drop down list with values updated dynamically in excel 2007
- Click Data tab
- Click Data validation button
- Click "Data validation..."
- Select List in the "Allow:" window. See picture below.
- Type =OFFSET($B$2, 0, 0, COUNT(IF($B$2:$B$1000="", "", 1)), 1) in the "Source:" window
- Click OK!
Download example workbook
Download excel sample file for this tutorial.
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
This blog article is one out of five articles on the same subject.
Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel
Related posts:
- Create a unique distinct alphabetically sorted list, extracted from a column in excel
- Sort text cells alphabetically from two columns using excel array formula
- Create unique distinct list sorted based on text length using array formula in excel
- Filter a column and create a new unique list sorted from A to Z using array formula in excel
- Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
- Sorting numbers and text cells descending also removing blanks using array formula in excel
- Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Sorting text cells using array formula in excel
- Sorting numbers and text cells also removing blanks using array formula in excel




December 10th, 2009 at 3:30 am
I encountered an issue when using Excel 2007 where upon saving the workbook as an Excel Workbook (not 97-2003) the embedded count(if( ...)) formula causes only the first item in the drop down list to appear. This only happens upon opening the workbook and if you select data validation on the cell the issue is resolved. However, upon saving and reopening the problem reappears. The only workaround if to either remove the function or keep the workbook in 97-2003 format.
December 15th, 2009 at 8:47 pm
Josh, thanks for bringing this to my attention. I´ll try to solve this issue.