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:
- Unsorted input data in sheet ‘Unsorted’:
- Sorted output data in sheet ‘Sorted':
- 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”).
- Unsorted input data:
- Sorted output data: Please keep in mind that the rank column refers to the input data table!
- Formulas: Please copy down as far as necessary. Columns F:I can be array-entered.