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:

Select the cells in which you want the sorted values.
The selection can include as many or as few rows and columns as desired.

With the cells selected, type the formula in the
first (top or left) of the selected cells.

With the cells still selected, press and
hold down the CTRL and SHIFT keys.

With the cells still selected, and with
the CTRL and SHIFT keys still held down, press Enter.

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.