Database/record-keeping design

[RobMatthews]

Hi folks.

With respect to this thread:
http://www.mrexcel.com/forum/showthread.php?558926-Tracking-macro-Usage

I am reading a bunch of text files that contain lines of user name and date of usage, for 24 macros. I have 20 users.

uhmmm...

How best should i record the data so that time-trends are visible if desired, along with user-specific information, and macro-specific information?

It seems that Pivot tables will be the display method, but actually recording the info: what do you think: a sheet per user, date down the rows and tools across the columns, and a counter for each day? Or all users and all tools on one sheet, ...

i don't have much formal database experience, but i can muddle my way though a fair bit of excel. (very noob with pivot tables and array formulae though).

Thanks in adavance for any ideas.

 

[Hermanito]

If you want a normalized database design, you'd get three tables: Users, Tools and Usage. Users would simply contain a list of users and a unique ID for everyone of them, the same for Tools. In Usage you would then get records like this: User = 112, Tool = 12; Usage = 2012/06/23 16:05:23

But imho, in this case that would work counterproductive (unless you want to store more info on users or tools in their respective tables) and my advice would be to go for 1 big list/table that contains all data. If you want to use a pivottable, it would be the easiest as well if you have all sourcedata in one list.

Based on a list that contains a column for User, Tool and UsageDateTime, pivottables are the perfect tool to mash the data into a readable format where trends can be detected...

Pivottables have long been something I tended to ignore, because I didn't quite grasp the concept, and besides, I could manage perfectly without them just fine. Until one day, at one job, I had to look into it because someone there went completely overboard with them , he could not distribute an Excelfile without at least a dozen pivottables in it . Now I fully grasp their power, and see it as the most powerful feature of Excel in data-analysis purposes. It really really really pays off to put in some time to get to understand pivottables!

 

[RobMatthews]

Thank you for that. It seems I have more work to do...

 

[Hermanito]

Good luck!

Don't hesitate to ask if you get stuck, but the key to understanding pivottables is using them... a lot , and then some more.