Unique list to be created from a column where an adjacent column has text cell values
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:
- Create unique distinct list from column where an adjacent column meets criteria
- Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
- Create a unique distinct text list from a range containing both numerical and text values in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Extract and sort text cells from a range containing both numerical and text values
- Unique distinct list sorted based on occurrance in a column in excel
- Create and sort distinct list by adjacent cell value size
- Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
- Create a list of all numbers or text in a column in excel
- Count unique text values in a range containing both numerical and text values




Leave a Reply