Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
Array formula in B12:
Copy cell B12 and paste it down as far as needed.
What is named ranges?
How to implement array formula to your workbook
Change the named range and "begins with" cell reference ($D$9). If your list starts at, for example, F3. Change $B$11:B11 in the above formulas to F2:$F$2.
Download excel sample file for this article.
Extract unique distinct values begins with A in a range.xls
(Excel 97-2003 Workbook *.xls)
I don´t think this can be done with a range using pivot table, if so please comment.
Functions in this article
ROW(reference) Returns the rownumber of a reference
Counts the number of cells within a range that meet the given condition
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
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
Returns the smallest number in a set of values. Ignores logical values and text
LEFT(text;num_chars) Returns the specified number of characters from the start of textstring
Returns the number of characters in a text string