+ Reply to Thread
Results 1 to 3 of 3

keeping rows intact during sort

  1. #1
    Jim
    Guest

    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. #2
    David McRitchie
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    > 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. #3
    Max
    Guest

    Re: 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(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
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Jim" <[email protected]> wrote in message
    news:[email protected]...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1