Formula in B15:
copied down as far as needed.
What is named ranges?
How to customize the formula to your excel spreadsheet
Change the named range. If your list starts at F3, change $B$14:B14 in the above array formula to $F$2:F2.
Download excel example file.
Create a unique distinct text list from a range with both numerical and text values.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Counts the number of cells within a range that meet the given condition
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Returns the relative position of an item in an array that matches a specified value
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Checks whether a value is text, and returns TRUE or FALSE
ROW(reference) returns the rownumber of a reference
Returns the smallest number in a set of values. Ignores logical values and text
Checks whether a value is nontext (blank cells are not text) and returns True or False