Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim
 
Posts: n/a
Default keeping rows intact during sort

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
David McRitchie
 
Posts: n/a
Default keeping rows intact during sort

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

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim" wrote in message ...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default keeping rows intact during sort

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(A 1)),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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jim" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort according to the colored rows swchee Excel Discussion (Misc queries) 2 August 18th 05 04:07 AM
How do I group rows prior to a data sort so they stay together? Linda Mills Excel Discussion (Misc queries) 4 August 17th 05 08:28 PM
I want to sort selected cells in Excel, not entire rows. Aeryn635 Excel Discussion (Misc queries) 1 June 1st 05 07:58 PM
Keeping duplicate rows Daniell Excel Worksheet Functions 2 April 18th 05 06:56 AM
Keeping Row 1 at the top during a Data Sort... bro. Billy Excel Worksheet Functions 2 December 13th 04 07:43 AM


All times are GMT +1. The time now is 04:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"