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.
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
- 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
- Convert the cells in this new column from formulae to values, so the values are not changed during the sort
- Sort the data on this new column (descending), with Name as the second sort key.
In case the above is not enough info:
- Create a 4th column: let's call it
- Give the first three rows in Ordering the following formulae:
=C2+2 (i.e. 54 in your example)
=C2+1 (i.e. 53)
=C2 (i.e. 52)
Select those 3 cells, and Fill Down to the bottom of your data, so for example, the next 3 rows would contain:
=C5+2 (i.e. 62 in your example)
=C5+1 (i.e. 61)
=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
- Copy the Ordering column to the clipboard
- Paste the Ordering column as Values, i.e. to replace the above formulae by the values that they calculated
- Now do your Custom sort
- On Ordering, Descending
- 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)