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 a unique distinct text list from a range containing both numerical and text values in excel
2. Unique distinct list from a column sorted A to Z using array formula in excel
3. Unique distinct list sorted based on occurrance in a column in excel
4. Extract and sort text cells from a range containing both numerical and text values
5. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
6. Create a list of all numbers or text in a column in excel
7. Count unique text values in a range containing both numerical and text values
8. Sum adjacent values using multiple lookup text values in a column in excel
9. Count unique distinct text values in a range in excel