Question: I want a unique list to be created from a column where an adjacent column has text cell value?

Answer: See picture below.

Formula in D3:D14:

=INDEX(A2:A17, SMALL(IF(ISERROR(IF(MATCH(IF(ISTEXT(B2:B17), List, ""), IF(ISTEXT(B2:B17), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), "", IF(MATCH(IF(ISTEXT(B2:B17), List, ""), IF(ISTEXT(B2:B17), List, 0), 0)=ROW(List)-1, ROW(List)-1, "")), ROW()-1)) + CTRL + SHIFT + ENTER

Named ranges
List (A2:A17)
What is named ranges?

Download excel example file
unique-list-to-be-created-from-a-column-where-an-adjacent-column-has-text-cell-values.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

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

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

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

This blog article is one out of thirteen articles on the same subject "unique".

• How to extract a unique distinct list from a column in excel
  
• Extract a unique distinct list from two columns using excel 2007 array formula
  
• Extract a unique distinct list from three columns in excel
• Extract distinct unique sorted year and month list from a date series in excel
  
• Create a unique distinct list from a date range in excel
  
• Unique values from multiple columns using array formulas
  
• Extract a unique distinct list sorted from A-Z from range in excel
  
• Sort a range by occurence using array formula in excel
  
• Filter unique distinct values from two ranges combined in excel 2007
• Create a unique list and sort by occurrances from large to small
  
• Unique list to be created from a column where an adjacent column has text cell values
  
• Create unique list from column where an adjacent column meets criteria
  
• How to create a unique distinct list where other columns meet two criteria

Related posts:

1. Create unique distinct list from column where an adjacent column meets criteria
2. Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
3. Create a unique distinct text list from a range containing both numerical and text values in excel
4. Unique distinct list from a column sorted A to Z using array formula in excel
5. Extract and sort text cells from a range containing both numerical and text values
6. Unique distinct list sorted based on occurrance in a column in excel
7. Create and sort distinct list by adjacent cell value size
8. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
9. Create a list of all numbers or text in a column in excel
10. Count unique text values in a range containing both numerical and text values