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 blog 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
- Sort text cells alphabetically from two columns using excel array formula
- Sort a range by occurence using array formula in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
February 1st, 2010 at 5:45 am
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
February 2nd, 2010 at 9:34 pm
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.
September 26th, 2010 at 8:30 pm
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
?
September 26th, 2010 at 9:37 pm
Ralee,
read this post: Sort values in parallel in excel, part 2
April 22nd, 2011 at 7:10 pm
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?
April 23rd, 2011 at 7:26 am
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}
December 30th, 2011 at 10:56 pm
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
January 6th, 2012 at 2:15 pm
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))
January 16th, 2012 at 10:08 am
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.
January 17th, 2012 at 4:05 pm
and how can i Sort values Parallel with 4 (four) column or more.
January 20th, 2012 at 2:00 pm
Sergio,
Download example file:
Sergio.xls