Array Sorting Functions

FastExcel SpeedTools has 6 dynamic sorting functions that can be used to return sorted arrays.

VSORTC                    - Sort of a vertical range/array, not case sensitive, using Excel’s sorting rules

VSORTC.INDEX          - Index sort of a vertical range/array, not case sensitive, using Excel’s sorting rules

Case.VSORTC          - Sort of a vertical range/array, case sensitive, lower-case before upper-case, using Excel’s sorting rules

Case.VSORTC.INDEX – Index sort of a vertical range/array, case sensitive, lower-case before upper-case, using Excel’s sorting rules

VSORTB                    - Fast sort of a vertical range, case sensitive, upper-case before lower-case, ignores collating rules for accented characters

VSORTB.INDEX          - Fast Index sort of a vertical range, case sensitive, upper-case before lower-case,  ignores collating rules for accented characters

The sorting functions use a stable sort method which preserves the original order of equivalent items.

Up to 15 columns in the input data can be sorted using a mixture of ascending and descending sort sequence.

Collating Sequences

A collating sequence defines the order in which characters and values are sorted. Collating sequences tend to vary by country, and sometimes a given country will have more than one collating sequence available.
There are 2 different types of text collating sequences available in the FastExcel SpeedTools sorting functions.

The locale-dependent collating sequence uses the user-specified National Language locale sequence. This handles national characters and character combinations based on the conventions established for each locale. This is the method used by Excel SORT and all the LOOKUP functions.
The VSORTC, VSORTC.INDEX, Case.VSORTC, Case.VSORTC.INDEX functions all use the locale-dependent collating sequence, and follow Excel’s sorting rules for hyphens and apostrophes.

The collating sequence used by VSORTB and VSORTB.INDEX is faster for sorting text than the locale-dependent sequence but may not give results compatible with Excel’s SORT and LOOKUP functions using the sorted option. VSORTB and VSORTB.INDEX ignore Excel’s sorting rules for hyphens and apostrophes.

Excel’s collating sequence by data type is:
Numbers < Textual Numbers < Text < Logical < Error Values < Empty cells (always last)

Empty cells are returned as zero by the SpeedTools sorting functions.

Sort and Index Sort

A conventional sort returns the input data in sorted order.
An Index Sort returns the position or index in the input data of the nth item in the sort order.

Sort( B, C, A, D) gives A,B,C,D

Index Sort (B, C, A, D) gives 3,1,2,4.
First A is position 3, second B is position 1, third C is position 2, fourth D is position 4.

Case-Sensitive Sort

The locale-dependent SORTs have both case-sensitive and case-insensitive versions. Binary Sorts are always case-sensitive:

VSORTC is not case-sensitive so the relative positions of the equivalent upper and lower case letters is preserved from the original sequence.

Case.VSORTC is case-sensitive and sorts lower-case before upper-case in the same way as Excel.

VSORTB is also case-sensitive but sorts upper-case before lower-case (this sequence is dependent on the code-page and character set being used).

Copyright © 2024 Decision Models Ltd