Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Pivot table grouping (office 2k)

    It is sooo easy to feel inadequate!

    I really have read the online help, and a couple of books. Either my excell isnt working or I am missing smething very simple.

    I have a workbook with a lot of data in it in a single large table (50,000 rows). I only mention size in case there are limits involved here. On the the columns is full of dates. They look like dates and I have formatted them as dates.

    I have a pivot table and I have put the date field in the row area, and it pivot tables perfectly. After much trial and error I found what they meant to get at the grouping feature, and selected it. One of my books says it will spot the field type and offer memonths / quarters or whatever to group by and it just doesnt. I have tried all sorts of things but all I get is a single additional field with no useful parameters populated wth "group 1".

    How and where do I select quarters as my grouping granularity?

    Thanks in anticipation,

    Mike C

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

    Re: Pivot table grouping (office 2k)

    As a experiment, I created a pivot table from a table with over 53,000 rows with a date column. When I clicked on a date and selected Data | Group and Outline | Group..., I got the standard date grouping options (Seconds, Minutes, ..., Years.) Are you sure the values are really dates? For example, if you set horizontal alignment to standard, are the dates still right-aligned? Sometimes, when dates are imported from another application, they are interpreted as text.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table grouping (office 2k)

    Legare did not provide a formula, I suspect it was something like: (confirm with ctrl-shift-enter)

    =SUM(IF(ISTEXT(A2:A65536),1))

    If you find only a few you can use this trick to find and edit them:
    highlight the column, then choose edit - goto <special>
    Check constants and uncheck numbers <OK>
    Now only the "nonnumbers" are selected
    Enter will go thru all the each of the selections allowing you to edit them.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table grouping (office 2k)

    Make sure that the some of the dates are not really text values. Put the following array formula into an empty cell replacing A1:A50000 with the range that contains the dates. Since this is an array formula, hold down Ctrl and Shift when you press Enter. If the value displayed is greater than 0, then some of the dates are text not date values.

    Edited by Legare to add formula (thanks Steve). My formula is similar to Steve's:

    <pre>=SUM(ISTEXT(A2:A65536)*1)
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pivot table grouping (office 2k)

    thanks for your replies folks. So...
    I should have said that 80% of the table was imported including the offending field.

    I ran the formula and it showed no text fields (returned 0)
    I built a tiny new table and pivot table with dates I keyed in by hand. The pivot table and grouping worked fine, so I got to understand the mechanism.
    I copied the offending column ran the pivot on the new column and it worked fine
    I copied and pasted the values from that column back into the original one and then that worked fine.

    So my problem is fixed, but I dont feel in charge of the process, nor confident that I wont meet it again. Seems to be clearly something about real and non-real date properties, but I would have thought that the istext formula would have found any problems...

    Thanks again

    Mike C

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table grouping (office 2k)

    Did you remember to confirm with ctrl-shift-enter?
    When you select the cell with the formula are there "brackets" around it?
    <pre>{=SUM(ISTEXT(A2:A65536)*1)}</pre>


    Excel adds them (you do not) when your edit and then confirm with ctrl-shift-enter. If it looks like:
    <pre>=SUM(ISTEXT(A2:A65536)*1)</pre>

    It is not an array formula and you are only looking at 1 item in the array and if shows if only the first cell is text.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    May 2001
    Location
    Kenilworth, Warwickshire, England
    Posts
    269
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Pivot table grouping (office 2k)

    Steve,

    absolutely went in as an array formula - I have had help from you and Hansv in the past on this...

    Cheers,

    M

Posting Permissions

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