## Sort values in parallel (array formula)

**Table of contents**

- How to sort a table by Column 1 and then by Column 2 (array formula)
- How to sort a table by Column 1 and then by Column 2 (Right-click / Sort /Custom sort...)

### 1. How to sort a table by Column 1 and then by Column 2 (array formula)

Array formula in E2:

copied down as far as needed.

Array formula in F2:

copied down as far as needed.

**Named ranges**

Descr (B2:B15)

Qty (B2:B15)

What is named ranges?

### Download excel sample file for this tutorial

Array formula sorts values in parallel.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

### 2. How to sort a table by Column 1 and then by Column 2 (Right-click / Sort /Custom sort...)

- Select range (B2:B15)
- Right click and select sort
- Click Custom sort..
- Sort by "Description" and values from A to Z
- Then by "Qty" and values from smallest to largest
- Click ok.

**External resources:**

### 16 Responses to “Sort values in parallel (array formula)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

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

Download 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

Download excel *.xlsx file

Sort-from-smallest-to-largest.xlsx