I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Sorting

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: [If you look for a simple VBA solution, go here, please.]

1. Unsorted input data in sheet 'Unsorted':

080223_PB_01_Unsorted

2. Sorted output data in sheet ‘Sorted':

080223_PB_01_Sorted

If you are interested in downloading a 20 KB Excel 2010 © sample file which contains the functions shown here, go to my Download page, please.

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

080223_PB_01_Sorted_Formulas

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:

080510_Unsorted_Complex

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

080510_Sorted_Complex

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

080510_Sorted_Formulas_Complex
Sulprobil   Get it done   Contact   Disclaimer   Download