Sort a column alphabetically
Table of Contents
1. Sort a column - Excel 365
Excel 365 formula in cell D3:
The SORT function
Function syntax:
2. Sort a column using array formula
I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandoo´s article, he sorts text with a "helper" column. My goal with this article is to show you how to sort text cells alphabetically without a helper column.
Here is an example, cell range $A$2:$A$15 contains text values.
Here is how to automatically sort text cells without any user interaction in cell B2:
Array formula in cell B2:
This article demonstrates how to filter a unique distinct list sorted from A to Z:
Extract a unique distinct list sorted from A to Z
The following post shows you how to sort and filter duplicate values alphabetically:
Extract a list of alphabetically sorted duplicates from a column
Learn how to sort a multi-column cell range alphabetically and display the values in a single column:
Sort a range from A to Z [Array formula]
Sort a data set using an array formula:
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
Tip! You can sort a data set in any way you like it, if you convert it to an excel defined table: Excel table - Sort data
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Copy cell B2 and paste it down as far as needed.
Explaining array formula in cell B2
Step 1 - Count "smaller" values
COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition
COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)
becomes
COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,KK,II,CC,HH,LL,AA,NN}, "<"&MM,"<"&BB,"<"&VV,"<"&EE,"<"&TT,"<"&GG,"<"&FF,"<"&KK,"<"&II,"<"&CC,"<"&HH,"<"&LL,"<"&AA,"<"&NN)
The first value in this array formula is "MM". Let´s see what happens when COUNTIF calculates how many values is small than "MM".
COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"MM"
becomes
COUNTIF({MM"<"&"MM",BB"<"&"MM",VV"<"&"MM",EE"<"&"MM",TT"<"&"MM",GG"<"&"MM",FF"<"&"MM",KK"<"&"MM",II"<"&"MM",CC"<"&"MM",HH"<"&"MM",LL"<"&"MM",AA"<"&"MM",NN"<"&"MM"}
MM<MM is FALSE and BB<MM is TRUE and so on.. The array becomes:
COUNTIF({FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}
becomes
COUNTIF({0, 1, 0, 1,0, 1 , 1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0} and the total is 10 which is the first number in the returning array. 0+1+0+1+0+1+1+1+1+1+1+1+1+1+1+1+0 = 10
Next is COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"BB")
becomes
COUNTIF({MM"<"&"BB",BB"<"&"BB",VV"<"&"BB",EE"<"&"BB",TT"<"&"BB",GG"<"&"BB",FF"<"&"BB",KK"<"&"BB",II"<"&"BB",CC"<"&"BB",HH"<"&"BB",LL"<"&"BB",AA"<"&"BB",NN"<"&"BB"})
becomes
COUNTIF({FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,FALSE , TRUE, FALSE})
becomes
COUNTIF({0, 0, 0, 0,0, 0, 0,0,0,0,0,0,0,0,0,1,0}) and the total is 1 which is the second number in the returning array.
and so on...
COUNTIF($A$2:$A$15, "<"&$A$2:$A$15) returns this array:
{10, 1,13,3,12,5,4,8,7,2,6,9,0,11}
Step 2 - Return the k-th smallest number in array
SMALL(array,k) returns the k-th smallest row number in this data set.
SMALL({10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, ROW(1:1)) returns the smallest number in this data set, 0 (zero).
Step 3 - Find position in array
MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value
MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0)
becomes
MATCH(0, {10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, 0) and returns 13.
Step 4 - Return value
INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
=INDEX($A$2:$A$15, MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0))
becomes
INDEX($A$2:$A$15, 13)
and returns AA in cell B2.
Get Excel sample file for this tutorial
Sorting-text-cells-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)
3. Sort two columns by the second column - Excel 365
Excel 365 is a lot more easy to work with, the new SORTBY function makes everything so simple when it comes to sorting values.
Excel 365 dynamic array formula in cell E3:
The SORTBY function spills values below and to the right of cell E3 as far as needed. A #SPILL error indicates that one or more cells are not empty. Delete the nonempty cell values and the formula works again.
The SORTBY function
Function syntax:
4. Sort two columns by the second column
These formulas are for earlier Excel versions than Excel 365, the image above shows a list of dates and corresponding values in cell range A2:B9.
The formulas in cell range D2:D9 and E2:E9 sort the values from small to large based on column B. Note that you can sort numbers just using the SMALL function, however, this formula in cell D2 also sorts text values from A to Z.
Array Formula in cell E2:
Array Formula in cell D2:
Get excel *.xlsx
Two columns sorting by the second column.xlsx
6. Sort text values by length
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the most characters are at the top, and the value with the least amount of characters is at the bottom.
Excel 365 dynamic array formula in cell D3:
I recommend Excel 365 users read this article: Sort by word length for how the formula works step by step. The following formula is for earlier Excel versions.
Array formula in D3:
copied down as far as needed.
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 B2
Step 1 - Calculate the k-th largest string length
In order to return a new value in each cell, the formula uses an expanding cell reference to return the next largest value. The LEN function returns the character length of a value, the ROWS function returns the number of rows in a cell reference. The LARGE function returns the k-th largest value. LARGE( array, k)
LARGE(LEN($B$3:$B$20), ROWS($A$1:A1))
becomes
LARGE(LEN($B$3:$B$20), 1)
becomes
LARGE({2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}, 1)
and returns 6.
Step 2 - Check previously displayed values in cells above
The COUNTIF function counts cells in cell range based on a condition or criteria. The first argument contains an expanding cell reference that lets the formula keep track of already shown values.
COUNTIF($F$2:F2, $B$3:$B$20)
becomes
COUNTIF("Sorted length",{"AB"; "CDE"; "BNA"; "BA"; "DDED"; "KJH"; "LKJ"; "DFV"; "CV"; "TTRYTU"; "DVDS"; "SGE"; "TFT"; "CC"; "BNA"; "T"; "DSF"; "AB"})
and returns
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Step 3 - Check if the count of prior values are less than the total count of each value
(COUNTIF($D$2:D2,$B$3:$B$20)<COUNTIF($B$3:$B$20,$B$3:$B$20)
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}<{2; 1; 2; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 2; 1; 1; 2}
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.
Step 4 - Match length to array
The MATCH function returns the relative position in a cell range or array of a given value.
MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)
becomes
MATCH(6, LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0)
becomes
MATCH(6, LEN($B$3:$B$20)*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, 0)
becomes
MATCH(6, {2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, 0)
becomes
MATCH(6, {2; 3; 3; 2; 4; 3; 3; 3; 2; 6; 4; 3; 3; 2; 3; 1; 3; 2}, 0)
and returns 10.
Step 5 - Return value
The INDEX function returns a value based on row number (and column number if needed)
INDEX($B$3:$B$20, MATCH(LARGE(LEN($B$3:$B$20), ROWS($A$1:A1)), LEN($B$3:$B$20)*(COUNTIF($F$2:F2, $B$3:$B$20)<COUNTIF($B$3:$B$20, $B$3:$B$20)), 0))
becomes
INDEX($B$3:$B$20, 10)
amd returns TTRYTU in cell D3.
Get Excel *.xlsx file
Sort text based on lengthv2.xlsx
7. Sort two columns
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column B and random numbers in column C. The array formula in cell E3 sorts the text values in column B from A to Z, the array formula in cell F3 sorts the numbers in column C based on the adjacent value in column E.
In other words, the records are sorted based on the text value and then on numbers.
Array formula in E3:
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.
Array formula in F3:
Explaining formula in cell E3
Step 1 - Create an array of numbers representing the rank order if the list were sorted
The COUNTIF function counts values based on a condition or criteria, in this case, we use the < less than sign to compare the value against all other values.
COUNTIF($B$3:$B$16, "<"&$B$3:$B$16)
becomes
COUNTIF({"FF"; "BB"; "GG"; "EE"; "AA"; "GG"; "FF"; "BB"; "EE"; "BB"; "HH"; "EE"; "AA"; "GG"},{"<FF"; "<BB"; "<GG"; "<EE"; "<AA"; "<GG"; "<FF"; "<BB"; "<EE"; "<BB"; "<HH"; "<EE"; "<AA"; "<GG"})
and returns
{8; 2; 10; 5; 0; 10; 8; 2; 5; 2; 13; 5; 0; 10}.
Step 2 - Get the k-th smallest number from array
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ROWS($A$1:A1))
becomes
SMALL({8; 2; 10; 5; 0; 10; 8; 2; 5; 2; 13; 5; 0; 10}, ROWS($A$1:A1))
becomes
SMALL({8; 2; 10; 5; 0; 10; 8; 2; 5; 2; 13; 5; 0; 10}, 1)
and returns 0 (zero).
Step 3 - Find position of number in array
The MATCH function finds the relative position of a value in an array or cell range.
MATCH(SMALL(COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ROWS($A$1:A1)), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), 0)
becomes
MATCH(0, COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), 0)
becomes
MATCH(0, {8; 2; 10; 5; 0; 10; 8; 2; 5; 2; 13; 5; 0; 10}, 0)
and returns 5.
Step 4 - Get value
The INDEX function returns a value based on row number (and column number if needed)
INDEX($B$3:$B$16, MATCH(SMALL(COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), ROWS($A$1:A1)), COUNTIF($B$3:$B$16, "<"&$B$3:$B$16), 0))
becomes
INDEX($B$3:$B$16, 5)
and returns "AA" in cell E3.
The image above demonstrates a table containing text values in both columns, you need a different array formula to sort the second column now containing text values.
Array formula in E3:
copied down as far as needed.
Array formula in F3:
Get Excel *.xlsx file
Sort two columns using an array formula.xlsx
8. Sort records based on two columns
Ralee asks:
If there is information in adjacent columns, say:
Joel, AA, 7
Mark, FF, 31
Nick, AA, 7
with the possibility of matching sort criteria (columns 2 & 3), how would I display the corresponding data after the sort (sorting only columns 2 & 3) without repeats; that is,
Joel, AA, 7
Nick, AA, 7
Mark, FF, 31
Instead of:
Joel, AA, 7
Joel, AA, 7
Mark, FF, 31
?
Answer:
Excel 365 formula in cell B9:
Array formula in B9:
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.
Array formula in C9:
Array formula in D9:
Explaining array formula in cell B9
Step 1 - Create array containing numbers representing rank order if sorted
The COUNTIF function lets you count values based on a condition. The less than sign lets you compare values based on sort order.
COUNTIF($C$3:$C$5, "<"&$C$3:$C$5)
becomes
COUNTIF({" AA";" FF";" AA"},{"< AA";"< FF";"< AA"})
and returns
{0;2;0}
Step 2 - Create rank order based on second column
Integers represent the sort order of column C and decimal values represent the sort order of column D, combining them creates an array that lets you identify the sort order based on both columns.
1/(COUNTIF($D$3:$D$5, ">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)
becomes
1/({0;2;0}+1+ROW($D$3:$D$5)/65536)
becomes
1/({0;2;0}+1+{3;4;5}/65536)
becomes
1/({0;2;0}+1+{0.0000457763671875;0.00006103515625;0.0000762939453125})
becomes
1/({1.00004577636718;3.00006103515625;1.00007629394531})
and returns
{0.9999542257282;0.333326551787276;0.999923711875012}
Step 3 - Add arrays
COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536))
becomes
{0;2;0} + {0.9999542257282;0.333326551787276;0.999923711875012}
and returns
{0.9999542257282;2.33332655178728;0.999923711875012}
Step 4 - Extract k-th smallest value in array
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.
The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.
SMALL(COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),ROWS($A$1:A1))
becomes
SMALL({0.9999542257282;2.33332655178728;0.999923711875012},ROWS($A$1:A1))
becomes
SMALL({0.9999542257282;2.33332655178728;0.999923711875012},1)
and returns
0.999923711875012.
Step 5 - Find position of value in array
The MATCH function finds the relative position in a cell range or array.
MATCH(SMALL(COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),ROWS($A$1:A1)),COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),0)
becomes
MATCH(0.999923711875012,COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),0)
becomes
MATCH(0.999923711875012, {0.9999542257282;2.33332655178728;0.999923711875012},0)
and retrurns 3.
Step 6 - Return corresponding value based on position
The INDEX function returns a value based on a cell reference and column/row numbers.
INDEX($B$3:$B$5,MATCH(SMALL(COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),ROWS($A$1:A1)),COUNTIF($C$3:$C$5,"<"&$C$3:$C$5)+(1/(COUNTIF($D$3:$D$5,">"&$D$3:$D$5)+1+ROW($D$3:$D$5)/65536)),0))
becomes
INDEX($B$3:$B$5,3)
and returns "Nick in cell B9.
Sort values category
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Excel categories
102 Responses to “Sort a column alphabetically”
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.
Oscar,
In the part of the formula: 1/(COUNTIF(Qty,">"&Qty)+1))
What is the purpose or logic for divide 1 by the countif (Qty,....)
and
what is the reason for the +1 par of the same formula?
thanks,
Chrisham
chrisham,
Sorry for the late answer.
What is the purpose or logic for divide 1 by the countif (Qty,....)?
The purpose is to sort and find the right "Qty" value for each "Description" value. I know this is a short answer but use "Evaluate Formula" to see each step in formula calculation.
what is the reason for the +1 par of the same formula?
COUNTIF(Qty, ">"&Qty) returns an array containing a zero. 1/0 returns an error. So I had to add 1 to the whole array.
This one did not seem to work. in your excel example file I imputed: a, h,II,b,j,k,c,l,m,d,n,f,p in column A.
The result was
a,a,b,c,d,f,h,II,j,k,l,m,n
but there are not two a's in column a and it seemed to drop p completely... it seems some how shifted... can you help? Thanks.
oops... I did not mean to leave my number for anyone other than you, can you remove it? (how embarrassing)
I figured out the problem I was having. It was simply that if "list" has 15 rows, then both my list content and my output cells NEED to contain 15 items or else funky things happen.
HOWEVER
When I try to do a lengthy list, say of 500 rows, I run into circular reference issues... any thoughts?
Matt,
This one did not seem to work. in your excel example file I imputed: a, h,II,b,j,k,c,l,m,d,n,f,p in column A.
The result was
a,a,b,c,d,f,h,II,j,k,l,m,n
but there are not two a's in column a and it seemed to drop p completely... it seems some how shifted... can you help? Thanks.
The named range contains a blank cell, that is why you get strange results. Change the named range cell reference or replace the named range with a cell reference.
Hi,
I would like to output the unique ID of the sorted list. But when duplicates exist in the original list (in this case ID 1 and 2) it ranks both the BBs as 1.
The formula i have in the Sorted ID List column is =MATCH(SMALL(COUNTIF(List,"<"&List),ROW(1:1)),COUNTIF(List,"<"&List),0)
CURRENT OUTPUT:
ID Text Sorted
values ID list
1 BB 13
2 BB 1
3 VV 1
4 EE 10
5 TT 4
6 GG 7
7 FF 6
8 KK 11
9 II 9
10 CC 8
11 HH 12
12 LL 14
13 AA 5
14 NN 3
Desired Output:
ID Text Sorted
values ID list
1 BB 13
2 BB 1
3 VV 2
4 EE 10
5 TT 4
6 GG 7
7 FF 6
8 KK 11
9 II 9
10 CC 8
11 HH 12
12 LL 14
13 AA 5
14 NN 3
can you please suggest something?
Raj,
=MATCH(SMALL(COUNTIF(List, "<"&List)+ROW(List)/1048576, ROW(1:1)), COUNTIF(List, "<"&List)+ROW(List)/1048576, 0) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.
adding a countblank statement ensures this working for ranges with blank cells (otherwise the first value will be repeated.
ROW(1:1)+COUNTBLANK(List)
Thanks Oscar, and Thanks Miel for solving my probleme! Cheers and Merry Xmas!
If there is information in adjacent columns, say:
Joel, AA, 7
Mark, FF, 31
Nick, AA, 7
with the possibility of matching sort criteria (columns 2 & 3), how would I display the corresponding data after the sort (sorting only columns 2 & 3) without repeats; that is,
Joel, AA, 7
Nick, AA, 7
Mark, FF, 31
Instead of:
Joel, AA, 7
Joel, AA, 7
Mark, FF, 31
?
Ralee,
read this post: Sort values in parallel in excel, part 2
Great! That's exactly what I needed.
~Thanks
The array formula does not need the SMALL() function to work. You can use this instead : =INDEX(List,MATCH(ROW(List)-MIN(ROW(List)),COUNTIF(List,"<"&List),0)) + cse
Thanks for the COUNTIF() trick.
Jeanbar,
You are right!
Thanks for your contribution!
Oscar,
I found out that the formula you gave to Raj is not working. I think it is due to the "ROW(1:1)" part of it which means nothing. If you want to have a valid rank for the SMALL(Array;rank) you should declare ROW(INDIRECT("1:"&ROWS(List)) as a vector of ranks.
The formula working in my environment is: SORTED LIST =
T(INDEX(LIST,MATCH(SMALL(COUNTIF(LIST,"<"&LIST)+ROW(LIST)/MAX(ROW(LIST)),ROW(INDIRECT("1:"&ROWS(LIST)))),COUNTIF(LIST,"<"&LIST)+ROW(LIST)/MAX(ROW(LIST)),0))) +cse
NB: if one or more entries are blank, they come first in the sorted list.
Jeanbar,
The formula is working here. ROW(1:1) is part of small function which returns k-th smallest value in the dataset. When formula is copied down, Row(1:1) changes to Row(2:2) and then to Row(3:3).
Row(1:1) equals 1
Row(2:2) equals 2
and so on..
Row(1:1) is a relative cell reference.
Maybe you copied the formula into all cells and then presssed CTRL + SHIFT + ENTER?
How to use the formula:
Copy array formula into cell B2 and press Ctrl + Shift + Enter.
Copy CELL B2 and paste it to the cells below, as far as needed.
This is great:)formula worked for me.
But can you explain why we press CTRL + SHIFT + ENTER?
im not an expert.
Jaseel,
I am happy you got the formula working.
The formula is an array formula. To enter the formula as an array formula, type the formula in a cell and then press and hold CTRL + SHIFT and then press ENTER once.
Read more about array formulas: Array formulas
Hi Oscar,
Your formula is great, but I need something greater (I think)
I want to sort multiple columns using formula and even multiple sort types, is it possible?
For Example:
Unsorted Data
==============
Col 1 Col 2
-------------------
B 2
A 1
C 2
A 2
B 1
C 2
Sorted Data must be :
Col 1 Col 2
---------------------
A 1
A 2
B 1
B 2
C 2
C 2
Actually I need the sort type of Col 1 ASC then Col 2 DESC then Col 3 ASC then Col 4 DESC then Col 5 ASC.
But If you could give just the Col 1 ASC then Col 2 ASC formula, it would help me a lot...
Thx
FB,
I think this post answers your question: Sort values in parallel (array formula)
Dear Oscar,
Greatest Help...
Thank you very much
Oscar, can you help me understand the logic behind comparing the array values to themselves? Descr, "<"&Descr
Is there an article I can read somewhere?
Kris,
COUNTIF(Descr, "<"&Descr) compares values in an array and ranks them on their order if they were sorted. COUNTIF({"FF";"BB";"GG";"EE";"AA";"GG";"FF";"BB";"EE";"BB";"HH";"EE";"AA";"GG"}, "<"&{"FF";"BB";"GG";"EE";"AA";"GG";"FF";"BB";"EE";"BB";"HH";"EE";"AA";"GG"}) COUNTIF({"FF"; "BB"; "GG"; "EE"; "AA"; "GG"; "FF"; "BB"; "EE"; "BB"; "HH"; "EE"; "AA"; "GG"}, {; "<"&"BB"; "<"&"GG"; "<"&"EE"; "<"&"AA"; "<"&"GG"; "<"&"FF"; "<"&"BB"; "<"&"EE"; "<"&"BB"; "<"&"HH"; "<"&"EE"; "<"&"AA"; "<"&"GG"}) FF, first value in array becomes: {"FF"&"<"&"FF"; "BB"&"<"&"FF"; "GG"&"<"&"FF"; "EE"&"<"&"FF"; "AA"&"<"&"FF"; "GG"&"<"&"FF"; "FF"&"<"&"FF"; "BB"&"<"&"FF"; "EE"&"<"&"FF"; "BB"&"<"&"FF"; "HH"&"<"&"FF"; "EE"&"<"&"FF"; "AA"&"<"&"FF"; "GG"&"<"&"FF"} becomes {0;1;0;1;1;0;0;1;1;1;0;1;1;0} and the total is 8. COUNTIF(Descr, "<"&Descr) returns {8;2;10;5;0;10;8;2;5;2;13;5;0;10}
there is an error is your named ranges
Qty (B2:B15) should be Qty (d2:d15)
Descr (B2:B15) should be Descr (c2:c15)
Paul,
thanks!
Thanks for formula however if the list has blanks it puts them up top and the data is in the bottom. Since I am using dynamic validation how can i put them up top so the user doesn't have to scroll down for selection?
rivers,
=INDEX(List, MATCH(SMALL(IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), ROW(1:1)), IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), 0))
I'm new to excel array techniques (and loving it), so please forgive me if my question's solution is obvious, but your tip goes a long way toward answering it.
I need to perform an approximate match upon an unsorted table and then lookup an associated value. Because the unsorted table is dynamic and will gain many new rows over time, I need the solution to be formula-based, with no intermediate tables.
Is there a way to build upon this tip to have the sorted arrays in memory? My current strategy would be to use LOOKUP vector function upon the two like-sorted arrays. A MrExcel/ExcelIsFun video demonstrates a related solution (see link below), but the table values are numeric and are sorted directly using SMALL() which won't work upon text data.
Many Thanks!
Mr Excel & excelisfun Trick 36: VLOOKUP w Approximate Match & Unsorted Table
https://www.youtube.com/watch?v=rxhL72gvM5E
Markosys,
That´s a question I don´t have an answer to. It seems overly complicated to create a 2dimensional sorted array when you can use MATCH function to lookup values in an unsorted array.
=INDEX(array, MATCH(lookup_value, array, 0))
Oscar, how would i change your formula if i want to work with COLUMNS rather than ROW... e.g. I have a single row of data in 5 columns, CDAEB and i want formulas in the next 5 columns that results in ABCDE.
I tried swapping the match reference and row(1:1) to column(1:1) but it doesnt work... many thanks.
Themin,
=INDEX($A$1:$A$5, MATCH(SMALL(COUNTIF($A$1:$A$5, "<"&$A$1:$A$5), COLUMN(A:A)), COUNTIF($A$1:$A$5, "<"&$A$1:$A$5), 0)) Get the example file *.xlsx Themin.xlsx
Dear Oscar,
I need array formula if there space empty row in the part of rows 2 until 15, say row 5 and row 10 . so the formula generate exactly the same as your Example.
Please help me on this.
and how can i Sort values Parallel with 4 (four) column or more.
Please tell me why you put "65536" in your array formula
thx
Sergio,
Get the example file:
Sergio.xls
what is the purpose of [COUNTIF(List, "<"&List)]? I tried the same overall formula but I replaced this part with a direct reference to the named range and switched SMALL for LARGE which resulted in the formula below:
=INDEX(List, MATCH(LARGE(List, ROW(1:1)), List, 0))
This produced the same results as your original formula which is what made me wonder why the [COUNTIF(List, "<"&List)] portion needs to be there at all.
Also, in my attempt to understand what this part of the formula is doing I came across something that confused me even more. If the section in question [COUNTIF(List, "<"&List)] is put into cells by itself, without the [INDEX(List...], then the results are dependant on the row where the formula was originally inputted and where the formula was copied. I'm assuming this is because the countif outputs an array but I don't understand why. Can you explain this for me?
I should add that when I said the modified formula produced the same results as the original I meant that both formula's had the SMALL function swapped for the LARGE function.
Hi
I see you are solving a lot of complicated tasks in excel. Though I checked also the link https://www.get-digital-help.com/2010/01/12/sorts-values-in-parallel-array-formula/, and many others I can not find the solution for my problem.
I'll explain it a litlle better what i have and what i want to get:
column A ; column B
2 1
2 17
7 11
7 26
3 5
3 19
2 1
2 18
i would like to get first column sorted ascending and second column belonging number to the number of the first column:
column A ; column B
2 1
2 17
2 1
2 18
3 5
3 19
7 11
7 26
I hope you have time to look in to it.
Best regards, Jernej
Jernej,
Is this what you are looking for?
Jernej.xls
in the sort order column i used the array formula you gave to Raj=MATCH(SMALL(COUNTIF(List, "<"&List)+ROW(List)/1048576, ROW(1:1)), COUNTIF(List, "<"&List)+ROW(List)/1048576, 0), and the second column is the text that needs sorting in alphabetical order, in the third column i placed the formula that Chandoo used in his article =VLOOKUP(ROW()-ROW($K$1),$I$2:$J$12,2,FALSE. By doing this helped me to avoid problems with non unique values. I am wondering if u have a way for me to by bass the help column altogether?
Sort Order Info needing Sorting Sorted Info
1 WORK COMPLETE WORK COMPLETE
2 WORK COMPLETE WORK COMPLETE
3 WORK COMPLETE WORK COMPLETE
4 WORK COMPLETE WORK COMPLETE
5 WORK COMPLETE WORK COMPLETE
6 WORK COMPLETE WORK COMPLETE
7 WORK COMPLETE WORK COMPLETE
8 WORK COMPLETE WORK COMPLETE
9 WORK COMPLETE WORK COMPLETE
10 WORK COMPLETE WORK COMPLETE
11 WORK COMPLETE WORK COMPLETE
Hi!
I thank you all for your effort.
I got the right result with the "trick" Anonymous stated in his post. Thanks a lot for answering. This equation will reduce my time behind the screen significantly :).
Best regards, Jernej
Jernej,
Can you give an excel example of the final thing that gave you your desired results.
You wanted above "i would like to get first column sorted ascending and second column belonging number to the number of the first column:
column A ; column B
2 1
2 17
2 1
2 18
3 5
3 19
7 11
7 26"
I need to do the same thing. Sort the first column and then after the sorting is done, put the associated 2nd column next to the first, as you mentioned. I have been successful in sorting the first column.
please help
thanks
could you give me formula for sorting the text with spacing row, and I need next column sorting by first column :
col1 col2
anne 2
marie 3
spacing (text)
jolie 2
linda 5
anne 5
i need name of "anne" converge consecutive in next column like this :
anne 2
anne 5
jolie 2 and so on
please help
thanks
pls can u help how to edit this formula
hi sir pls help how edit this shorting formula. when me was pasting this formula in differnt sheet it show #NA. how i need to apply in my sheet pls expl
great one i love it.one small one i required has u had given a xls to sergio in that if there is an empty in any one error is generated like #NUM!. but me want empty space instead of #NUM!. pls help pls
pardhu,
Create a named range (List) using your cell reference.
Then apply my formula.
pardhu,
Excel 2007:
=IFERROR(INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0)), "") Excel 2003: =IF(ISERROR(INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0))), "", INDEX(Descr, MATCH(SMALL(IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), ROW(1:1)), IF(Descr="", "", COUNTIF(Descr, "<"&Descr)), 0)))
THANK YOU SO MUCH SIR .......... YOUR GREAT
Hi,
I have this data:
12-04-12 1
19-04-12 3
23-04-12 2
01-05-12 1
07-05-12 1
15-05-12 1
05-06-12 1
27-08-12 1
How to get the two column output sorted on the second column using array formula?
Best Regards
Kamal,
See this section: Two columns sorting by the second column
Hello,
I love the formula!
I'm having issues with some of my data being numbers stored as text.
This is the data I have:
AK2
CB4D
23
207
H1
Returned sort:
23
23
AK2
CB4D
H1
Any idea how to solve that?
Cheers,
Ryan,
See attached *.xlsx file
ryan.xlsx
i am very new ing advanced excel. i want to sort from largest to small, but i can't. i need your guidence. Thanx.
Uygar,
Sort from smallest to largest number
Formula
=SMALL($A$2:$A$11, ROW(A1))
Sort from smallest to largest text length
Array formula
=INDEX($A$2:$A$9, MATCH(SMALL(LEN($A$2:$A$9), ROW(A1)), IF(COUNTIF($C$1:C1, $A$2:$A$9)=COUNTIF($A$2:$A$9, $A$2:$A$9), "", LEN($A$2:$A$9)), 0))
How to create an array formula
1. Select cell C2
2. Paste formula
3. Press and hold Ctrl + Shift
4. Press Enter
Get the Excel *.xlsx file
Sort-from-smallest-to-largest.xlsx
Sort 1 Sort 2
1350 1350
1351 1351
1352 1352
1353 1353
1354 1354
1355 1355
9999 1357
1357 1357
1358 1358
1359 1359
1360 1360
1361 1361
1362 1362
1363 1363
1364 1364
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
I am trying to sort the first column into the second. Wherever the number is 9999 it duplicates what would be the next number. It should be shifting up and moving the 9999 to the end. I am working with data sets of about 200 numbers.
Thanks!
Mark,
Copy cell D1. Paste to cell range D2:D22.
hi, i have a text file and wanted to sort the text file info into different location of row and column in excel.
may i know how can i do that?
inside the text file example:-
header
a1
123
456
a2
123
456
a3
123
456
789
i want to sort the data and display it using the a1,a2,a3 with the content inside
expected output in excel sheet as follow:-
cella b c
a1 123 456
a2 123 456
thank you inadvance for your help
A1, A2, A3, and so on are the row delimiters?
Using all your values:
a b c d
a1 123 456
a2 123 456
a3 123 456 789
Correct?
I have a data in cell A1, A2 & A3. How to rearrange data in ascending order in same cell. Please help how to solve without VBA or Macros.
DBCA
IJNM
ALAM
Output in should be like this.
ABCD
IJMN
AALM
Thanks in advance
ROHIT,
I have no clue but I am sure it is possible with vba.
Hi,
I know its possible, but I can't manage to do it :) I have in col A 200 names, but between them, there are empty cells, and they need to stay. Is it possible in the sort (col B) to sort from A-Z without the empty cells.
With the formula like it is now, he will put the empty cells above A.
=INDEX($A$2:$A$200; MATCH(SMALL(IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); ROW(90:90)); IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); 0))
The formula should ignore the empty cells, or delte them in the array. Thanks for helping out :)
Your formula ignores empty cells, I changed ROW(90:90) to ROW(1:1).
=INDEX($A$2:$A$200; MATCH(SMALL(IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); ROW(1:1)); IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); 0)) Did you create the array formula in one cell and then copy/paste the cell (not the formula) down as far as needed?
Hello,
I have a table of example data: Column A contains Names, Columns B and C both contain Weight and Height data respectively. How can I sort Columns B and C to give an accurate combination of both Weight and Height from Largest -> Smallest.
Many thanks!
Jo
Joseph,
Here is a post about sorting values in parallel:
Sort values in parallel (array formula)
Get the Excel example file *.xlsx
Sort-values-in-parallel-Joseph.xlsx
Many thanks Oscar!
Jo
My solution is much simpler.
1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function :D
2. My sorted numbers shall be in cells J29-O29,
3. The formula for cell J29 is
=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))
3. The formula for cell K29 is … just convert all the “=1″ into “=2″
4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.
5. The RANK function will rank every cell in the range. There will not be any unranked. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the first number of the same rank.
Hope this would help u guys.
Note: In order to make it work for words, you need to convert Words into ASCII by using the function CODE.
Just some futher explanations ..
1. the formula in the first sorted cell J29 will seek which number is ranked #1 and the second cell, it would seek the number ranks #2 and so on.
cJ29- "=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1.."
cK29- "=IF(RANK($B29,$B29:$G29,1)=2,$B29,IF(RANK($C29,$B29:$G29,1)=2.."
The IF statement checks every one of the ranks in the unsorted cells B29 - G29 .
i used this formula successfully. but i can't understand why (--(sum(a2=$a$2:a2)) has been used in this formula. and what the logic of (--(sum(a2=$a$2:a2)). I used row function in place of (--(sum(a2=$a$2:a2)), the result was not proper when the values were same.
Dear sir,
I have another question for the foumula copied below,
Array Formula in cell E2:
=INDEX($B$2:$B$9, MATCH(SMALL(COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), ROW(1:1)), COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), 0))
Array Formula in cell D2:
=INDEX($A$2:$A$9, SMALL(IF(E2=$B$2:$B$9, MATCH(ROW($B$2:$B$9), ROW($B$2:$B$9))), SUM(--(E2=$E$2:E2))))
sir. The sorting of first list can be done only small function such as {=SMALL($b$2:$b$9,ROW(1:9))}, why the rest of the formula is used.
once again, thanks a lot, sir.
The sorting of first list can be done only small function such as {=SMALL($b$2:$b$9,ROW(1:9))}, why the rest of the formula is used?
Your formula works only for numbers, my formula works for text also.
Hi - I tried pasting your formula for my purposes, but it returned every row with the same thing - the first item in the list... any idea why?
sc,
Paste the formula in one cell and enter it as an array formula. Copy the cell (not the array formula) and paste to cells below.
When you copy the cell the relative cell reference changes, that will not happen i you copy the array formula.
Im using the formula and I received a 0.
=INDEX(F2:F1000,MATCH(ROW(F2:F1000)-MIN(ROW(F2:F1000)),COUNTIF(F2:F1000,"<"&F2:F1000),0))
Instead of using "list", I put the range manually.
Now I received only the first word, but then I just got #NUM!
Well I can do it, but there's a problem, i want it alphanumeric order, could be possible? for example, if you hace the same words but at the end different number. Example
VIC_TXT[1]
VIC_TXT[2]
....
Regards,
Daniel,
Array formula in cell C3:
Expand the array to A1:B9 and use the Index ability to use different columns. Then you can sort how many column you need. (sorry for bad english) :)
Hej Pelle Bergkvist
Yes, but you can only use a single column at a time, right?
I think I described the technique here:
https://www.get-digital-help.com/2013/03/15/index-function-explained/#ex5
How do you sort an array generated by formulas though? Is that possible?
How do you sort an array generated by formulas though? Is that possible? With an array formula that is.
All I get are zeros
Bryant,
How do you sort an array generated by formulas though?
Can you share your array formula?
Hi Oscar
This is my problem
Dates Values Dates Values
12/04/2012 1 1 12/04/2012 1 1
19/04/2012 2 3 01/05/2012 4 1
23/04/2012 3 2 15/05/2012 6 1
01/05/2012 4 1 05/06/2012 7 1
07/05/2012 5 3 23/04/2012 3 2
15/05/2012 6 1 19/04/2012 2 3
05/06/2012 7 1 07/05/2012 5 3
27/08/2012 8 3 27/08/2012 8 3
1
12/04/2012 1
01/05/2012 4
15/05/2012 6
05/06/2012 7
2
23/04/2012 3
Like sort it like this
3 with numbers changing
19/04/2012 2
07/05/2012 5
27/08/2012 8
I tried using the formula for sorting two columns using the second column, with the second column having the numbers for the ranking and the first column having the text that should be ranked along with it.
The formula worked fine for small lists, but started producing only a "1" in every cell when used with a large array (>2,000 entries).
I have 64 bit Excel and am surprised the program cannot handle the formula. In fact, using the SORT feature from the pull-down menus works just fine, so it doesn't seem to me that the program should be unable to do this. Rather, it is simply my inability to make your formula work. So do you have any suggestions?
The problem you are facing is in the "ROW" function within the formula. Since it is an array formula, it is only recognizing the first smallest number across board. Try replacing the "ROW(1:1)" with "ROW(List)-Row(1)" this is assuming your data "List" named range starts from row 2. Hope this helps.
Just wanted to take a moment to thank you for this guide, it condesnsed what would have taken a day into 10 minutes of work. Very easy to follow for someone new to Excell, thanks again!
Thanks for the post. I would apply the formulas in excel for sorting. This helps me for customizing sorting.
Hi,
How can I make it work without dragging the formula down from B1 cell? This is what I have on B1 cell. It works but it only shows the first item. Thanks
=ArrayFormula(INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0)))
I have a 3 column list that was extrapolated from several worksheets within my workbook. There are 3 columns and 30 rows, Col A-Gender (f, m. my, fy), Col B-Name, Col C-Score. I have hidden formulas for sorting highest to lowest scores as a group, now I need this list sorted by gender from highest to lowest score. In summary, I need 1st and 2nd place for each of the 4 gender groups. I hope this is clear, can you help?
I want to remove double value by fomular, so how i have to do ?
Thanks a lot for the idea. Did not work for me as it is, mainly because row(1:1) did not change in the array formula (so it was the first element of the sorted set in all cells). Have to add a column with list numbers (1,2,3, etc) and refer to this column instead of row(1:1) in the formula. Everything else was ok, thanks!
enhancing the formula of the original excellent post, the following sorts a single-column list by Asc order
(for Desc order change the '' below )
-range 'List' covers the original list to be sorted
-the formula is entered as a single array formula over the range where the output is needed.
=INDEX(List, MATCH(SMALL(COUNTIF(List, "<="&List),
ROW(List) - ROW(OFFSET(List,0,0,1,1))+1),
COUNTIF(List, "<="&List),
0)
)
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the below )"
and again:
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the \ below )"
and again:
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the LessThan symbol to a GreaterThan symbol )"
how do i add your formula =INDEX($B$2:$B$9, MATCH(SMALL(COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), ROW(1:1)), COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), 0)) to my formula as below
=IFERROR(INDEX(table1,SMALL(IF(COUNTIF(B587,table2)*COUNTIF(C587,table3)*COUNTIF($AD$2,table4), ROW(table3)-ROW($B$2)+1),COLUMN($B$1))),"")
i add another table5 to count ascending
Dear Oscar,
The suggested formula is going to fit in those countries who uses a COMMA as Decimal mark. (France, Austria, Germany etc').
In Israel (and in many more countries) we use a DOT as a decimal mark.
I would suggest to add this as a comment beside your suggested formula.
Have a nice weekend,
--------------------------
Michael (Micky) Avidan
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
Michael Avidan,
thank you for pointing that out. I will update this article.