Sorting by Total (97 SR1)

Topic

New Reply

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.

Reply | Quote

Replies

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 … ?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

On the attached I don’t get that error; note that the area selected has to include the headers and the grand total. If you have a solution that is satisfactory, go with it, otherwise you can look at this attachment (the highlighted area is sorted by name instead of subtotal amount) and see if the sort blows up on you.

Hope this has been fun for you.

Reply | Quote

Thanks John. I tried your method and it worked as well. I guess I didn’t have the grand total as part of the highlighted area. Thanks again for all the help.

Reply | Quote

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

Reply | Quote