Advanced sorting in excel

Question

I have a data in excel in the format:

Description      Name            Percent
Always             A               52
Sometimes          A               23
Usually            A               25      
Always             B               60
Sometimes          B               30
Usually            B               15 
Always             C               75
Sometimes          C               11
Usually            C               14

I want to sort this data:

For each name the sequence of description has to be same (eg: always followed by sometimes followed by usually) but for three names A, B and C, I want to sort the always percent from smallest to largest. Eg: I want the above example to look like this after sorting:

Description      Name            Percent
Always             C               75
Sometimes          C               11
Usually            C               14      
Always             B               60
Sometimes          B               30
Usually            B               15 
Always             A               52
Sometimes          A               23
Usually            A               25

The always percent of name C was highest and always percent of name A was lowest. I hope I was able to explain it. I would really appreciate your help regarding the same.

Answer 1

You can do a three level sort to solve this

1. By Name {z to a}
2. By Description (click on Custom List, then enter in Always, Sometimes, Usually)
3. then by Percent (smallest to largest)

Pls see screenshot (from xl2010 below)

Answer 2

I can't see a one-step approach, but try the following... (It's actually not as complicated as it looks: it's just hard to explain both succinctly and clearly!)

It is based on the assumption that the rows in the initial data are always in the desired relative order, i.e.

1. Always
2. Sometimes
3. Usually

If this is true (rather than a coincidence in your example data), then you can create a 4th column, for the purposes of sorting, that generates numbers that can be sorted in your desired order.

Summary of approach

1. Create a new column, containing data derived from your Percent values, that will retain the desired order of each set of 3 rows during sorting
2. Convert the cells in this new column from formulae to values, so the values are not changed during the sort
3. Sort the data on this new column (descending), with Name as the second sort key.

Detailed steps

In case the above is not enough info:

1. Create a 4th column: let's call it Ordering
   • D1: Ordering
2. Give the first three rows in Ordering the following formulae:
   • D2: =C2+2 (i.e. 54 in your example)
   • D3: =C2+1 (i.e. 53)
   • D4: =C2 (i.e. 52)

3. Select those 3 cells, and Fill Down to the bottom of your data, so for example, the next 3 rows would contain:

   • D5: =C5+2 (i.e. 62 in your example)
   • D6: =C5+1 (i.e. 61)
   • D7: =C5 (i.e. 60)

   Note 1: I'm really impressed/amazed that Excel's "Fill Down" mechanism populates the formulae as you need it here, at least on Excel 2010 on Windows. Do check the resultant values yourself, to ensure the Mac one behaves the same.

   Note 2: Don't be temped to do the Sort at this point, as the relative references get broken during the sort, and you end with an #REF! in D2

4. Copy the Ordering column to the clipboard
5. Paste the Ordering column as Values, i.e. to replace the above formulae by the values that they calculated
6. Now do your Custom sort
   1. On Ordering, Descending
   2. On Name, Ascending

With your original data, this gives the desired result.

The Sort on Name is for the case where 2 sets of 3 rows have the same Percent value. Otherwise the results for the two different Names could get mixed up (if Excel's sort doesn't retain the initial relative order)

Answer 3

select the collumns and in the context menu find the sorting menu and use custom sorting

if you want them grouped create another column with the always percent of the group (through data insertion or formula) and primary sort on that