text-values2I 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.

=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0)) + Ctrl + Shift + Enter copied 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

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

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)

becomes

{MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"MM"

becomes

{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"}

becomes

{FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}

becomes

{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.

Next is {MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"BB"

becomes

{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

{FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,FALSE , TRUE, FALSE}

becomes

{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

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

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

=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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Create unique distinct list sorted based on text length using array formula in excel
  2. Identify largest text value in a column using array formula in excel
  3. Filter common text values in range 1 and in range 2 using array formula in excel
  4. Filter text values existing in range 1 but not in range 2 using array formula in excel
  5. Extract and sort text cells from a range containing both numerical and text values
  6. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
  7. Sort text values by length using array formula in excel
  8. Extract numbers and text from a range using array formula in excel