Sort a group of names based on the group total

Hi

I've searched a bit but didn't turn anything that helps in my case, so a hand is once needed. I have the attached worksheet and I want to sort the data on the "Data" tab by the total of each group, so that I have a listing similar to that of "Data (2)".

Help will be immensely appreciated.

Thanks

Hi

I've taken the liberty of slightly changing your data. See the Data (3) tab on the attached. Using this layout allows you to use the Pivot Table functionality which simplifies things immeasurably.

Note I've removed the subtotals and added an extra helper columns which sums the values for each client name. We can then use this helper column in the pivot table - see column G. You'll probably want to hide this since it's just used by the pivot table to arrange the totals in descending order.

The pivot table could be be in a pivot table sheet of its own if you prefer. Just right click anywhere in the PT, choose PT wizard and select New WorkSheet.

HTH

Thank you,

I knew this was around the Pivot Table's alley, but, as lots of people, this a weakness to me. Would you mind to walk me through the steps u took in generating the pivot table from your modified data table. I know what to do up to point when you need to start dropping fields into the pivot table (where to do it and whether it is a row or column, etc.

Thanks

Hi,

1. Select the data in A2:E25 and from the menu pick Data Pivot Table, then Next, Next, select a location in the existing worksheet and Finish

2. Right click in the table, select Pivot Table Wizard and layout

3. Drag the sorted total field, then the Group Name field, then the Client Name field, then the Account No field into the row fields section, and drag the Amount filed into the Data items area.

4. For those fields where you don't want a subtotal, right click the field name, pick field settings and tick the None for subtotals and OK.

5. Finally right click the sorted total field, click field settings then Advanced and in the Auto sort options pick the Descending option and OK

Regards