Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: PivotTables

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    All,
    I decided it's about time I looked at using these PivotTable thingies.
    I've ran into problem already.

    I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

    The Fields/Headings I'm importing are:

    {"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


    An explaination of the fields I need to look at currently are:

    calldate: is the Date AND Time stamp, combined, of the record
    operator: User NT login
    DMCYN: Always Y or N

    I've been through the PivotTable wizard.

    I selected the range tblCallLog, which is a Dynamic Named Range.
    I placed:
    calldate in Row
    operator in Column
    and Count of DMCYN

    Finish.

    Now for my question.
    the calldate is in the following format

    24/04/2002 18:17:02

    therefore every seccond in accounted for.

    how do I Group them so the Row part reads

    12:00
    13:00
    14:00

    and has the count of TIME>=12:00 and <13:00

    Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

    this is not very well explained but I can answer any questions as required.

    All your help will be greatly received.

    Many thanks
    "Have a good time......all the time"
    Ian Mac

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 06:08, Ian Mac wrote:
    All,
    I decided it's about time I looked at using these PivotTable thingies.
    I've ran into problem already.

    I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

    The Fields/Headings I'm importing are:

    {"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


    An explaination of the fields I need to look at currently are:

    calldate: is the Date AND Time stamp, combined, of the record
    operator: User NT login
    DMCYN: Always Y or N

    I've been through the PivotTable wizard.

    I selected the range tblCallLog, which is a Dynamic Named Range.
    I placed:
    calldate in Row
    operator in Column
    and Count of DMCYN

    Finish.

    Now for my question.
    the calldate is in the following format

    24/04/2002 18:17:02

    therefore every seccond in accounted for.

    how do I Group them so the Row part reads

    12:00
    13:00
    14:00

    and has the count of TIME>=12:00 and <13:00

    Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

    this is not very well explained but I can answer any questions as required.

    All your help will be greatly received.

    Many thanks
    Hi Ian,

    you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

    (I've seen your posts...you know your stuff !)

    Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

    Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

    apologies if I'm stating the obvious !

    (I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)


    :: Pharma Z - Family drugstore ::

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 06:08, Ian Mac wrote:
    All,
    I decided it's about time I looked at using these PivotTable thingies.
    I've ran into problem already.

    I have an ammount of data that is brought into a workbook using MSQuery from an Access Database.

    The Fields/Headings I'm importing are:

    {"calldate","cbdate","leadcode","outcome","operator","link_id","Type","DMCYN","NoInterest"}


    An explaination of the fields I need to look at currently are:

    calldate: is the Date AND Time stamp, combined, of the record
    operator: User NT login
    DMCYN: Always Y or N

    I've been through the PivotTable wizard.

    I selected the range tblCallLog, which is a Dynamic Named Range.
    I placed:
    calldate in Row
    operator in Column
    and Count of DMCYN

    Finish.

    Now for my question.
    the calldate is in the following format

    24/04/2002 18:17:02

    therefore every seccond in accounted for.

    how do I Group them so the Row part reads

    12:00
    13:00
    14:00

    and has the count of TIME>=12:00 and <13:00

    Another problem is that I'd like to select the date but can't uae the calldate in both Page and Row

    this is not very well explained but I can answer any questions as required.

    All your help will be greatly received.

    Many thanks
    Hi Ian,

    you can add "calculated fields" in your pivot tables which may help..... I've not used them much myself, so you'd proabbly be on the same learnign curve if you attempted to add some to determine the times

    (I've seen your posts...you know your stuff !)

    Alternatively, could you pop in an extra column in your data to strip the time range from the calldate and include this is the table ?

    Similarly, another column in your data with the date stripped out from the call date would allow you an extra field with which to pop into the page field....

    apologies if I'm stating the obvious !

    (I usually stay well clear of any questions about dates and times, as they are not my fortes by *any* stretch of the imagination!)


    :: Pharma Z - Family drugstore ::

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chis my best mate


    tell me whats wrong with times and date, anyone would have thought you dont like them!!!

    I would never ask you such a question ............... would i?



    _________________
    If you can help a guy in trouble -
    If you can sort that nagging problem -
    Pease try, at home, at work or on a message board.

    Others help you!
    So PLEASE help if you can - If only the once.

    Thank you -

    Rdgs
    ======

    [ This Message was edited by: Jack in the UK on 2002-04-30 14:23 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 14:23, Jack in the UK wrote:
    Chis my best mate


    tell me whats wrong with times and date, anyone would have thought you dont like them!!!

    I would never ask you such a question ............... would i?



    _________________
    If you can help a guy in trouble -
    If you can sort that nagging problem -
    Pease try, at home, at work or on a message board.

    Others help you!
    So PLEASE help if you can - If only the once.

    Thank you -

    Rdgs
    ======

    [ This Message was edited by: Jack in the UK on 2002-04-30 14:23 ]
    Sorry Jack but what has this got to do with my question/post? I think nothing.

    If you can help a guy in trouble -
    If you can sort that nagging problem -
    Pease try, at home, at work or on a message board.

    Others help you!
    So PLEASE help if you can - If only the once.


    but if you just want to talk to your mate...........

    But seriously, if any one can think of a way to do this without striping the date/time, (Chris I hadn't thought of it til a lot later. ). The problem I have now is that I wish to do the same with another set of data within the same table working on 20000 rows, which would add a further 40000 enteries to an already fairly heavy file (i.e MSQuery to Excel to pivot).
    on the other hand what I have currently is running a lot quick than the SUMPRODUCT() solution I've been experimenting with.

    Many thanks,

    _________________
    Share the wealth!!
    Ian Mac

    [ This Message was edited by: Ian Mac on 2002-04-30 15:27 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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