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:
Sorting arrays in parallel (VBA)
Related posts:
Sort values in parallel in excel, part 2
Sort numeric values by closest to farthest to a specific number in excel (array formula)
Sort text values by length using array formula in excel
Filter unique rows and sort by date using array formula in excel
Filter duplicate rows and sort by date using array formula in excel
Recommended categories
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 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
Download excel *.xlsx file
Sort-from-smallest-to-largest.xlsx