Unique distinct list sorted alphabetically based on a condition
Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users.
This is entered as a regular formula, however, it returns an array of values and extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array.
The array formula below is for earlier Excel versions, it filters values in column C based on the value in cell E3, the output is a sorted unique distinct list in cell E6 and below.
Array formula in cell E6:
Recommended post
How to create an array formula
- Double click on cell E6
- Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
- Press and hold Ctrl + Shift simultaneously
- Press Enter once.
- Release all keys.
There are now a beginning and ending curly bracket in the formula bar, like this: {=formula}
Don't enter these characters yourself.
Explaining array formula in cell E6
Read my explanation here: Create a unique distinct alphabetically sorted list, extracted from a column
Recommended reading:
Download excel *.xlsx file
Create a unique distinct alphabetically sorted list with criteria.xlsx
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.
Vlookup across multiple sheets
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Use a drop down list to search and return multiple values
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
Vlookup with multiple matches returns a different value
Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]
Lookup multiple values in different columns and return multiple values
Jason C asks: I have a set of data, like the one you used in the original example that also […]
Vlookup a cell range and return multiple values
VLOOKUP a multi-column range and return multiple values.
Use VLOOKUP and return multiple values sorted from A to Z
The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]
One Response to “Unique distinct list sorted alphabetically based on a condition”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] https://www.get-digital-help.com/2017/08/15/unique-distinct-list-sorted-alphabetically-and-based-on-… […]