Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting by Total (97 SR1)

    I have a spreadsheet that looks like this:

    1999______Smith, John____125
    2000______Smith, John____200
    2001______Smith, John____320
    _____________Total______545
    1999______Jones, Jane____100
    2000______Jones, Jane____52
    Total______152

    So basically, not all people have 3 years and then a total, some have two and seom have one. How would I sort by total when it looks like this? Is there a macro that can look for the total field, sort by it, yet keep together all the fields above it? Thanks for the help.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting by Total (97 SR1)

    If you use Data, Subtotals, to subtotal each name, then Group the data at the subtotal Outline level, you can sort on subtotals without a macro. See attached, where originally before the sort Jane was the second name in the list. If that will work for you ... ?
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting by Total (97 SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> kanders1

    OK to sort a body of data it is best to have all the rows with a specific value to sort by. So what I would suggest is to make a column call it total and for all the rows that are above a total have the same value. Then sort by that value.

    So if you would look at the workbook I attached, you will see a neat way of how to do this.

    HTH

    Wassim
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    I didn't create this spreadsheet. It was just given to me and they definitely want to see each year and the totals, then sort by the totals. Just like my first post. Any ideas?

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting by Total (97 SR1)

    kanders1

    Did you take a look at my attached workbook? In column B, sorry I forgot to name it Total, you have a formula that will place the Total number all the way per group of totals. hen you collapse the details and sort by that column and it come up as you would expect. The lower total first and then the next higher...

    If that is not what you want to do, please explain again.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting by Total (97 SR1)

    Wassim and I have demonstrated how to in the attachments (are you OK with saving attachments?), except I omitted a final step. After Grouping at the Name Subtotal level, display all the data by:

    1. showing the highest level of detail; in the WB's we posted, click on the '3' on the outline header on the left side,
    OR ...

    2. select the entire area subtotaled/totalled and select Data, Group & Outline, Show Detail (if necessary repeat until all data shows),
    OR ...

    3. Data, Group & Outline, Clear Outline if you are done with sorting for the WB; the data will then show as a standard unGrouped sheet but the sort is retained.

    HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    I don't understand the formula in column B. I'm not too knowledgable on this stuff. Column E is my column with the totals for each year and then the grand total. I'd like to put the formula in column F. How would this work? Thanks.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting by Total (97 SR1)

    Wassim is using column B to get the totals from column L, so he can sort every row by the ranking in Column B. He could have skipped this step, clicked on Outline Group level 2 and and then sorted on column L (Item 10) Subtotals, then clicked on Outline Group 3 to redisplay the data.

    Since we don't seem to meeting your need, perhaps you could post an example of your layout?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    Investigate the power of Pivot Tables as they are the perfect tool for what you want.

    Attached is your data with a pivot table. To illustrate the idea, amend the value of Jane Jones amount so that her tota is greater than Smiths, right click on the Pivot Table and select Refresh Data. To use this method you do not have totals in the source data and it can be totally unsorted.

    If you right click on the Name Fiel header and click the advanced tab, you set the sort criteria you want.

    Andrew C
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    Kanders, maybe this can help you:

    With this example I will try to descript the solve:
    __A; _____B; _____C
    Year___name___value
    1999___John___235
    2000___John___333
    2001___John___344
    2000___peter___34
    1999___Robert___5454
    2000___Robert___343
    1999___Josue___235
    2000___Josue___333
    2001___Josue___344

    Then add the column D with the label "Total" in D1 and thew next formula in D2
    =SUMA(SI($B$2:$B$10=B2,$C$2:$C$10,0))
    as array (Ctl+Shift+Enter).
    Then you can order by "Total" (Asc), "Name" (Asc)
    After this, you can eliminate the column "Total" and make the subtotal function

    Look at my attached workbook "Sort_Total".

    I hope this can help you.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    Sorry Kanders, the formula is
    =SUM(IF($B$2:$B$10=B2,$C$2:$C$10,0))

  12. #12
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    JohnBF,
    I was able to do what you had in your attachment except when I collapse and try to sort at the subtotal level, I get this message, "This removes the subtotals and sorts again. If you want to sort the subtotaled groups, choose the cancel button; then collapse the outline and try again." I did this and continue to get the same message. Haven't been able to get it to sort.
    Thanks.

  13. #13
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting by Total (97 SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> kanders1

    Did you get what you need from the replies of all the other nice people?

    If you still need help, drop me a note and I'll see what I can do.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  14. #14
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    Wassim,

    I tried to get your formula to work but I'm doing something wrong. Could you look at the attachment and let me know? Thanks.
    Attached Files Attached Files

  15. #15
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Total (97 SR1)

    Servando,
    I got your formula to work! Thank you all very much for your help. This has been a good learning experience.

Page 1 of 2 12 LastLast

Posting Permissions

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