Sort a range from A to Z [Array formula]
Question: How do I sort a range alphabetically using excel array formula?
Answer:
Cell range $B$2:$E$5 contains text values in random order, the formula in cell B8 extracts values sorted from A to Z.
Array formula in B8:
Filter unique distinct values sorted from A to Z with no blanks from a multi-column and multi-row cell range:
Extract unique distinct values A to Z from a range and ignore blanks
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
Extract unique distinct values A to Z from a range and ignore blanks
How to enter an array formula
- Double click on cell B8
- Copy an paste above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
Learn how to filter unique distinct values from a multi-column and multi-row cell range:
Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]
Extract unique distinct values from a multi-column cell range
How to implement array formula to your workbook
If your list starts at, for example, F2. Change $B$8:B8 in the above formula to F2:$F$2.
The following article demonstrates how to filter a unique distinct list sorted from A to Z, from a multi-column and multi-row cell range:
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 […]
Extract a unique distinct list sorted from A-Z from range
How to copy array formula
Copy cell B8 and paste to cells below.
How to filter duplicate values from a multi-column and multi-row cell range:
Extract duplicates from a range
The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]
Extract duplicates from a range
Explaining formula in cell B8
Step 1 - Rank values based on their relative position if they were sorted
The COUNTIF function counts cells based on a condition, however, in this case, I am using a cell range instead of a single cell in the second argument.
Also, the ampersand & concatenates each value with a less than sign. This makes the COUNTIF function compare each value against the others, the number returned represents the corresponding values position if the list were sorted.
COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)
returns {1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}.
The image above shows the array in cell range B7:E10, "Apple" is 0 (zero) which is the first position if the list were sorted.
Step 2 - Extract the k-th smallest value in array
The SMALL function lets you get the k-th smallest value from a cell range or array.
SMALL(array, k)
SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))
The ROWS function allows you to keep track of the number of cells, the cell reference is expanding as we copy the formula and paste to cells below.
SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, ROWS($B$8:B8))
becomes
SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, 1) and returns 0 (zero).
Step 3 - Compare value to array
The logical expression in the following IF function determines if the value of the position corresponding to the k-th smallest value will be a row number or FALSE.
IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(0=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)
becomes
IF(0={1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, 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 4 - Extract the smallest number from array
The MIN function ignores boolean values TRUE and FALSE.
MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1))
becomes
MIN({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE})
and returns 2. We now know that the first value to be extracted is located on row 2 relative to cell range $B$2:$E$5.
Step 5 - Find column
In this case, the MATCH function returns the relative position horizontally of the value we are looking for.
MATCH(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)
becomes
MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)
becomes
MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},1),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)
becomes
MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)
I will be using the INDEX function to extract the values we need based on a row number.
MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)
becomes
MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,2,,1)),0)
becomes
MATCH(0,COUNTIF($B$2:$E$5,"<"&B3:E3),0)
becomes
MATCH(0,{6,15,0,11},0)
and returns 3. The value we are looking for is in column 3 relative to cell range $B$2:$E$5.
Step 6 - Return value based on row and column
INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0))
becomes
INDEX($B$2:$E$5, 2, 3)
and returns "Apple" in cell B8.
Download Excel *.xlsx file
This post shows you how to filter a multi-column and multi-row cell range based on frequency:
Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Sort a range based on value frequency
Recommended article
Array formulas allows you to do advanced calculations not possible with regular formulas.
Recommended article
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
This blog article is one out of five articles on the same subject.
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
Sort text cells alphabetically from two columns
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Populate drop down list with unique distinct values sorted from A to Z
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Create a unique distinct sorted list containing both numbers text removing blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
Lookup and return multiple values sorted in a custom order
Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Sort column based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
5 Responses to “Sort a range from A to Z [Array formula]”
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.
[…] = window.adsbygoogle || []).push({}); Hi, I'm working on a simple macro that would insert a simple sorting formula depending on the user selection. I took few lines of code to call a form enabling the range […]
Hi, What if I want to sort alphabetically from B2 to E5 and keep the words within the range B2:E5? Thanks
how i sort a column through an excel formula
and can u explain about tbl in your formula because i did not get that
if u now about autofilter through a formula please give the formula
Hi
Thank you for all the info your providing
How do I create a formula to consider a range of numbers, sort them in descending order then add up only the 6 highest numbers?
yours truely
Markus
in this case "Sort a range from A to Z [Array formula]"
how about remove blank cell ?
thanks