Unique distinct list sorted alphabetically based on a condition

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z.

1. Unique distinct list sorted alphabetically based on a condition - earlier Excel versions

The image above demonstrates a formula in cell G6 that extracts unique distinct values only if the corresponding value on the same row meets a given condition.

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 G3 and cells below.

Array formula in cell G3:

Recommended post

Recommended articles

Back to top

1.1 How to create an array formula

1. Double press with left mouse button on cell G3.
2. Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell.
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

There is now a beginning and ending curly bracket in the formula bar, like this: {=formula}
Don't enter these characters yourself, they appear automatically if you completed the above steps.

Back to top

1.2 Explaining array formula in cell G3

Step 1 - Count values

The COUNTIF function counts values based on a condition or criteria.

COUNTIF(range, criteria)

The range argument contains a cell reference that is both relative and absolute meaning it grows when the formula is copied to cells below. This makes the formula aware of the previous values above.

COUNTIF($G$2:G2, $C$3:$C$11)

becomes

COUNTIF("Unique distinct list sorted alphabetically",{"DD";"EE";"FF";"EE";"GG";"BB";"FF";"GG";"DD"})

and returns {0;0;0;0;0;0;0;0;0}. 0 (zero) means that no cells meet the given condition.

Step 2 - Check if values in array equals 0 (zero)

The equal sign compares the values in the array to 0 (zero). The output is a boolean value TRUE or FALSE.

COUNTIF($G$2:G2, $C$3:$C$11)=0

becomes

{0;0;0;0;0;0;0;0;0}=0

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 3 -

$B$3:$B$11=$E$3

Step 4 -

COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3)

Step 5 -

IF((COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), "")

Step 6 -

SMALL(IF((COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1)

Step 7 -

MATCH(SMALL(IF((COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0)

Step 8 -

INDEX($C$3:$C$11, MATCH(SMALL(IF((COUNTIF($G$2:G2, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0))

Read my explanation here: Create a unique distinct alphabetically sorted list, extracted from a column

Recommended reading:

Recommended articles

Back to top

2. Unique distinct list sorted alphabetically based on a condition - Excel 365

Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users.

The image above demonstrates a dynamic array formula that works only in Excel 365. Despite its name you simply enter it as a regular formula.

It filters values from column C based on the corresponding values in column B given the condition in cell E3. The output in cell G3 is a sorted unique distinct list from A to Z.

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.

Back to top

2.1 Explaining Excel 365 formula

Step 1 - Extract values based on a condition

The FILTER function extracts values based on a condition.

FILTER(C3:C11, E3=B3:B11)

Step 2 - Extract unique distinct values

The UNIQUE function returns an array of unique distinct values meaning duplicates are merged into one distinct value.

UNIQUE(FILTER(C3:C11, E3=B3:B11))

Step 3 - Sort values from A to Z

The SORT function returns an array of values sorted from A to Z in its default state.

SORT(UNIQUE(FILTER(C3:C11, E3=B3:B11)))

Back to top

3. Unique distinct list sorted alphabetically based on a range - Earlier Excel versions

The image above shows a formula in cell H3 that extracts values from column C it the corresponding numbers in column B meet a condition based on a numerical range specified in cells F3:F4.

The output in cell H3 and cells below as far as needed is a sorted unique distinct list from A to Z. Unique distinct values are all values, however, duplicate values are merged into one distinct value.

Formula in cell H3:

=INDEX($C$3:$C$11, MATCH(SMALL(IF((COUNTIF($H$2:H2, $C$3:$C$11)=0)*($B$3:$B$11>=$F$3)*($B$3:$B$11<=$F$4), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0))

Back to top

3.1 Explaining formula

Back to top

4. Unique distinct list sorted alphabetically based on a range - Excel 365

Formula in cell H3:

=SORT(UNIQUE(FILTER(C3:C11,(F3<=B3:B11)*(F4>=B3:B11))))

Back to top

4.1 Explaining Excel 365 formula

Step 1 -

F3<=B3:B11

Step 2 -

F4>=B3:B11

Step 3 -

(F3<=B3:B11)*(F4>=B3:B11)

Step 4 -

FILTER(C3:C11,(F3<=B3:B11)*(F4>=B3:B11))

Step 5 -

UNIQUE(FILTER(C3:C11,(F3<=B3:B11)*(F4>=B3:B11)))

Step 6 -

SORT(UNIQUE(FILTER(C3:C11,(F3<=B3:B11)*(F4>=B3:B11))))

Back to top

Get the Excel file

Create-a-unique-distinct-alphabetically-sorted-list-with-criteria-v2.xlsx

Back to top

Vlookup and return multiple values category

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.

Improve your VLOOKUP formula and return multiple values
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Vlookup with 2 or more lookup criteria and return multiple matches
VLOOKUP and return multiple matches based on many criteria.

Excel categories

Comments (1)