keeping rows intact during sort

[Jim]

I have information for a name that uses three rows. (See below) An average
is created from the values from cells in those three rows. I want to sort by
those averages, but keep the three rows relevant to the name intact. Can
this be done? If so, how?
EX:
1st person 5 4 5 14 12.00
3 3 4 10
3 4 5 12
2nd person 4 4 4 12 11.33
4 5 5 14
4 2 2 8
3rd person 4 5 4 13 14.00
5 5 5 15
4 5 5 14

The averages are the far right number. When sorting, I would like the
highest average listed first and all the information listed in the two rows
under the row containing the average stays with it. What I end up with is

3rd person 4 5 4 13 14.00
1st person 5 4 5 14 12.00
2nd person 4 4 4 12 11.33
3 3 4 10
3 4 5 12
4 5 5 14
Etc.

Thanks,
Jim

 

[David McRitchie]

Hi Jim,
See Fill in the Empty Cells
http://www.mvps.org/dmcritchie/excel/fillempt.htm

--

 

[Max]

One play which could auto-extract the desired results ..

Sample construct available at:
http://www.savefile.com/files/1370196
AutoSortDescending_w_GroupedRowsIntact

In Sheet1
---------
Data as posted assumed in A1:F9

Use 3 adjacent empty cols
Put in G1: =IF(ISNUMBER(F1),"x","")
Put in H1: =COUNTIF($G$1:G1,"x")
Put in I1: =SUMIF(H:H,H1,F:F)-ROW()/10^10
Select G1:I1, copy down to I9

In Sheet2
---------
Put in say, A1:

=IF(OR(ISERROR(LARGE(Sheet1!$I:$I,ROW(A1))),
INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$I:$I,ROW(A1)),Sheet1!$I:$I,0))=0),
"",INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$I:$I,ROW(A1)),Sheet1!$I:$I,0)))

Copy A1 across to F1, fill down to F9
(Format as desired, eg: col F as Number, 2 dp)

Sheet2 will return the desired results, i.e. a descending sort of the lines
by the averages in col F in Sheet1, with all grouped lines intact