Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table problem (Excel 2003 11.56.12.5606)

    This just goes to show that, even after working with Excel since version 3.0, I still don't understand it as well as I'd like.

    This is an oddball problem. I'm calling it a pivot table problem due to a migraine and a feeble brain which cannot think of a better word. I'm trying to tabulate when medications are scheduled to be administered over a month's time. For example, Monday at 1 pm, Thursday at 2 am, Sunday at noon. I have a little over 11,000 orders with various administration times. My problem is the data from my hospital system isn't arranged so that a pivot table can make sense of it. Once I got it cleaned up, I got to this point (each row represents one medication order and the administration times are in 24-hour format. Sorry if this doesn't line up:
    <font face="Georgia">A B C D E F
    Tuesday 0100 0730 1130 1600 2030
    Thursday 0600 1400 2200
    Friday 0300 1900 2300

    For a pivot table to work, at least as I understand them, it would need it laid out like this:
    A B
    Tuesday 0100
    Tuesday 0730
    Tuesday 1130
    Tuesday 1630
    Tuesday 2030
    Thursday 0600
    Thursday 1400
    Thursday 2200
    Friday 0300
    Friday 1900
    Friday 2300
    </font face=georgia>
    As I mentioned, the worksheet is 11000 rows long now, and "flattening it out" in the manner above - if I knew how - might take more than 65,000 rows. With the data being irregular - one row has one time, another has up to twelve - I'm not sure how to use Excel to add everything up. A sample file is attached. Thanks a lot for any help.
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Pivot table problem (Excel 2003 11.56.12.5606)

    If the total number of rows in the "straightened out" data could be over 65,536, you might create a table in an Access database and use that as data source for the pivot table. Access can handle tables with hundreds of thousands or millions of rows without problem. Or wait until Excel 2007 is released - it will be able to handle millions of rows. Whether you store the data in Excel or in Access, you can use a macro to convert the data from the current layout to the straightened layout.

    It is also possible to use a macro to create a table that looks more or less like the pivot table, but that would be much less flexible.

    What would you prefer?

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (Excel 2003 11.56.12.5606)

    Since I'm under a deadline I'd rather not wait for Excel 2007. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I thought about Access not having the 65K row limit, and that perhaps a crosstab query might work, but I lack experience with that feature. Whatever method is easiest for you, using Access so the report includes all my data, would be great.
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Pivot table problem (Excel 2003 11.56.12.5606)

    I have attached azip file with your workbook and an Access database you can use.

    The database contains a table tblPivotData with 3 fields: ID (AutoNumber), TheDay and TheTime.

    I have created a macro in your workbook that will transfer the data from your table to the Access table. You can then create a pivot table in your workbook based on the Access table. In the first step of the Pivot Table wizard, select the second option - using an external data source for your pivot table.

    Before running the macro, take a look at the code and substitute the correct path for the database on your system.

    Note 1: to be able to work with the database, I have set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.
    Note 2: make sure that macro security is not set to high, otherwise you won't be able to run the macro.

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (Excel 2003 11.56.12.5606)

    Thanks a lot. My work day is nearly over, so I'll start on this fresh in the morning. Thanks again.
    Jim Whitt
    Pharmacist
    Temple, Texas

  6. #6
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (Excel 2003 11.56.12.5606)

    Well, as are sometimes said in Texas, "Dang, I wish I was that smart," and, "I'm obliged to you." Obviously it worked perfectly.
    Jim Whitt
    Pharmacist
    Temple, Texas

Posting Permissions

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