PrecisionCalc
xl
Precision

xlpSORT

Sorts a range of numbers, with up to 32,767 significant digits of precision.

• Can sort in ascending or descending order.
• Can sort top to bottom or left to right.
• Can sort down multiple rows or across multiple columns.
• Can retain the number of rows and columns of the source range, or can sort into a different selected number of rows and columns.

xlpSORT must be array-entered. To array-enter an xlpSORT formula, follow these steps:

1. Select the cells in which you want the sorted values. The selection can include as many or as few rows and columns as desired.
2. With the cells selected, type the formula in the first (top or left) of the selected cells.
3. With the cells still selected, press and hold down the CTRL and SHIFT keys.
4. With the cells still selected, and with the CTRL and SHIFT keys still held down, press Enter.
5. You should see the sorted results in the selected cells.

Requires the 5,000 SD edition of xlPrecision or higher, or the Free Edition.

Syntax

xlpSORT(numlist,descending,left_to_right,fit_to_array,blank_text)

 numlist Required. The range of cells with the numbers to sort. descending Optional. If True, sorts from higher numbers to lower numbers. If False, sorts from lower numbers to higher numbers. False by default. left_to_right Optional. Determines direction of sorting if the xlpSORT array formula is on multiple rows and columns. If True, sorts the numbers left to right. If False, numbers are sorted top to bottom. False by default. fit_to_array Optional. Determines the dimensions of the sorted array of numbers if the xlpSORT array formula does not have the same dimensions as numlist. If True, sorts into the xlpSORT array formula's dimensions. If false, numlist's dimensions are retained. False by default. blank_text Optional. If the xlpSORT array formula contains more cells than the number of cells in numlist, this text is used in the extra cells. Blank by default.

Remarks

• Text in numlist cells that xlPrecision can't resolve to a number, and blank cells, are sorted to the bottom. They are not sorted among themselves, but may be randomly reordered among themselves by the sorting procedure.

• Numbers that are exactly equal but formatted differently (for example, one has thousands separators while the other does not) are not sorted among themselves, but may be randomly reordered among themselves by the sorting procedure.

• numlist can accept cells with both numbers and text.

• numlist can accept cells with text formatted with the local currency symbol and thousands separators, and negatives can be formatted with either a leading hyphen or parentheses.

Examples

Formula Description Result
=xlpSORT(A1:C5)
(array-entered in A10:C14)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in ascending order, from top to bottom, and retaining the dimensions of numlist.

 A B C 10 10 70 120 11 20 80 140 12 30 90 150 13 40 100 ABC 14 60 110
=xlpSORT(A1:C5,TRUE)
(array-entered in A10:C14)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in descending order, from top to bottom, and retaining the dimensions of numlist.

 A B C 10 150 90 30 11 140 80 20 12 120 70 10 13 110 60 ABC 14 100 40
=xlpSORT(A1:C5,,TRUE)
(array-entered in A10:C14)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in ascending order, from left to right, and retaining the dimensions of numlist.

 A B C 10 10 20 30 11 40 60 70 12 80 90 100 13 110 120 140 14 150 ABC
=xlpSORT(A1:C5,,,TRUE)
(array-entered in A10:B17)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in ascending order, from top to bottom, into the xlpSORT formula's dimensions.

 A B 10 10 100 11 20 110 12 30 120 13 40 140 14 60 150 15 70 ABC 16 80 17 90
=xlpSORT(A1:C5,TRUE,TRUE,TRUE,"---")
(array-entered in A10:D14)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in descending order, from left to right, into the xlpSORT formula's dimensions, and displaying "---" in the unused cells.

 A B C D 10 150 140 120 110 11 100 90 80 70 12 60 40 30 20 13 10 ABC --- 14 --- --- --- ---
=xlpSORT(A1:C5,,,,"---")
(array-entered in A10:D15)

 A B C 1 10 20 70 2 120 30 110 3 90 60 ABC 4 40 150 5 80 140 100
Sort the numbers in A1:C5 in ascending order, from top to bottom, retaining the dimensions of numlist, and displaying "---" in the unused cells.

 A B C D 10 10 70 120 --- 11 20 80 140 --- 12 30 90 150 --- 13 40 100 ABC --- 14 60 110 --- 15 --- --- --- ---
=xlpSORT(A1:A17)
(array-entered in A20:D36)

 A 1 100000000000000000000.1 2 1000000000000000000000.1 3 10000000000000000000000.1 4 100000000000000000000.01 5 1000000000000000000000.01 6 100000000000000000000.001 7 1000000000000000000000.001 8 100000000000000000000.0001 9 1000000000000000000000.0001 10 \$1,000 11 1,234,567,890,123,456,789.012345 12 \$1,234,567,890,123,456,789.012345 13 1234567890123456789.012345 14 \$100000000000000000000 15 \$1000 16 5 17 1.00000000000000000000000000001
Sort the numbers in A1:C5 in ascending order, from top to bottom, and retaining the dimensions of numlist.

 A 20 1.00000000000000000000000001 21 5 22 \$1,000 23 \$1000 24 1,234,567,890,123,456,789.012345 25 \$1,234,567,890,123,456,789.012345 26 1234567890123456789.012345 27 \$100000000000000000000 28 100000000000000000000.0001 29 100000000000000000000.001 30 100000000000000000000.01 31 100000000000000000000.1 32 1000000000000000000000.0001 33 1000000000000000000000.001 34 1000000000000000000000.01 35 1000000000000000000000.1 36 10000000000000000000000.1