Putting subtotals in $ descending order
I have exported a report from a Donor based program to Excel v 2003. The
columns are donor name, date of donation and dollar amount of donation. Some donors have more than one donation. All donors have subtotals for their donations, the subtotal is below the detail of donation amounts. How can I put this report in order of donation subtotals, highest to lowest. This is an example of how an entry might look Column a Column b Column c row 5 John Smith 4/29/05 $5,000 row 6 John Smith 6/20/06 $10,000 row 7 2 $15,000 row 9 Jane Doe 4/04/07 50.00 row 10 Jane Doe 4/25/07 1,000.00 row 11 Jane Doe 6/30/07 20,000.00 row 12 3 21,050.00 row 14 Bill Brown 6/20/06 10.00 row 15 1 10.00 In putting the subtotal amounts in descending order, it will need to keep the detail with it. Thank you. |
Putting subtotals in $ descending order
Here's one way, based on your sample data...
In cell D5, enter this formula: =IF(A5=A4,D4,IF(OR(ISNUMBER(B5),LEN(B5)=0),D4,D4+1 )) In cell E5, enter this formula: =IF(ISNUMBER(B5),C5,"") In cell F5, enter this formula: =SUMIF($D$5:$D$16,D5,$E$5:$E$16) (replace every 16 in this formula with the number of the row after your final subtotal) Select D5:F5 and copy down through the row after your final subtotal (16 in your example). Select all the data + the extra row (A5:F16 in your example). From the Data menu, select Sort. Sort by column F in descending order, then by column D in ascending order. Hope this helps, Hutch "Ms. Domzalski" wrote: I have exported a report from a Donor based program to Excel v 2003. The columns are donor name, date of donation and dollar amount of donation. Some donors have more than one donation. All donors have subtotals for their donations, the subtotal is below the detail of donation amounts. How can I put this report in order of donation subtotals, highest to lowest. This is an example of how an entry might look Column a Column b Column c row 5 John Smith 4/29/05 $5,000 row 6 John Smith 6/20/06 $10,000 row 7 2 $15,000 row 9 Jane Doe 4/04/07 50.00 row 10 Jane Doe 4/25/07 1,000.00 row 11 Jane Doe 6/30/07 20,000.00 row 12 3 21,050.00 row 14 Bill Brown 6/20/06 10.00 row 15 1 10.00 In putting the subtotal amounts in descending order, it will need to keep the detail with it. Thank you. |
All times are GMT +1. The time now is 04:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com