Table of contents

1. How to sort a table by Column 1 and then by Column 2  (array formula)
2. 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:

=INDEX(Descr, MATCH(SMALL(COUNTIF(Descr, "<"&Descr), ROW(1:1)), COUNTIF(Descr, "<"&Descr), 0)) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in F2:

=INDEX(Qty, MATCH(SMALL(COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), ROW(1:1)), COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), 0)) + CTRL + SHIFT + ENTER 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...)

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

External resources:

Sorting arrays in parallel (VBA)

Related posts:

1. Filter unique rows and sort by date using array formula in excel
2. Sort text values by length using array formula in excel
3. Filter duplicate rows and sort by date using array formula in excel
4. Sort a range by occurence using array formula in excel
5. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
6. Sort dates within a date range using excel array formula
7. Sort a range from A to Z using array formula in excel
8. Sort text cells alphabetically from two columns using excel array formula
9. Extract largest values from two columns using array formula in excel
10. Filter text values existing in range 1 but not in range 2 using array formula in excel