Results 1 to 5 of 5
  1. #1
    risman
    Guest

    Creating new file from subtotalled entries

    I have a large (~22,000 records) Excel file, of which the attached file is a small sample, that contains data on amounts paid for services provided by a group of Rendering Providers, but the revenue accrues to a smaller group of Billing Providers. Several Rendering Providers may work for a single Billing Provider. I want to be able to subtotal the amounts paid to all Rendering Providers who work for a Billing Provider and then create a new file that contains just the Billing Provider numbers with the subtotals of all revenue generated by the Rendering Providers who work for them. Is there a way to do this?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating new file from subtotalled entries

    I would use a pivot table on the data. This is under Data / PivotTable Report and I have reattached your workbook with a simple example. You stated you wanted this in a seperate workbook - select the pivot table and copy, then paste special values in your new workbook.

    Brooke
    Attached Files Attached Files

  3. #3
    risman
    Guest

    Re: Creating new file from subtotalled entries

    That looks like it does the trick, but I'm not familiar with pivot tables and can't figure out exactly what you did to create it. Could you walk me through the steps? Many thanks.
    Bob

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating new file from subtotalled entries

    Bob,

    Open the file with your data in and select a single cell within that data.

    On the standard menubar, select "Data" and then select "PivotTable Report...."

    You should now have a wizard in front of you.

    Step 1 - check the top option - "Microsoft Excel List or Database" and click next.

    Step 2 - this should select the range of data to be analysed automatically - it does in the example you posted if you start with one of the cells within the list selected, but if it doesn't click on the refedit button at the right of the box with the range in, manually select the range of data you require and reclick on the refedit button. Click Next

    Step 3 - Drag the "billing provider" button into the area marked "Row" and drag the "amount paid" button into the area marked "data". At this point the description should change to "sum of amount paid". If however it changes to something like "count of amount paid", double-click on the button where it is and select the correct function you require - in this case sum - from the list and click ok. Click on next.

    Step 4 - check the top option - "new worksheet" and click on finish.

    That should get you a pivot table exactly like the one in the copy of your workbook that I posted as that is all I did. Hopefully you're now an expert in pivottables!

    Brooke

  5. #5
    risman
    Guest

    Re: Creating new file from subtotalled entries

    Thanks very much. It seems there is a limit on the number of records that a pivot table can handle. I had about 23,000 records, but found that if I split it in two I could do pivot tables on each half and then combine them.

Posting Permissions

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