Excel has no built-in way to do this. However, it can be done with a
formula to control the sort. Assume for example that the data table to
be sorted is in the range C5:F20. Assume further that the value upon
which the sort is based are in the first row of each block of 4 rows,
in column D.
Create a new column next to F, which will be a new G, and enter the
following formula in G5 and copy down to the last row of your data
table.
=OFFSET(D5,-MOD(ROW()-ROW($5:$5),4),0,1,1)
Change D5 to the column and row of the sort value in the first row of
the data table. Change the $5:$5 to the first row of the data table.
You need the $ characters in the $5:$5 address. In this formula,
change the reference to D to the column upon which the sort it to be
based and change the 5 to the first row number of the entire data
table.
This formula will create a series of values in column G, each block of
four rows having the same value. Now, select the range C5:G20, which
is your original data range plus the new column G. Open the Sort
dialog from the Data menu and specify column G as the sort key column.
Choose ascending or descending as desired. The sort operations will
sort all the rows in your data range according to the values in column
G. Since Excel's sort is order-preservative, each block of cells will
be sorted as a block of 4 rows and if there are duplicate keys, the
block order is preserved.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
