Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this article. From a range spanning multiple rows and columns I extract a unique distinct list sorted from A-Z.
Array formula in B8:
Copy cell B8 and paste it down as far as necessary.
What is named ranges?
How to implement array formula to your workbook
Change the named range. If your list starts at, for example, F3. Change $B$7:B7 in the above formulas to F2:$F$2.
Download excel sample file for this tutorial.
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Counts the number of cells within a range that meet the given condition
Adds all the numbers in a range of cells
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
MATCH(lookup_value;lookup_array; [match_type] 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
SMALL(array,k) Returns the k-th smallest row number in this data set.
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