I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

To sort subtotals for groups in Excel, you can follow these steps:

1. Select the data range that you want to sort.
2. Go to the "Data" tab in the ribbon and click on "Sort".
3. In the "Sort" dialog box, select the column that contains the subtotals you want to sort by in the "Sort by" dropdown list.
4. Choose "Values" in the "Sort On" dropdown list.
5. Select "Descending" in the "Order" dropdown list.
6. Click "Add Level" to add another sorting level if needed.
7. Select the column you want to sort by in the "Then by" dropdown list.
8. Choose "Values" in the "Sort On" dropdown list.
9. Select "Descending" in the "Order" dropdown list.
10. Click "OK" to apply the sorting.

This should sort your subtotals by descending order of cost. If you have used the subtotals feature to group your data, the subtotals will remain grouped together and sorted within their respective groups.

First, I'm using xl2003. It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

rtwoi wrote:

I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

--

Dave Peterson

Dave,

Thank you for your response. What you describe is how I expected it to
work. As I am using Excel 2002 without the latest patches however you get the
following message when you try to sort the subtotals.

"This removes the subtotals and sorts again. If you want to sort the
subtotalled groups, choose the Cancel button; then collapse the outline and
try again."

Trying again sends you round in a loop.

The Pivottable method does not seem to work at all in my version.

The solution is therefore to apply the latest Office updates to Office XP
(2002)

"Dave Peterson" wrote:

First, I'm using xl2003. It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

rtwoi wrote:

I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

--

Dave Peterson

I get the same message when I had all the details showing and attempted to sort
the range.

You sure you had the details hidden?



rtwoi wrote:

Dave,

Thank you for your response. What you describe is how I expected it to
work. As I am using Excel 2002 without the latest patches however you get the
following message when you try to sort the subtotals.

"This removes the subtotals and sorts again. If you want to sort the
subtotalled groups, choose the Cancel button; then collapse the outline and
try again."

Trying again sends you round in a loop.

The Pivottable method does not seem to work at all in my version.

The solution is therefore to apply the latest Office updates to Office XP
(2002)

"Dave Peterson" wrote:

First, I'm using xl2003. It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

rtwoi wrote:

I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

--

Dave Peterson


--

Dave Peterson

Dave,
Yes the details are hidden. It also seems to depend on the quantity of data
as a small spreadsheet works OK but one over a page in size has the problem
has the problem.

As I have solved my problem I am not going to do any more investigation.

Thanks for your help.

"Dave Peterson" wrote:

I get the same message when I had all the details showing and attempted to sort
the range.

You sure you had the details hidden?



rtwoi wrote:

Dave,

Thank you for your response. What you describe is how I expected it to
work. As I am using Excel 2002 without the latest patches however you get the
following message when you try to sort the subtotals.

"This removes the subtotals and sorts again. If you want to sort the
subtotalled groups, choose the Cancel button; then collapse the outline and
try again."

Trying again sends you round in a loop.

The Pivottable method does not seem to work at all in my version.

The solution is therefore to apply the latest Office updates to Office XP
(2002)

"Dave Peterson" wrote:

First, I'm using xl2003. It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

rtwoi wrote:

I have a spreadsheet with lists of telephone calls and their cost.
I want to group calls to the same number together and get the total cost of
these calls. I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

--

Dave Peterson


--

Dave Peterson

On a separate sheet you can extract a list of phone numbers dialled
and then using Advanced Filter you can reduce these to uniques so that
there is only one entry for each phone number. Then you can apply a
SUMIF formula to get the total cost for each number dialled, and then
just sort these in descending order. With a COUNTIF formula you can
obtain a count of the calls to each destination number, and then sort
these, so that you can obtain the Top-10 (or whatever) Most Frequently
called and the Top-10 Most Expensive destination.

Hope this helps.

Pete

On Jul 6, 11:13*pm, rtwoi wrote:
Dave,
Yes the details are hidden. It also seems to depend on the quantity of data
as a small spreadsheet works OK but one over a page in size has the problem
has the problem.

As I have solved my problem I am not going to do any more investigation.

Thanks for your help.



"Dave Peterson" wrote:
I get the same message when I had all the details showing and attempted to sort
the range.

You sure you had the details hidden?

rtwoi wrote:

Dave,

Thank you for your response. *What you describe is how I expected it to
work. As I am using Excel 2002 without the latest patches however you get the
following message when you try to sort the subtotals.

"This removes the subtotals and sorts again. If you want to sort the
subtotalled groups, choose the Cancel button; then collapse the outline and
try again."

Trying again sends you round in a loop.

The Pivottable method does not seem to work at all in my version.

The solution is therefore to apply the latest Office updates to Office XP
(2002)

"Dave Peterson" wrote:

First, I'm using xl2003. *It may be different for xl2007.

With a pivottable:
rightclick on the phone number header in the pivottable.
Select field settings
Click the advanced button
Choose the sort option you want (descending)
Using field:
Sum of Cost
(or whatever you called it)

========
With subtotals:
Apply the subtotals
use the outlining symbols to the left to hide the details
(Keep the field totals showing)
Select this range
Data|sort
sort by cost in descending order.

The groups will be sorted--but the details won't be. *If you want the details
sorted, then remove the subtotals, sort the way you want (phone number in
ascending, cost in descending), then reapply the data|subtotals.

Then hide the details and sort again.

rtwoi wrote:

I have a spreadsheet with lists of telephone calls and their cost..
I want to group calls to the same number together and get the total cost of
these calls. *I then want to sort the groups into descending order of cost.

Both the subtotals facility and the Pivot table facility allow me to group
and sum the cost of the calls but I cannot then find a way to sort the
results in descending order of total cost. *I am trying to find out what
called numbers have the highest total cost.

I am using Excel 2002.

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -