## Sorting text cells using array formula in excel

**Table of Contents**

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:**

**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(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.*

- Sorting text cells using array formula in excel

- Sorting numbers and text cells also removing blanks using array formula in excel
- Sorting numbers and text cells descending also removing blanks using array formula in excel
- Sort text cells alphabetically from two columns using excel array formula
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Sort a range from A to Z using array formula in excel

Read more related articles

Two columns sorting by the second column

**Array Formula in cell E2:**

**Array Formula in cell D2:**

**Download excel *.xlsx**

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)

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

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

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

Download example file *.xlsx

Themin.xlsx

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 http://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

pardhu,

Create a named range (List) using your cell reference.

Then apply my formula.

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

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)

Download example file *.xlsxSort-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:

http://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?