Abstract

You want to sort data automatically? The sort key may be a number or a text? There might even be duplicates? And you are not allowed to use macros?

Here you go:

1. Unsorted input data in sheet ‘Unsorted’:

2. Sorted output data in sheet ‘Sorted':

3. Formulas for sorted output data in sheet ‘Sorted': Please copy down as far as necessary.

A more complex example would need us to use SUMPRODUCT: Think of a list which you want to sort a) by surname b) by first name and c) by birth date and where duplicate entries should appear in original order (“stable sort”).

1. Unsorted input data:

2. Sorted output data: Please keep in mind that the rank column refers to the input data table!

3. Formulas: Please copy down as far as necessary. Columns F:I can be array-entered.