Reformat data from mainframe report (2003 SP 2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.
Replace the names “Sheet1” and “Sheet2” in the code with the correct names.

Test thoroughly on a copy of the report!

Reply | Quote

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.

Reply | Quote