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
Sort two columns using an array formula.xlsx
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
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 […]
Excel categories
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.
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 number
Formula
=SMALL($A$2:$A$11, ROW(A1))
Sort from smallest to largest text length
Array 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 formula
1. 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