Sorting text cells using array formula in excel

Filed in Excel, Sort values on Mar.27, 2009. Email This article to a Friend

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 a example list containing text values in column A.

Here is how to automatically sort text cells without any user interaction in cell B2:

Array formula in cell B2:

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

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

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

Step 1 - Count "smaller" values

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

COUNTIF(List, "<"&List)

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(List, "<"&List) 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(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 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(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))

becomes

=INDEX(List, 13) and returns AA in cell B2.

Named ranges

List (A2:A15)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel sample file for this tutorial

Sorting-text-cells-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

This blog article is one out of five articles on the same subject.

Comments (22)

22 Responses to “Sorting text cells using array formula in excel”

MATT Says:
July 14th, 2010 at 8:35 pm

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.
MATT Says:
July 14th, 2010 at 8:39 pm

oops... I did not mean to leave my number for anyone other than you, can you remove it? (how embarrassing)
MATT Says:
July 14th, 2010 at 9:19 pm

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?
Oscar Says:
July 15th, 2010 at 2:09 pm

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.
Raj Says:
July 30th, 2010 at 7:03 pm

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?
Oscar Says:
August 5th, 2010 at 9:07 am

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.
Miel Says:
September 10th, 2010 at 4:47 pm

adding a countblank statement ensures this working for ranges with blank cells (otherwise the first value will be repeated.
ROW(1:1)+COUNTBLANK(List)
Jeanbar Says:
December 19th, 2010 at 4:14 pm

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.
Oscar Says:
December 19th, 2010 at 8:31 pm

Jeanbar,
You are right!
Thanks for your contribution!
Jeanbar Says:
December 27th, 2010 at 8:02 pm

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.
Oscar Says:
December 28th, 2010 at 10:29 pm

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.
Jaseel Says:
March 2nd, 2011 at 8:00 pm

This is great:)formula worked for me.
But can you explain why we press CTRL + SHIFT + ENTER?
im not an expert.
Oscar Says:
March 2nd, 2011 at 9:25 pm

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
FB Says:
April 12th, 2011 at 5:10 pm

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
Oscar Says:
April 12th, 2011 at 5:40 pm

FB,
I think this post answers your question: Sort values in parallel (array formula)
FB Says:
April 12th, 2011 at 5:54 pm

Dear Oscar,
Greatest Help...
Thank you very much
rivers Says:
June 14th, 2011 at 1:21 pm

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?
Oscar Says:
June 14th, 2011 at 1:43 pm

rivers,
=INDEX(List, MATCH(SMALL(IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), ROW(1:1)), IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), 0))
Themin Says:
January 11th, 2012 at 1:33 pm

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.
Oscar Says:
January 13th, 2012 at 1:38 pm

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))
Download example file *.xlsx
Themin.xlsx

Spencer Says:
January 30th, 2012 at 10:10 pm

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?

Spencer Says:
January 30th, 2012 at 10:13 pm

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.

Most Popular Blog Posts