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

    Reformat data from mainframe report (2003 SP 2)

    I have attached a file I hope will explain my problem. The way our main hospital system is set up, users have a primary or master menu, secondary options that are given ad hoc, and security keys that allow use of a sub-option from the primary menu, or use of a secondary option. If the user doesn't have they key, the option is hidden from view. Every six months I have to review everyone in my service (** Possible SPAM post - please alert a Moderator (2)**) to make sure menus and keys check out. But, the report from our main system is hard to work with, especially if one has vision problems as I do right now. I am trying to reformat the report and move things around so I can create Pivot Tables or something I can work more easily with. I can recognize options or keys that are not appropriate, but I just can't handle a 3900 line report right now. Thanks for your help!

    Jim Whitt
    Pharmacist
    Temple, Texas USA
    Attached Files Attached Files
    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: Reformat data from mainframe report (2003 SP 2)

    The mainframe system should be able to export data in different ways. If you can get the system to produce data in a useful format, that's a lot easier than trying to convert already exported data back into a useful format.

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

    Re: Reformat data from mainframe report (2003 SP 2

    Thanks, Hans. I've had sixteen years' experience with this system, and I know for sure it's a canned report that can't be modified. Even if it could be, our IT service's number one programmer is out for awhile. I have some add-ins such as Spreadsheet Assistant. I'll look through them again, maybe there's something I missed. Have a great day.
    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: Reformat data from mainframe report (2003 SP 2

    I have little time right now, I will look at it again later today.

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

    Re: Reformat data from mainframe report (2003 SP 2

    Now that I'm looking at it again: is the primary menu really represented inconsistently? For Pharmacist1, in B1, it is Primary menu: Primary 1. whereas for Pharmacist2, in B29, it is just Primary 1.

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

    Re: Reformat data from mainframe report (2003 SP 2

    Sorry, that's an error on my part setting up the sample data. All the Primary menus are in the format, "Primary Menu: Whatever". It would be easier if I could just send you a piece of the actual sheet, but the data's confidential. My bad, as the young folks say.
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Reformat data from mainframe report (2003 SP 2

    Do the data in the "real" export start in row 2, as in your sample?

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

    Re: Reformat data from mainframe report (2003 SP 2

    Yes. I have headers in row 1, and the data starts on row 2. If I take a series of steps using some of the Spreadsheet Assistant add-ins, I think I can eventually get it like I want it. But, boy, are there a lot of steps.

    Let me float this balloon. It would really help if I could tell Excel to start at a cell with "Secondary Menus" in it (call it B2), then go to the next cell with "Security Keys" (call it B11), then select B3:C10 and move them over. The cell addresses won't be the same since I'll have to insert blank columns, but perhaps you get the idea. I know how to tell it to find, but not how to select the cells in between.

    Thanks again for taking the time to check it.

    Jim
    Jim Whitt
    Pharmacist
    Temple, Texas

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

    Re: Reformat data from mainframe report (2003 SP 2

    See the attached version. It contains a macro that should fill the second worksheet.

    If you copy the macro to your real workbook, you should
    a) Create a second sheet with the column headers.
    [img]/forums/images/smilies/cool.gif[/img] Replace the names "Sheet1" and "Sheet2" in the code with the correct names.

    Test thoroughly on a copy of the report!
    Attached Files Attached Files

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

    Re: Reformat data from mainframe report (2003 SP 2

    Hans, your code works perfectly. I had done more cleanup on the original list than I had realized, so what I sent and what you worked on was not the original list. My fault, duh. So, as soon as I get it to the point where the code can work I'll be fine. I wish I could learn to write code like that. Thanks for all the help.
    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
  •