Excel 2021: SORT array [UPDATED]

Question

📌 Note: This article was originally created by Lz., a valued member on Answers Support Community. It provided meaningful insights and proved helpful to many. We're recreating it here on their behalf to preserve its value and ensure continued access for others.

Intro.

A friend of mine asked if it would be possible to dynamically SORT an array (or range) > 1 row/column. As often, later came the question from her boss: if I needed to modify the formula to SORT numbers, and only the UNIQUE ones and... how would this be done? - A totally different story & challenge

2 scenarios are covered:

1. SORT an array/range with an option to change the orientation (to columns) of the result

Do the same thing with Numbers only, UNIQUE numbers + an extra option to pad undesirable cells in the result

Updated workbook supporting this article is available here (inc. the formulas embedded as plain Text files)

Both involve approaches covered in:

TOCOL and TOROW with Excel 2021

WRAPCOLS and WRAPROWS with Excel 2021

Notes

• Updated 2024-10-21: Reduced the calculations; Added optional parameters/arguments validation - Given the low level of feedback/Mark as helpful received on a number of previous articles, explanations are reduced to the minimum

SORT Behavior

(FALSE BLANKs in arrayOne & arrayTwo contain formulas returning "")

1 - SORT array

The LET formula has parameters:

array (required): A refence to an Array or Range

sort_order (optional): 1 = Ascending; -1 = Descending. Default is 1

wrap_cols (optional): TRUE (or 1); FALSE (or 0). If TRUE, result is presented in Columns. Default is FALSE

Note: If any argument is invalid a #VALUE! error will result

1.1 - SORT array - Examples

2 - SORT array Numbers

The LET formula has parameters:

array (required): A refence to an Array or Range

sort_order (optional): 1 = Ascending; -1 = Descending. Default is 1

unique (optional): TRUE (or 1); FALSE (or 0). If TRUE, unique/distinct numbers will result. Default is FALSE

wrap_cols (optional): TRUE (or 1); FALSE (or 0). If TRUE, result is presented in Columns. Default is FALSE

pad_with (optional): Any value (inc. error value). Default is #N/A

Notes:

If any argument is invalid a #VALUE! error will result

If array has no numbers a #CALC! error will result

2.1 - SORT array Numbers - Examples