Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rank Subtotaled amounts (2000)

    I have a list of data - payee and amount are column headers. I have subtotaled by payee. Now, w/o cutting and pasting subtotaled amounts into a separate worksheet, I'd like to rank by subtotaled amounts. Does anyone know of a simple way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rank Subtotaled amounts (2000)

    Do you want to calculate ranks, or do you want to sort the data by subtotal? If the latter, look at the thread starting at <post#=115793>post 115793</post#>; essentially, you can either hide the detail records and sort the subtotals, or use a pivot table; you will find examples of both approaches attached to replies in that thread.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank Subtotaled amounts (2000)

    My goal was to sort by subtotals. I had way too many payees to hide the data. I will try looking at the post you mentioned.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rank Subtotaled amounts (2000)

    You don't need to hide the detail data manually! Use the outline buttons that appear automatically when you create subtotals, click the button numbered 2. Then sort the way you want, and click the button numbered 3 to display all records again.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rank Subtotaled amounts (2000)

    GREAT. WHAT A HELP!!!!!!! Hey, you piqued my interest in pivot tables. They seem very powerful. In my table, I have the following columns currently:
    Payee, Charge Date, Charge Description and Amount. How do I know which should be a row label and which should be a column label. I tried several orientations but got message unable to display data (too much).

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rank Subtotaled amounts (2000)

    A column field will display each value of the field in a separate column. Since an Excel worksheet has "only" 256 columns, you should not use a field with a large number of distinct values as a column field. A worksheet has 65,536 rows, so you have much more room for row fields. You can use multiple row fields (or column fields), by the way, and you don't need to use ALL fields in a pivot table. Just experiment, you can always change the layout of a pivot table after you have created it, by dragging the gray field "buttons" around.

Posting Permissions

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