Excel and large datasets - advice?

[jim]

I've just inherited a woeful Excel doc (all 40MB of it) and am in the process of coming up with ideas to fix it, but I think, technically, it ought to be possible to improve on it by just not using Excel at all.

The context is: there's a system which tracks all logons, and produces a monthly report with a list of UIDs and when they accessed the system. With a list of staff information (inc. UIDs), the excel doc then runs lookups to find all of the relevant staff info for each and every UID. Then there is a huge list of tables, over a thousand lines long, which show the percentages of people accessing the system by grade, by department, by location, and so on.

As you can imagine, running that on several months' data, running to thousands and thousands of lines of lookups, it all becomes unbearably slow. What I'm in the process of doing is offloading the analysis to separate workbooks, so that each month is calculated separately, and then it's possible to just paste the values into a final overview doc, month by month (since the old data won't change).

However, that only feels like half a solution to me - a stop-gap I guess. I'm thinking that Access might be a better solution, because surely you should be obtaining information about what percentage of people accessed the system from a particular office by running a report - not by having pre-emptively created a table with a very precise and bespoke formula to calculate it? Or is my logic flawed?

If I can use Excel, then it's much easier for me, it just feels... wrong.

[scaryjim]

If it's actual data, get it in Access ASAP. Then consider moving it to SQL Server

OK, now I'll actually read your post ......

Without seeing the actual data I can't be 100% sure, but it looks to me like a fairly trivial piece of work for an Access database. Seriously, learn it, get your hands dirty with some VBA to stick a nice dashboard on it, and management will love you Just be sure you *very carefully* document all the business logic in the reporting before you try to reimplement it.

Then you can print nicely formatted reports, export data for use in other applications (even back into Excel if that's how management want it), whatever: handling that volume of data will be *way* easier in Access.

p.s. if your employers want to hire an Access consultant for a day or two just to get you going in the right direction....

[TheAnimus]

Ah I spent a few years fixing stuff like this.

40MB isn't much. When you get above 400 it will be more 'fun'.

For now, I'd have thought modern hardware and 2013 let this work nicely enough. The question is, how big will it have to be in the future?

[jim]

Ah I spent a few years fixing stuff like this.

40MB isn't much. When you get above 400 it will be more 'fun'.

For now, I'd have thought modern hardware and 2013 let this work nicely enough. The question is, how big will it have to be in the future?

I think if the 40MB was raw data it would probably be alright, but it's about 5% data and 95% index/matches, just sends the processor into overdrive. Full calc takes about 2 mins on my quad core laptop, on the older machines it just crashes them outright.

[herulach]

Access or some other form of database is obviously the right home for at least the lookups, If its anything like our place though you'll have issues getting the source data directly into it. Also, prepare to be the resident IT guy if you do do it and you're not already.

[Nipun]

You can use Octave , Matlab or R to do the data analysis .I prefer MATLAB over others

[finlay666]

DB + Lightswitch

[oldboy47]

Definitely a suitable candidate for a database - several source tables and multiple reports/outputs back to applications like Excel etc.