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.
Array formula in D3:
How to create an array formula
- Copy array formula (Ctrl + c)
- Double click cell D2
- Paste array formula (Ctrl + v)
- Press and hold Ctrl + Shift
- Press 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-values2.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 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 unique distinct text list from a range containing both numerical and text values in excel
Recommended categories
When I try to put the distinct list on another sheet and copy down, it will not work. In downloaded sample there are {} that are at the beginning and the end, but when I copy and past and enter the sheet reference, those brackets go away, and the wheels fall off the bus. Can't figure out how to make it work.
Kevin,
thanks for bringing this post to my attention!
I have simplified the array formula and you will also find instructions on how to create an array formula.
Sir
Im having two excel files.In first file there are ID(column1),data1(column2),data2(column3),..data12(column12)...In second file Im having only ID(column1)(these ID will be present in first file).Now i want to extract the datas for these ID's from the first file.Please help me to get through this problem....because the file size also bigger it is tough to use ctrl+F...
harinishree,
Use the array formula provided here:
http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple
[...] then copy it across and down. This solution was taken from here and slightly altered to suit...... Unique list to be created from a column where an adjacent column has text cell values | Get Digital ... I hope that helps. Good luck. [...]