Extract a unique distinct list sorted from A-Z from range
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range B2:E5.
Array formula in B8:
Copy cell B8 and paste it to cells below as far as necessary.
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Explaining formula in cell B11
This formula consists of two parts, one extracts the row number and the other the column number needed to return the correct value.
INDEX(reference, row, col)
Step 1 to 6 shows how the row number is calculated, step 7 to 11 demonstrates how to calculate the column number.
Step 1 - Prevent duplicates
The COUNTIF function counts values based on a condition or criteria, in this case, we take into account previously displayed values in order to prevent duplicates in our output list.
COUNTIF($B$7:B7, $B$2:$E$5)=0
becomes
COUNTIF("Unique distinct", {"Banana", "Raspberry", "Banana", "Raspberry";"Grapefruit", "Banana", "Apple", "Grapefruit";"Blueberry", "Kiwifruit", "Raspberry", "Blackberry";"Raspberry", "Blueberry", "Blueberry", "Banana"})
becomes
{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}=0
and returns
{TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}.
Step 2 - Replace TRUE with the corresponding rank order
The following IF function returns the corresponding sort order if the list had been sorted from A to Z based on the logical expression. FALSE returns "" (nothing).
IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,"")
becomes
IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {1,12,1,12;9,1,0,9;6,11,12,5;12,6,6,1}+1,"")
becomes
IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {1,12,1,12;9,1,0,9;6,11,12,5;12,6,6,1}+1,"")
becomes
IF({TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE}, {2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2},"")
and returns
{2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}
Step 3 - Extract smallest value in array
The SMALL function extracts the k-th small number in array, in this case the second argument (k) is 1.
SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)
becomes
SMALL({2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, 1)
and returns 1.
Step 4 - Replace TRUE with the corresponding row number
The IF function uses a logical expression to determine which value (argument) to return.
IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(1=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(1={2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}, {1;2;3;4})
and returns
{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,2,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}
Step 5 - Find smallest value
The SMALL function finds the smallest value in the array ignoring the boolean values
SMALL(IF(SMALL(IF(COUNTIF($B$7:B7,$B$2:$E$5)=0,COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,""),1)=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)+1,ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1),1)
becomes
SMALL({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,2,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE},1)
and returns 2. This is the row number we need to extract the correct value from cell range B2:E5.
Step 6 - Return array
This step uses the INDEX function to return an array from a given row in cell range B2:E5.
INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1)
becomes
INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, 2, , 1)
becomes
INDEX({2,13,2,13;10,2,1,10;7,12,13,6;13,7,7,2}, 2, , 1)
and returns
{10,2,1,10}
Step 7 - Match value in array
This step uses the MATCH function to return the correct column number needed to extract the value we need from cell range B2:E5.
MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1), 0)
becomes
MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), {10,2,1,10}, 0)
becomes
MATCH(1, {10,2,1,10}, 0)
and returns 3. This is the column number we need.
Step 8 - Return value
INDEX($B$2:$E$5, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), MATCH(MIN(IF(COUNTIF($B$7:B7, $B$2:$E$5)>0, "", COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1)), INDEX(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, SMALL(IF(SMALL(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ""), 1)=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5)+1, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1), 1), , 1), 0), 1)
becomes
=INDEX($B$2:$E$5, 3, 2)
and returns "Apple" in cell B8.
Get Excel *.xlsx file
Extract a unique distinct list sorted alphabetically from a range.xlsx
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Excel categories
3 Responses to “Extract a unique distinct list sorted from A-Z from range”
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.
This is exactly what I've been looking for... almost. It breaks if there are blank cells in the named range. Is there a way to get this to work if there are blanks in the range?
Thanks.
Dave,
see this blog post: https://www.get-digital-help.com/extract-a-unique-distinct-list-sorted-alphabetically-removing-blanks-from-a-range-in-excel/
Oscar,
I need to sort from largest value to smallest and continue to have issues. I'm working with values and have flipped SMALL to LARGE and Min to MAX. I must be missing something simple, could you please point me in the correct direction?.
Appreciate all the help.
Alex