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

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

### 16 Responses to “Sort two columns”

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

**Contact Oscar**

You can contact me through this contact form

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.

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

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}

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

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.

Sergio,

Get the example file:Sergio.xls

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,

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

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 numberFormula

=SMALL($A$2:$A$11, ROW(A1))

Sort from smallest to largest text lengthArray 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 formula1. 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