Error in Subtotal Wizard (Grouping and Outlining)

I'm having trouble with Excel 2003 and the Subtotals Wizard.  When I perform
a three-level grouping on the data listed below, it appears that some groups
are missing and outline levels 3, 4, and 5 are messed up.  Given the
following list of data:

      Region columnb Office Employee columne Issue
      NW  SEA 123  Salary
      NW  PDX 234  Benefits
      NW  PDX 244  Salary
      NW  PDX 212  Salary
      NW  BOI 232  Benefits
      SW  LAX 673  Salary
      SW  LAX 34  Benefits
      SW  LAX 654  Benefits
      SW  PHX 556  Salary
      SW  DEN 322  Benefits
      SW  DEN 65  Salary
      East  ATL 778  Benefits
      East  ATL 890  Salary
      East  ATL 768  Salary
      East  NYC 77  Benefits


The "Region" label value is in Cell A1.  The "columnb" and "columne" columns
have no detail values -- they act as placeholders for the subtotal labels of
the "Office" and "Issue" subtotals, respectively.

I sorted the list by Region (ascending), Office (ascending), then Issue
(ascending).  Then, I started the subtotaling routine as follows:

Select A1
Select Data | Subtotals...
At each change in: Region
Use Function: Count
Add subtotal to: Region
Replace current subtotals = true
Page break between groups = false
Summary below data = true
Click OK

Select B1 (because a new column has been inserted at A)
Select Data | Subtotals...
At each change in: Office
Use Function: Count
Add subtotal to: Office
Replace current subtotals = false
Page break between groups = false
Summary below data = true
Click OK

Select B1
Select Data | Subtotals...
At each change in: Issue
Use Function: Count
Add subtotal to: Issue
Replace current subtotals = false
Page break between groups = false
Summary below data = true
Click OK

What I expected was the data grouped and outlined as follows:
Outline Level 1 = Grand Totals
Outline Level 2 = Subtotals for the Regions (and Grand Totals)
Outline Level 3 = Subtotals for the Offices (and Subtotals for Regions and
Grand Totals)
Outline Level 4 = Subtotals for the Issues (and Subtotals for Regions and
Offices and Grand Totals) and no Detail rows
Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows

What I got was the data counts properly tallied, but the third and fourth
level outline (and associated groups) were all messed up:
Outline Level 1 = Grand Totals
Outline Level 2 = Subtotals for the Regions (and Grand Totals)
Outline Level 3 = Subtotals for some Offices, Subtotals for some Issues (and
Subtotals for Regions and Grand Totals)
Outline Level 3 = Subtotals for the Issues (and Subtotals for Regions and
Offices and Grand Totals) and some Detail rows
Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows

This problem did not seem to occur in Excel 97.

Is this a known bug in Excel 2003 or are my expectations unrealistic?
Please advise.

Thanks in advance,
Ed


0
10/18/2004 7:55:08 AM
excel 39879 articles. 2 followers. Follow

3 Replies
796 Views

Similar Articles

[PageSpeed] 53

Here is another listing of the data (in comma-separated value format):

Region,columnb,Office,Employee,columne,Issue
NW,,SEA,123,,Salary
NW,,PDX,234,,Benefits
NW,,PDX,244,,Salary
NW,,PDX,212,,Salary
NW,,BOI,232,,Benefits
SW,,LAX,673,,Salary
SW,,LAX,34,,Benefits
SW,,LAX,654,,Benefits
SW,,PHX,556,,Salary
SW,,DEN,322,,Benefits
SW,,DEN,65,,Salary
East,,ATL,778,,Benefits
East,,ATL,890,,Salary
East,,ATL,768,,Salary
East,,NYC,77,,Benefits

Now it is easier to see that the detail values under the "columnb" and
"columne" columns are null.

"Ed" <ed.svastits@cox.net> wrote in message
news:DFKcd.5585$EZ.2153@okepread07...
> I'm having trouble with Excel 2003 and the Subtotals Wizard.  When I
perform
> a three-level grouping on the data listed below, it appears that some
groups
> are missing and outline levels 3, 4, and 5 are messed up.  Given the
> following list of data:
>
>       Region columnb Office Employee columne Issue
>       NW  SEA 123  Salary
>       NW  PDX 234  Benefits
>       NW  PDX 244  Salary
>       NW  PDX 212  Salary
>       NW  BOI 232  Benefits
>       SW  LAX 673  Salary
>       SW  LAX 34  Benefits
>       SW  LAX 654  Benefits
>       SW  PHX 556  Salary
>       SW  DEN 322  Benefits
>       SW  DEN 65  Salary
>       East  ATL 778  Benefits
>       East  ATL 890  Salary
>       East  ATL 768  Salary
>       East  NYC 77  Benefits
>
>
> The "Region" label value is in Cell A1.  The "columnb" and "columne"
columns
> have no detail values -- they act as placeholders for the subtotal labels
of
> the "Office" and "Issue" subtotals, respectively.
>
> I sorted the list by Region (ascending), Office (ascending), then Issue
> (ascending).  Then, I started the subtotaling routine as follows:
>
> Select A1
> Select Data | Subtotals...
> At each change in: Region
> Use Function: Count
> Add subtotal to: Region
> Replace current subtotals = true
> Page break between groups = false
> Summary below data = true
> Click OK
>
> Select B1 (because a new column has been inserted at A)
> Select Data | Subtotals...
> At each change in: Office
> Use Function: Count
> Add subtotal to: Office
> Replace current subtotals = false
> Page break between groups = false
> Summary below data = true
> Click OK
>
> Select B1
> Select Data | Subtotals...
> At each change in: Issue
> Use Function: Count
> Add subtotal to: Issue
> Replace current subtotals = false
> Page break between groups = false
> Summary below data = true
> Click OK
>
> What I expected was the data grouped and outlined as follows:
> Outline Level 1 = Grand Totals
> Outline Level 2 = Subtotals for the Regions (and Grand Totals)
> Outline Level 3 = Subtotals for the Offices (and Subtotals for Regions and
> Grand Totals)
> Outline Level 4 = Subtotals for the Issues (and Subtotals for Regions and
> Offices and Grand Totals) and no Detail rows
> Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows
>
> What I got was the data counts properly tallied, but the third and fourth
> level outline (and associated groups) were all messed up:
> Outline Level 1 = Grand Totals
> Outline Level 2 = Subtotals for the Regions (and Grand Totals)
> Outline Level 3 = Subtotals for some Offices, Subtotals for some Issues
(and
> Subtotals for Regions and Grand Totals)
> Outline Level 3 = Subtotals for the Issues (and Subtotals for Regions and
> Offices and Grand Totals) and some Detail rows
> Outline Level 5 = All Detail rows and all Subtotal and Grand Total rows
>
> This problem did not seem to occur in Excel 97.
>
> Is this a known bug in Excel 2003 or are my expectations unrealistic?
> Please advise.
>
> Thanks in advance,
> Ed
>
>


0
10/18/2004 8:28:15 AM
It's a known problem that should be corrected by installing this hotfix:

       http://support.microsoft.com/default.aspx?id=833855

You can visit this page on the Microsoft site, to check what Office 
updates should be installed:

      http://office.microsoft.com/en-ca/officeupdate/default.aspx

Ed wrote:
> I'm having trouble with Excel 2003 and the Subtotals Wizard.  When I perform
> a three-level grouping on the data listed below, it appears that some groups
> are missing and outline levels 3, 4, and 5 are messed up.  


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/18/2004 7:17:03 PM
Debra:

Thanks for the information.  I'm running SP1 of Excel 2003 (published in
July 2004), which should have included the hotfix that you listed below
(833854, published in Feb 2004) but (upon reading the fine print) did not.
I'll contact Microsoft for the hotfix.

Ed

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:417416AF.4050402@contexturesXSPAM.com...
> It's a known problem that should be corrected by installing this hotfix:
>
>        http://support.microsoft.com/default.aspx?id=833855
>
> You can visit this page on the Microsoft site, to check what Office
> updates should be installed:
>
>       http://office.microsoft.com/en-ca/officeupdate/default.aspx
>
> Ed wrote:
> > I'm having trouble with Excel 2003 and the Subtotals Wizard.  When I
perform
> > a three-level grouping on the data listed below, it appears that some
groups
> > are missing and outline levels 3, 4, and 5 are messed up.
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
10/19/2004 10:18:05 AM
Reply:

Similar Artilces:

KBArticle.Create() error
Hello, I've been trying to create KB articles through the SDK, and am having troube submitting the content of the article. I quieried other articles to get the articlexml strucutre, but when I try to submit this, it comes back with an error that it has too many tags (I.E. doesn't know the sub-format), but then if I create it all as CDATA it still comes up Blank. Any Ideas ...

"there has been an error reading or writing the file"
I have been having numerous problems lately with my Mny delux 2005. 1. portfolio does not show correct "Market Value" of most securities most of the time, 2. Lines listing securities on portfolio manager are duplicated and change upon being floated over by the mouse curser and 3. the program blows out after displaying the error message, "there has been an error reading or writing the file" after folowing the intructions at: http://help.msn.com/(ZmlsdGVyPUNEX0RMWCZwcm9qZWN0PW1vbmV5MDUmbWFya2V0PWVuLVVTJmN1PSZ0bXQ9dG1wOUYudG1wIC0gTWljcm9zb2Z0IE1vbmV5JmtjPSZmb3JtYXQ9)/help...

How come code inside a false condition is causing an error
How come code inside a false condition is causing an error? I have the following code with a condition that is false (lets say 1=2.) How come I get an error that is related to the code inside if the condition is false? Msg 927, Level 14, State 2, Line 3 Database 'NMS' cannot be opened. It is in the middle of a restore. The code IF 1= 2 begin select * FROM NMS..MR_BackupHistory end The error in case it is executed when the db is being restored. Msg 927, Level 14, State 2, Line 3 Database 'NMS' cannot be opened. It is in the middle of a rest...

1018 error while taking bkp in exchange 5.5
We have transfered all the mails to local pst file. I don't have onlne backup to restore to eliminate 1018 error.Offline bakup what I have is one month old, will it resolve the problem if I restore that. I have only option to run eseutil /p (hard repair) on database. what will be the implication on site. Kidly suggest. >-----Original Message----- >http://support.microsoft.com/default.aspx?scid=kb;en- us;812531 >http://support.microsoft.com/default.aspx?scid=kb;en- us;314917 >http://support.microsoft.com/default.aspx?scid=kb;en- us;151789 >Basically you have to restor...

Grouping event handlers
I have a data entry form with numerous fields. Certain fields ar required and need a non-blank input. I am trying to create a clas module for a text box where non-blank inputs are not allowed. I have the following code: Public WithEvents Required As MSForms.TextBox Public notComplete As Integer Private Sub Required_Change() If (Required.Text <> "") Then notComplete = 0 Required.BackColor = &HFF& Required.ForeColor = &H8000000E Else notComplete = 1 Required.BackColor = &HFF& Required.ForeColor = &H8000000E End If MsgBox "In Event" End Sub ...

Error message in windows mail 12-21-09
I just set up my account and the existing messages imported fine, but when I try to send mail I get the following error: Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Subject 'new e-mail', Account: 'mail.td-wm.com', Server: 'mail.td-wm.com', Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 10053, Error Number: 0x800CCC0F Please help. Double check that all your account settings are correct. If they are, then e-mail scanning is a likely...

Sharing tasks, group task list
I have a team of account people who assign tasks to a production team. I want to create a shared task list that I (and others) can assign tasks to my team that also updates me when progress on the tasks has been made (ex. marked complete). I know I can do this from my personal task list, but is there a way to have a shared task list where items can be assigned and will also be updated through exchange? Public folders maybe...? Using Outlook 2003 on exchange server. Thanks! Chris Set up a shared Mailbox and make Tasks there, Assign them to team members. In that Tasks folder, show t...

CRM HTTP Error 401.1 IIS
Hei, I installed CRM on the Windows Server 2003 on three box for 100 users. All users registred in the CRM Server and working on the clients. A: 1 for Active Directory Domain controler (ADSRV) B: 1 for SQL 2000 + CRM Server ver 1.2 (CRMSRV) C: 1 for Exchange Server 2003 (EXSRV) i have one problem that when i type http://crmwebsite on the CRMSRV then i get "HTTP Error 401.1, Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS)". When i try it on the ADSRV then it works well. It is also working well on the client c...

Recovery storage group...with a 2000 database?
I have a user that has recently discovered that after a server outage last summer he is missing a great deal of Calendar items from prior to that date. The problem is that back then we were running Exchange 2000, and in December we migrated to 2003. Is it at all possible to use the recovery storage group and restore an Exchange 2000 database to it from one of our quarterly tapes? I'm trying to avoid building a sandbox with a clone of our old server and AD in it just to see if his Calendar contained items from back then (note that not all appointments are missing, but he typically ...

sp 6 Payroll tax update error
client attempted to install Rd 6 payroll tax update and code. The TX.cab file was successful but code update was successful on 2 databases then errored out on the third. She rec'd error: 'The stored procedure smGrantAccessOnAccountMSTR returned the following results: DBMS: 0 MicrosoftDyanmics GP' Has anyone experienced the same? Thanks, Debi And the error is? DBMS: 0, Microsoft Dynamics GP: ? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot....

E2003
Greetings, I'm in the middle of an Exchange 2003 upgrade (from 2000), and I'm running into some problems. One of these is with our out-going SMTP queues. I've never had a reason to stare at the queues for hours, so I'm not sure if this is normal behavior. We have 2 sites and 3 SMTP connectors. One site has 2 (going to its 2 Sendmail gateways) and the other has 1. The server at each site is listed as a local bridgehead for ONLY the same-site SMTP connector. Looking at the queue though, it uses all 3 connectors. For instance, the server on SiteX has 30,000 messages in it&#...

error numbers
Does anyone know if microsoft has a way to look up errors and their resolution by ERROR NUMBER? I have never found anything like that. Now they have something called "Events and Errors Message Center" but even that doesn't appear to let one look up errors by error number! Thanks Hi, Fredrated schrieb: > Does anyone know if microsoft has a way to look up errors and their > resolution by ERROR NUMBER? > > I have never found anything like that. Now they have something called > "Events and Errors Message Center" but even that doesn't appear to...

OWA HTTP Error
Hi all I have just installed a new Exchange 2000 server and have tried to set up OWA 2000 but keep getting a HTTP error 404 file not found. The history is that the exchange used to be on the DC and ISA box and OWA was set up and working before although there was no reference to this in ISA so Im not sure how it worked! Troubleshooting I have done:- I am able to get to the exchange from internal using http://servername/exchange and it works fine I have set up OWA , I made the necessary adjustments to IIS ON THE EXCHANGE SERVER, allowing basic authentication and then published the web ser...

Error while import BCM Database into CRM 3.0
I am trying to import BCM Database into CRM 3.0 When I run " Data Migration for Microsoft Outlook Business Contact Manager" wizard I get an unexpected error. Can someone help ? thanks Here is the Log: 8/21/2007 5:15:38 PM------>Transitioning to next screen. From: Welcome screen. To: PreconditionCheck screen. 8/21/2007 5:15:38 PM------>Connecting to Microsoft CRM Shamir_Systems_MSCRM 8/21/2007 5:15:39 PM------>Checking that Microsoft CRM Data Migration Pack is installed: Success 8/21/2007 5:15:39 PM------>Establishing connection to CDF database: Success 8/21/2007 5:1...

Error sending Excel worksheet as an attachment
If anyone can give a hand in this that would be awesome. One of our users in this office is unable to send and Excel 2000 worksheet as an attachment when selecting File > send to > Mail Recipient (as Attachment). She can send to Mail Recipient just fine (in the body of the e- mail)... and is she just right clicks the file and sends to recipient it sends fine as an attachment... she only can't send it this one way (as above). She gets the Outlook error: "The action could not be completed" and when she clicks OK to that error she gets the Excel error: "Genera...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Sending out mass (group) fax
Does anyone know how to set up 2007 Windows XP Pro Version 5.1 Service Pack 3 Fax Console (set up already with the Microsoft Fax Setup Wizard that came with operating system) so that I can send the same fax to about 50 different fax numbers? I already have the letter typed in Word and have it Mail Merged with the list of names and fax numbers as instructed by the "How-to" Microsoft Office Online article to set up form letters, e-mails or faxes; however, Mail Merge seems to have no provision for faxing--only for printing. It has allowed me to insert each fax receiver&#...

Error code 57E
I have Vista 32 bit XP windows Home Premium. Try to install 2007 Microsoft System KB972581 and Visio Viewer 2007 Service Pact 2, stalls at 50% install, however any other updates are installed. I have turned off the firewall and Microsoft Security Essentials and tried to install after, and still error code 57E on these 2 updates. The updates that did install are as follows... Cumulative Security Active X Killbits Microsoft Outlook 2007 Junk Mail Filter Security Essentials for Microsoft Office Windows Malicious Removal Tool Security Update I had this problem before with the SP2 ...

Technical error
AMEX and Discover are not being sent over to the credit card processor during settlement. I comes up with a "TECHNICAL ERROR". Visa and MC are settling fine. Does anyone know what the problem is?? All are setup as different tranaction types in RMS and ALL authorize fine during the transaction process. Your merchant account is not correctly configured for AMEX & Discover. This is on the bank's side, not RMS. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me dir...

error in receiving messages.
I started getting an error message last night when I tryed to set my emails, and am still getting the same message this morning. The message is as follows The connection to the server has failed. Account: 'mail.charter.net', Server: 'mail.charter.net', Protocol: POP3, Port: 110, Secure(SSL): No, Socket Error: 10061, Error Number: 0x800CCC0E What so I need to do. Thanks, Bill "bransn1" <bransn1@discussions.microsoft.com> wrote in message news:774E5DA8-C0DB-4354-8C35-A8482F1876EA@microsoft.com... >I started getting an error message last n...

custom errors in HRESULT
We have methods that all return HRESULT. We want to also report our custom error values in HRESULT. What's the right way to do this? "Sasha" <Sasha@discussions.microsoft.com> wrote in message news:87F67172-76EA-4B3D-9FEE-EBFC687F83D0@microsoft.com... > We have methods that all return HRESULT. We want to also report our custom > error values in HRESULT. What's the right way to do this? See "Error Handling (COM)" at http://msdn.microsoft.com/library/en-us/com/html/15f3ae3e-1794-4948-a7aa-6309a703364b.asp In particular, read the section on "Usi...

Run-time error 438?
Hello, I am trying to create an edit control for my form. I lock my textboxes in properties and then I created a command button with an event on onclick. My codes are working for most of them expect a few. When I click the button it gives me Run-time error 438. My code is based liked this. Me![Control].Locked=False Private Sub Edit_Record_Click() Me![Drago ID].Locked = False Me![Drago Claim Number].Locked = False Me![Dealer Claim Number].Locked = False Me![Dealer].Locked = False Me![Store].Locked = False Me![Machine Acres].Locked = False Me![Part Fail Date].Locked = False - NOT WORKING ...

Business Contact Manager import error
Hi All. I'm using the new Data Migration Pack for Microsoft Dynamics CRM 3.0. to import data from Microsoft Business Contact Manager into a new, clean, CRM 3.0 system. The import is failing, and I don't know why. I get as far as Map Users > Map Territories > Mapping Summary. When I click 'Configure' the migration stops with an unexpected error. I've included an extract of the activity log below, which shows more details. Any help or suggestions would be greatly appreciated! R 28/03/2006 11:38:11------>Next Screen Request: From ConfigurationSummary screen...

Use of user's group with W2000
Two things: 1-If no MVP can answer a question, someone should tell the questioner so he/she can move on. Frustration is high enough without being ignored. 2-It is grossly unfair that I can't post questions because I have W2000 and am limited to Explorer6. I can't even get to this page. Microsoft should not squeeze us out of the loop. (I borrowed a friend's computer with XP and explorer7 to write this and to post my unaswered question.) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for th...

how can i set the outlook express when receive email, the will be stored as a group (like as the newsgroup)
Thanks. Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Win asked: | Thanks. ...