I have a large set of data on the daily jobs off all porters in a hospital . They receive work requests to a mobile device and start and complete the jobs on that unit. It feeds in to a database and i believe uses SQL to plop me out an excel spreadsheet of every job done each day (the start and finish time and any delays). That's a very simplified breakdown but it should be enough to explain and I have no control over the SQL side yet.
I then paste this worksheet in to one i have built that fixes a lot of formula issues and errors we get because we start some jobs before midnight and complete them after. This is all in Excel 2010.
So at the end of each year i have a spreadsheet for the previous 12 months worth of work and I have been using pivot tables and very basic excel tools to analyse this data and its understandably slow. My first iteration has all 12 months of "raw" data on one worksheet (over 3.6 million cells at one point).
I have been trying this year to see if it's better to have 12 separate worksheets/files (one for each month of the year) with a standalone pivot table on a worksheet that links to all 12, but I'm only getting started and it feels quite clunky.
Before i get any further, am i wasting my time by trying a function that's never going to work well? Are there any programs that do this function better? I have crystal reports but I've no training, it seems to want a database connection rather than local files. And Access 2010 is proving to be less than useful with my lack design skills
Each spreadsheet is 30 columns and approx 12-16k rows for a single month.
I then paste this worksheet in to one i have built that fixes a lot of formula issues and errors we get because we start some jobs before midnight and complete them after. This is all in Excel 2010.
So at the end of each year i have a spreadsheet for the previous 12 months worth of work and I have been using pivot tables and very basic excel tools to analyse this data and its understandably slow. My first iteration has all 12 months of "raw" data on one worksheet (over 3.6 million cells at one point).
I have been trying this year to see if it's better to have 12 separate worksheets/files (one for each month of the year) with a standalone pivot table on a worksheet that links to all 12, but I'm only getting started and it feels quite clunky.
Before i get any further, am i wasting my time by trying a function that's never going to work well? Are there any programs that do this function better? I have crystal reports but I've no training, it seems to want a database connection rather than local files. And Access 2010 is proving to be less than useful with my lack design skills
Each spreadsheet is 30 columns and approx 12-16k rows for a single month.
