text-values2

Table of Contents

Sort a column using array formula

Two columns sorting by the second column

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

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. 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.

Read more related articles


Two columns sorting by the second column

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))))

Download excel *.xlsx

Two columns sorting by the second column.xlsx