Using subtotals as single data entries

Sorry about the subject--I couldn't figure out how to describe it
simply.

I have a large file (16,000 records) of amounts billed by roughly
10,000 service providers. A number of these providers have multiple
office locations, so each record is unique to a specific office
location. In other words, a provider who billed from 3 different
office locations will have 3 entries. Each provider has a unique
provider ID number, which stays the same regardless of which office
location he is billing from.

I want to be able to subtotal the amount billed by each provider for
all their office locations combined, and then assign those subtotals
to each provider who has multiple offices, so that I will be able to
sort the providers by the total amount they billed, e.g., how many
unique providers billed a total of $1,000 or more, etc., regardless of
how many offices they had.  Is there a way to do this?

Thanks for any help.
0
risman (3)
1/12/2004 8:38:16 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
398 Views

Similar Articles

[PageSpeed] 28

Bob,
There are a couple options.

1) use the COUNTIF function to create a table  (probably not what you want)
2) use Data>Subtotal
3) use a Data>PivotTable Report

Play around with them and see which one is best for your needs.

Good Luck,
Mark Graesser
mark_graesser@yahoo.com
     
     ----- Bob wrote: -----
     
     Sorry about the subject--I couldn't figure out how to describe it
     simply.
     
     I have a large file (16,000 records) of amounts billed by roughly
     10,000 service providers. A number of these providers have multiple
     office locations, so each record is unique to a specific office
     location. In other words, a provider who billed from 3 different
     office locations will have 3 entries. Each provider has a unique
     provider ID number, which stays the same regardless of which office
     location he is billing from.
     
     I want to be able to subtotal the amount billed by each provider for
     all their office locations combined, and then assign those subtotals
     to each provider who has multiple offices, so that I will be able to
     sort the providers by the total amount they billed, e.g., how many
     unique providers billed a total of $1,000 or more, etc., regardless of
     how many offices they had.  Is there a way to do this?
     
     Thanks for any help.
     
0
anonymous (74722)
1/12/2004 9:06:11 PM
OK, I tried the Pivot Table route, but there are too many records
(>16,000) for it to work. I tried splitting the file in half and doing
pivot tables on each half, which works fine, but it may be missing
some values that appear in each half. Is there some way around this
problem, or of "adding" 2 pivot tables?

"Mark Graesser" <anonymous@discussions.microsoft.com> wrote in message news:<BD628331-B0B1-4464-AF63-C6ACECA7A793@microsoft.com>...
> Bob,
> There are a couple options.
> 
> 1) use the COUNTIF function to create a table  (probably not what you want)
> 2) use Data>Subtotal
> 3) use a Data>PivotTable Report
> 
> Play around with them and see which one is best for your needs.
> 
> Good Luck,
> Mark Graesser
> mark_graesser@yahoo.com
>      
>      ----- Bob wrote: -----
>      
>      Sorry about the subject--I couldn't figure out how to describe it
>      simply.
>      
>      I have a large file (16,000 records) of amounts billed by roughly
>      10,000 service providers. A number of these providers have multiple
>      office locations, so each record is unique to a specific office
>      location. In other words, a provider who billed from 3 different
>      office locations will have 3 entries. Each provider has a unique
>      provider ID number, which stays the same regardless of which office
>      location he is billing from.
>      
>      I want to be able to subtotal the amount billed by each provider for
>      all their office locations combined, and then assign those subtotals
>      to each provider who has multiple offices, so that I will be able to
>      sort the providers by the total amount they billed, e.g., how many
>      unique providers billed a total of $1,000 or more, etc., regardless of
>      how many offices they had.  Is there a way to do this?
>      
>      Thanks for any help.
0
risman (3)
1/13/2004 6:52:43 PM
Bob
I didn't realize there was a limit on the pivot table function.  Live and learn

I put together the following procedure which should do the trick.  Post back if you have any problems

1) Select ID colum

2) This will require that the first row of the data columns is a header

     Data>Filter>Advance
        Check “Copy to another location�
        List: Already filled i
        Criteria: Select header cell of ID colum
        Copy to: Select first cell of a blank colum
        Check “Unique records only�

3) Assuming ID in column A, bill amounts in B, and filtered data in D, enter this formula in E2

     =SUMPRODUCT(($A$2:$A$30000=D2)*($B$2:$B$30000)

This will check for each occurrence of the ID number in D2 in column A and sum up the corresponding values in column B.  The Sumproduct function cannot use column references (ex. A:A) so I just used more cells then you should need.  The ranges are absolute references so they won’t change when you copy the formula

4) Copy down to end of filtered list.  This can be done very quickly by selecting E2 and double clicking on the fill handle (black square in lower right corner of selection)

5) Go get a cup of coffee.  I tested this with 25000 records on 12000 ID numbers.  It took about 10 minutes.  You will see “CALCULATE” at the bottom of the Excel window for about 1 minute, and then Calculating ##% for the rest of the time.  Be patient.  If you do anything else with your computer while this is calculating it will take much longer

6) Edit>Copy column E and Edit>Paste_Special>Values on top of itself.  This will replace the formulas with the actual numbers.  Otherwise whenever you change a cell Excel will recalculate the entire sheet

Now you should have the table you need

Good Luck
Mark Graesse
mark_graesser@yahoo.co

    
     ----- Bob wrote: ----
    
     OK, I tried the Pivot Table route, but there are too many record
     (>16,000) for it to work. I tried splitting the file in half and doin
     pivot tables on each half, which works fine, but it may be missin
     some values that appear in each half. Is there some way around thi
     problem, or of "adding" 2 pivot tables
    
     "Mark Graesser" <anonymous@discussions.microsoft.com> wrote in message news:<BD628331-B0B1-4464-AF63-C6ACECA7A793@microsoft.com>..
     > Bob
     > There are a couple options
     >> 1) use the COUNTIF function to create a table  (probably not what you want
     > 2) use Data>Subtota
     > 3) use a Data>PivotTable Repor
     >> Play around with them and see which one is best for your needs
     >> Good Luck
     > Mark Graesse
     > mark_graesser@yahoo.co
     >>      ----- Bob wrote: ----
     >>      Sorry about the subject--I couldn't figure out how to describe i
     >      simply
     >>      I have a large file (16,000 records) of amounts billed by roughl
     >      10,000 service providers. A number of these providers have multipl
     >      office locations, so each record is unique to a specific offic
     >      location. In other words, a provider who billed from 3 differen
     >      office locations will have 3 entries. Each provider has a uniqu
     >      provider ID number, which stays the same regardless of which offic
     >      location he is billing from
     >>      I want to be able to subtotal the amount billed by each provider fo
     >      all their office locations combined, and then assign those subtotal
     >      to each provider who has multiple offices, so that I will be able t
     >      sort the providers by the total amount they billed, e.g., how man
     >      unique providers billed a total of $1,000 or more, etc., regardless o
     >      how many offices they had.  Is there a way to do this
     >>      Thanks for any help
     
0
anonymous (74722)
1/14/2004 3:16:17 PM
"Mark Graesser" <anonymous@discussions.microsoft.com> wrote in message news:<CB3041F7-0720-4E7D-826D-8947DF6B4A50@microsoft.com>...
> Bob,
> I didn't realize there was a limit on the pivot table function.  Live and learn.
> 
> I put together the following procedure which should do the trick.  Post back if you have any problems.

Excellent! That did the trick. Thanks very much!
0
risman (3)
1/21/2004 6:19:19 PM
Reply:

Similar Artilces:

Windowsupdate using Intranet?
Is there a way to setup a local server to use as the wundowsupdate site for a large # of pc's, but that has a small pipe to the internet? Try downloading the Software update services onto one server, use that the install onto PC's etc, using Group Policies cheers >-----Original Message----- >Is there a way to setup a local server to use as the >wundowsupdate site for a large # of pc's, but that has a >small pipe to the internet? >. > ...

Send data from one form to another
Hi All, I want to know if it,s possible to send data from 1 form to another totally unrelated form. The situation I have it one form is my MRP requirments. I want to open another form PURCHASE ORDER (PO). I was wanting to add a button to the MRP form that would send the data to the PO form. The MRP form may change to multiple items and then add these different items to the same PO. Does anyone have any suggestions on how I could do this or if it can even be done. -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.as...

Converting grid data to side-by-side lists
Hello everyone, this has been driving me crazy so want to see if any of you have suggestions: I have a grid in an Excel worksheet formatted like this: Company A Company B Company C Service 1 X X Service 2 X X Service 3 X X I need to convert this data to a list that maps the Services to the Companies like this: Services Companies Service 1 Company A Service 1 Company C Service 2 Company A Service 2 ...

Excel Data Querry using criteria from Cell
Hello, I have created a data query in Excel using DATA - IMPORT EXTERNAL DATA - NEW DATABASE QUERY and then selecting Access and browsing to an query within an Access database. This query returns all data into excel, but I would like to limit the criteria based on what a user has typed into a cell in the Excel workbook. I thought this could be accomplished by clicking DATA - IMPORT EXTERNAL DATA then PARAMETERS but the PARAMETERS option is grayed out. I can select EDIT CURRENT QUERY, but this just brings me into the Microsoft query, where I don't see any options to query based on...

using button to clear contents
I have finally found out how to add a button but now I am haveing a problem on having the button clear contents in certain cells does anyone know how to do this thanks for your help I do not know any codes for this I'll bet you could learn very quickly by using the macro recorder while you select some cells and edit>clear>contents. Here is a sample for a button from the Control Toolbox Private Sub CommandButton1_Click() ActiveSheet.Range("certain cells").ClearContents End Sub "certain cells" would be your range of cells. Gord Dibben MS Exc...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Using icon sets with relative references
I am using Excel 2007 in the Vista OS. I am trying to use conditional formatting with an icon set in a column but Excel will not allow relative references. Example: A1=10 min B1=20 max C1=30 oh D1=a formula { =IF(C1=0,B1,(B1-C1)) } The condtional formatting of D1 would be: if the value showing in D1 is >B1 the cell would show a red 'X', if the value showing in D1 is <A1 the cell would show a green 'check mark', and if the value showing in D1 is between the values in A1 and B1 it would show a yellow 'exclamation point'. I can get the D1 ...

how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

Reading ranges and copying data from Excel when it is not open
Is there a way in code to copy data out of an Excel file even if Excel is not up and running? If Excel were open, I'd copy, say, the first 30 rows and paste the info to PowerPoint. Then, since rows 1-5 are for column headings, I'd hide rows 6-30 and copy a new range which would look involve 30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a contiguous block. I am trying to do this in VBA without opening Excel and instatiating objects, etc. Is it possible? What VBA commands would I use? You would need to treat the excel file as a database and use ADO to g...

Chart, lines drop to zero when no data entered
Have several charts that get the data from a master data sheet which gets its data from several other sheets, thus a linked formula is involved. X axis is for Jan - Dec. I only thus far have data for Jan - May. The lines on the charts are fine for Jan - May, but since there is no data in June - Dec, the line drops to zero. It make the production chart look like the person suddenly stopped working. I realize I can erase the formulas but this is not acceptable in that several people, not excel proficient, will be updating their own charts and printing them. I want the line to sto...

HOW IS F3 USED WTH EXAMPLE
hi guys reply me soon 1) In A1 type a number such as 12% 2) With A1 still selected, in the Name box (box to left of Formula Bar) type the word taxrate and press Enter. You must press Enter for this to work! 3) Let's confirm that you have named the cell A1: click on cell D1, now click on cell A1. The name box should not display "taxrate". If not, repeat steps 2 4) In A2 type a number like 100 5) In B2 type =A2* and then tap the F3 key. A dialog box opens, select the entry "taxrate" and click the OK key. Another way is to double click the "taxrate" entry....

2 different types of data sets displayed on 1 graph
I have a district that I wish to show performance (sales)in the months from July to November and comapred this with 2004 and 2005. Now on top of this I want to display on the same graph a second variable say number of customers for each month and each year, in order to bring some greater relevance to the static bar graphs that I propose doing for the first part. So I have been assisted on this discussion group to have a bar graph that is set up with the months as rows and the 2004 and 2005 as columns for the sales data BUT how do I now display the second variable 'number of customer...

how do i stop outlook auto adding entries to contacts
My Outlook 2003 adds every address I send an email to to my contacts, which is really annoying and means that I now have thousands of unwanted contacts. As far as I am aware I do not deliberately have an 'Add-in' that is doing this, but don't know how to check for that either! Any ideas how to stop outlook doing this, as I can't find any help on the matter, or any obvious way to turn it off. The problem is duplicated as well, because if I email John Smith, for instance, it adds two contacts, one for Smith, John, and one just for Smith. Any help or advice gratefu...

Inventory adjustment creates two journal entries
I was tracking the results of an inventory adjustment transaction when I discovered the system broke the resulting entry to the G/L into two, separate journal entries. The adjustment transaction (# 0000000009826) contained 123 lines; two journal entries were output by GP. The first entry had the following reference information: Journal Entry #: 499514 Source Document: IVADJ Audit Trail Code: GLTRX00021619 Reference: Transaction Entry Orig Audit Trail Code: IVADJ00001482 The second journal entry had the following information: Journal Entry #: 499515 Source Document: GJ Audit Trail Code...

Subtotal Headers
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01CA24AC.8EB67EE0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit I have an Excel users who has lost the subtotal headers in Excel 2007, see attached where it shows the 123 columns. She does not have that, any ideas. ------=_NextPart_000_000C_01CA24AC.8EB67EE0 Content-Type: image/png; name="excel example.PNG" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="excel example.PNG" iVBORw0KGgoAA...

AP Aging report shows zero after Data migration
We have migrated the the payables data from GP ver 8.00 to GP 10.0. We are able to successfully carry out the migration. However, when the aging report is run, the vendor activity in the report all shows zero. Can anybody help identify the fissure? -- My future starts when I wake up every morning... Every day I find something creative to do with my life. ...

Excel cant SAVE added data?
This is a copy/paste with minor edits from post submitted yesterday ... Excel 2000 ... I have a single page spread sheet (my own) ... built in Excel 2000 (still Excel 2000) that was working fine. After expanding the spread sheet to handle more equipment I received error message stating: Excel can not SAVE all of the added data & formatting ... Here I had the option to select OK without fixing ... CANCEL ... or ... HELP. I selected HELP & ended up on a blank HELP Page ... So now I am turning to this board. Anybody know what gives? Could I be running into a formatting limit i...

Binary data and BinaryFormatter!
Hi all, I have a web app that exports a dataset to an XML file, the customer now wants this XML file to include media as well (Word, JPEG, bitmap and many other formats I might not expect). I've got the dataset exporting great, but I cannot figure out how to append multiple binary files in XML format. Using BinaryFormatter I have been able to simply append a chunk of Base64 encoded data to the end of the XML, but it needs to be delimited so that I can extract the separate media items on the other end. e.g. <NewDataSet> <References> <ReferenceID>19</ReferenceID...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

transfer contacts using email
Is it possible to transfer contacts from one machine to another using email? If, yes, what does the receiving person need to do? Many thanks, Frank FJB <FJB0623@aol.com> wrote: > Is it possible to transfer contacts from one machine to another using > email? If, yes, what does the receiving person need to do? Create a new PST (File>Net>Outlook Data File). Now, right-click Contacts and choose Copy. SPecify the new PST as the destination. When the copy completes, right-click the new PST and choose Close. Close Outlook. Rename the PST's file extension to something...

Functionality lost by using exchange
Our institution recently changed from provider based internet mail (pop3) to letting external mail arrive directly to our exchange 2003 server. The following problem shows up: in the old scenario, groups had 2 mailboxes: one for the group (G) and a private mailbox for the manager (M), each with their own e-mail address. I emulated this by creating a mbx-enabled user G, that is only used for the mailbox, not for login. I gave all group members including M access to that mailbox. But what happens? 1. If a group member sends a mail, the return address is the address of the sender; it should b...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

Simple date entry WAY off
Entering today's date as "013006" in a blank cell/new workbook, an changing formatting to ANY type, returns crazy date of "8/10/1935"? All PCs across our company appear to be doing this "all of sudden." Date/regional settings are all normal still. Only recent change was a update to ASAP Utilities, but I can't see how that would affect thing since it's just a plugin. Thoughts/suggestions? Thanks -- ulinetek ----------------------------------------------------------------------- ulineteks's Profile: http://www.excelforum.com/member.php?action=g...

Using external email address for AD user
We have certain users that we would like to use an external email address for and give access to resources in the domain. The only solution I have found so far is to create an account they will use for access and then to add a contact so we can send emails and add as a member of DL's. Ideally, I would like to do this without adding 2 separate objects in AD. Is that possible? Hi there, Are you aware that you can create a user and mail-enable them (not mailbox-enable), establishing an external address on the account. It removes the need for both a contact and a user accoun...