Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Question: How do I create a drop down list with unique distinct alphabetically sorted values?
Answer:
Excel array formula in B2
=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. See picture below
Create a dynamic named range
- Click "Formulas" tab
- Click "Name Manager"
- Click List
- Type =OFFSET(Sheet1!$A$2, 0, 0, COUNT(IF(Sheet1!$A$2:$A$1000="", "", 1)), 1) in "Refers to:" field.
- 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
- 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
Create dependent drop down lists containing unique distinct values in excel
Related posts:
- Create unique distinct list sorted based on text length using array formula in excel
- Create dependent drop down lists containing unique distinct values in excel
- Filter a column and create a new unique list sorted from A to Z using array formula in excel
- Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
- Create a unique distinct sorted list containing both numbers text removing blanks in excel
- Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Create a unique distinct text list from a range containing both numerical and text values in excel
- Filter unique distinct text values using “begins with” criterion in a range 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.
March 26th, 2010 at 10:29 pm
I want the drop down box to be on a different worksheet. But EXCEL won't allow OFFSET without having it all on one worksheet.
Is there some other way to have the lists on one sheet, get unique values and sort them, then reference them from a drop down on another worksheet? To make matter more fun, I need the ranges to be dynamic as the user may be adding new records.
Thanks!
Dianne
April 9th, 2010 at 1:55 pm
I have re-created the above formula but would like to create a range that is larger than the current data so that the unique list picks up all new data. Currently my unique list repeats the last line of data several times because the range name extends beyond it. Can this be done?
Thanks
Keels
April 9th, 2010 at 8:18 pm
Dianne,
I want the drop down box to be on a different worksheet. But EXCEL won't allow OFFSET without having it all on one worksheet.
It is not OFFSET, it is Data Validation that won´t allow having it all on one worksheet.
But there is a workaround, if you create another named range (for example, named unique) and type =OFFSET(Sheet1!$B$2, 0, 0, COUNT(IF(Sheet1!$B$2:$B$1000="", "", 1)), 1).
Create a drop down box on another sheet and type =unique in "source:"
To make matter more fun, I need the ranges to be dynamic as the user may be adding new records.
I have updated the post, the named range is now dynamic.
April 9th, 2010 at 8:35 pm
Keels,
I have re-created the above formula but would like to create a range that is larger than the current data so that the unique list picks up all new data. Currently my unique list repeats the last line of data several times because the range name extends beyond it. Can this be done?
I have updated this post, the named range is now dynamic.